# Google Apps Script, Spreadsheets, Twitter and Gadgets #guug11

Yesterday was the inaugural [Google Apps UK User Group (#guug11)](http://guug11.lboro.ac.uk/) 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](http://blog.ouseful.info/2011/02/16/google-apps-as-a-mashup-environment-slides-from-guug11/), 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](http://mhawksey.tumblr.com/post/3326036799/my-first-google-gadget-to-embed-a-flickr-photoset-with). 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](https://hawksey.info/blog/2010/11/auto-twitter-list/) 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](https://hawksey.info/blog/2011/02/twitteralyticsv2/)). 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](https://spreadsheets.google.com/ccc?key=0AqGkLMU9sHmLdDZ5Y2h5XzkzaDVhOUgzdnU1SUxvQXc&hl=en_GB&authkey=CJTuiM0J)) – 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](https://spreadsheets1.google.com/lv?key=tdK4JDQL80JWnS3q9_sc1CA&type=view&gid=15&f=false&colid0=0&filterstr0=mhawksey&sortcolid=-1&sortasc=false&page=3&rowsperpage=4) (I’ve filtered it to show the tweets I made during my presentation)