Mar 05, 2010

Google Apps Script + Spreadsheets = Mashup Hub

I’ve been playing around with Google Apps Script. It has great library support for calling out to restful Web APIs. This makes it a great mashup platform. Also:

• It uses web-native Javascript.
• The documentation is clear.
• It’s ridiculously easy to write a small script and then try it out and continuously refine it by running it in a spreadsheet.
• Spreadsheets are a great hub to pull together data from disparate sources and slice-and-dice it.

The key is the UrlFetchApp module, which lets you make HTTP GET and POST calls. For example, this is how you would call out to the bit.ly API to shorten a URL:

function shortenUrl(longUrl) {
  var response = UrlFetchApp.fetch("http://api.bit.ly/shorten?version=2.0.1"
      + "&longUrl=" + longUrl + "&login=<YOUR BIT.LY USERNAME>"
      + "&apiKey=<YOUR BIT.LY API KEY>&format=xml");
  var parsedXml = Xml.parse(response.getContentText());
  var shortUrl = parsedXml.getElement().getElement("results")
      .getElement("nodeKeyVal").getElement("shortUrl").getText();
  return shortUrl;
}

Substitute your own bit.ly username and API key in there.

You can now use “shortenUrl” like any other function in a spreadsheet formula:

Press “Enter”, and you get the shortened URL:

Here’s another example. This code snippet will tweet its argument. It shows how to issue an HTTP POST. Note the advanced arguments to the fetch method.

function tweet(status) {
  var advancedArgs = {
      method: "post",
      payload: "status=" + status,
      headers: {"Authorization": "Basic <base64 encoding of your username:passwd"}};
  var response = UrlFetchApp.fetch(
      "http://api.twitter.com/1/statuses/update.xml", advancedArgs);
  var parsedXml = Xml.parse(response.getContentText());
  var createdAt = parsedXml.getElement()
      .getElement("created_at").getText();
  return createdAt;
}

With these two functions, you could use a Google spreadsheet as your Twitter client.