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)

Comments are closed.