Google Apps Script, Spreadsheets, Twitter and Gadgets #guug11

Yesterday was the inaugural Google Apps UK User Group (#guug11) event at Loughborough University. I was put to the test with 5 minutes in the open mic session talking about my work with Google Spreadsheets and Twitter. Fortunately before I took the stage Tony Hirst had a session on Mashing Up Google Apps, which did a great job of setting the scene highlighting how Google Apps is a great environment for quickly manipulating ‘stuff’.
Given the short time and the fact I was demoing stuff in Spreadsheet instead of flipping back and forth from a presentation I decided to embed my slides with the aid of a little gadget I wrote which plays back a flickr photoset. This enabled me also prepare cells of text I could tweet at a click of button. This required a bit of refactoring of my Populating a Twitter List via Google Spreadsheet creating this more general function:

function tw_request(method, api_request){
  var requestData = {
        "method": method,
        "oAuthServiceName": "twitter",
        "oAuthUseToken": "always"
      };
   try {
      var result = UrlFetchApp.fetch(
          "https://api.twitter.com/1/"+api_request,
          requestData);
      var o  = Utilities.jsonParse(result.getContentText());
      Logger.log(result.getHeaders());
    } catch (e) {
      Logger.log(e);
    }
   return o;
}

I could then attach the following script to a button on the sheet:

function tweetBut(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); // get active sheet
  var tweet = sheet.getActiveCell().getValue(); //grab the active cell as item to tweet
  var api_request = "statuses/update.json?status=" + encodeURIComponent(tweet); // build query for Twitter Api
  var data = tw_request("POST", api_request); // function to send tweet
  sheet.getRange("B34").setValue(tweet); // write tweet in specified cell < formated cell to large font for eveyone to see
  Logger.log(data); // debug stuff
  if (data["text"]!=""){ // if tweet sent make a notification
    ss.toast("Tweet sent", "Tweet");
  }
}

As this original spreadsheet already had a function to interact with Twitter Search to find people using a particular hashtag, I rewrote this slightly to also allow me to pull a named search into a sheet (as used in Twitteralytics v2).
With my new tw_request() function this opens up the entire Twitter API for endless mashineering (mentions, followers, following and more). There’s more tinkering to do but here’s my latest iteration of Spreadsheet/Twitter mashup (GoogTweetSheet) – there’s a bug with the custom twitter menu not appearing –to do
Using my search here’s an archive of all the #guug11 tweets (I’ve filtered it to show the tweets I made during my presentation)