Google Apps Script: like AppleScript for the Web

I’ve always been a huge fan of AppleScript for automating tasks in scriptable appplications and (more importantly) gluing scriptable applications together. Particularly when working with applications which are designed to take full advantage of AppleScript, like BibDesk, Delicious Library, and XTension, AppleScript makes even complex tasks easy. Unlike macros which are confined to a single application, AppleScript is based on top of Apple Events, making it easy to target any scriptable application, even on a remote Mac over the network. More importantly, AppleScripts aren’t macros; they don’t just play back keyboard and mouse events; you get a real object-oriented view of the data being manipulated. But really good scriptable applications are hard to come by, and of course AppleScript does you no good if you’re using cloud-based applications like Google Docs.

Browser automation tools, like Selenium, and libraries like mechanize help fill the gap somewhat, but they’re far from providing the same rich environment that AppleScript does. To give a concrete example, I was recently working on a spreadsheet listing Twitter accounts for the top 50 transit agencies in the US (more on that project here). In the spreadsheet, I’d listed agencies’ accounts by username (that is, @username). But what I really wanted was a link to each account on Twitter (that is, http://twitter.com/username). I could have entered the links manually, but that would have required needless manual work. If I were using a conventional spreadsheet application on the desktop, I could have used whatever macro or scripting facility it provided, or I could have exported the file to CSV and used sed and awk to get the job done. But I was working in the cloud; I knew there had to be a better way.

Enter Google Apps Script. Google Apps Script provides for Google’s cloud-based applications the same scriptability that AppleScript provides for desktop applications on the Mac. In only a few minutes, after studying the documentation, I was able to produce a script which achieved the desired effect.

Buoyed by my quick success, I decided to try going a step further: what if I could use the Twitter API to automatically set each cell’s comment to the most recent Tweet? Doing so would give viewers a quick preview of the Twitter account’s content, without leaving the spreadsheet. Working off of some sample code from Google, I quickly wrote another script to do the job. I ran into trouble for a while until I found that the “Callback URL” in the Twitter application settings must be set to https://spreadsheets.google.com/macros; once that was done, everything worked perfectly. (Incidentally, the error message given in that case, “unexpected error”, is completely useless, and gives no clue as to the actual problem.) From there, all I had to do was set up a time-based trigger to run the script automatically so the Tweets would update periodically, and I was done.

For me, the real point—and the power of Google Apps Script—is how quickly and easily I was able to not only automate otherwise-tedious processes, but draw in data from disparate sources and display it automatically. I’ve only scratched the surface of what can be done with Google Apps Script; the technology can be made to do a lot more.