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(
      var o  = Utilities.jsonParse(result.getContentText());
    } catch (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)

0 Responses to “Google Apps Script, Spreadsheets, Twitter and Gadgets #guug11”

  • No Comments

Leave a Reply


This blog is authored by Martin Hawksey e-Learning Advisor (Higher Education) at the JISC RSC Scotland N&E.

JISC RSC Scotland North & East logo

If you would like to subscribe to my monthly digest please enter your email address in the box below (other ways are available to subscribe from the button below):

Subscribe to MASHe to monthly email updates


The MASHezine (tabloid)

It's back! A tabloid edition of the latest posts in PDF format (complete with QR Codes). Click here to view the MASHezine

Preview powered by: Webthumb

The MASHezine (eBook)

MASHe is also available in ebook and can be downloaded in the following formats:



Opinions expressed in this blog are not necessarily those of the JISC RSC Scotland North & East.

JISC Advance Logo

JISC Advance is a new organisation that brings together the collective expertise of established JISC services:

For further information visit

Creative Commons Licence
Unless otherwise stated this work is licensed under a Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License