Editor’s Note:This is a guest post from John Watkinson. John is a developer and the co-founder of Docracy, a start-up company that hosts crowd-sourced legal documents and provides free e-signing services. He recently attended, and won first place, at the Google Apps Hackathon in NYC. Here's John's story about the event.
Our company is focused on documents in the cloud, so we attended the Google Apps Developer Hackathon on December 1st in New York City to learn more about the various Google Apps APIs, and how we can best integrate with them. During the initial presentations from Ryan Boyd and Saurabh Gupta of the Google Apps team, we were delighted to learn about Apps Script. It provides a powerful JavaScript interface into Google Apps, including Docs, Calendar, Gmail, Maps and many others.
After the tech presentations, we formed teams and started hacking with the technology. I joined a team with Matt Hall (also from Docracy), Nick Siderakis, Jeff Hsin and Scott Thompson. We struck upon the idea of creating a function that would make calls to Wolfram Alpha via their developer API. It took all five of us hacking away for a few hours to parse the Alpha results properly, but eventually we had an Apps Script custom function for Spreadsheet called “wolf”. It had a kind of magic feeling about it, as you could pass simple English-language queries into the function and get back exact numeric results in your Google Spreadsheet. Some example working queries:
=wolf("distance from the earth to the moon in km")
=wolf("population of canada in 1960")
=wolf("average january temperature in buenos aires")
=wolf("9th digit of pi")
=wolf("count of olympic medals won by japan in 2008")
Any function in Apps Script can be used as a spreadsheet function directly, so our main wolf function is the entry point to our script. We use Apps Script’s UrlFetch service to make our API call to Wolfram Alpha (note, the full URL is truncated for brevity):
wolf
UrlFetch
var response = UrlFetchApp.fetch("http://api.wolframalpha.com/..." + encodeURIComponent(input));
The response from Alpha is an XML file that includes a lot of metadata we don’t need. So, we used a recursive function and the XML manipulation tools in Apps Script to track down the element of interest to us (below is a simplified version):
function findText(e) { if (e.getName().getLocalName() == 'plaintext') { return e.getText(); } else { var children = e.getElements(); for (var i = 0; i < children.length; i++) { var result = findText(children[i]); if (result) return result; } return null; } }
A little bit more work is required to parse the result, as sometimes Alpha will report numbers in words (i.e. “40.7 million”), but otherwise that’s it!
For a small script, it definitely feels pretty powerful! Our little invention earned us a first place finish at the hackathon, and the prizes were little indoor RC-controlled helicopters. I managed to irreparably damage mine in a gruesome crash into my refrigerator already, but I hear that many of the others are still serviceable and flying missions daily.
Once we were comfortable with the Apps Script Services (and aware of its capabilities with spreadsheets in particular), a side project emerged that used some of the other integration features available. Apps Script functions can listen for events that are triggered by the editing of a spreadsheet cell (if the user grants the appropriate permissions). Using these onEdit Event Handlers, we wrote an implementation of the classic game Mine Sweeper that is playable right in the spreadsheet!
You can play the game by making a copy of this spreadsheet and then clicking the menu item SheetSweeper > New Game. When you play the game, the script responds to onEdit events that are dispatched from the spreadsheet.
function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [{name: "New Game", functionName: "startGame"}]; ss.addMenu("SheetSweeper", menuEntries); }
Then when the user edits a cell, the script responds and implements the mine sweeper logic:
function onEdit(event) { var ss = event.source.getActiveSheet(); var cell = ss.getActiveCell(); if (cell.getValue() == 'x') { // Flagging a mine flagCell(cell); } else { // Clearing a cell clearCell(cell); } }
We had a great time at the hackathon, and are happy to have been introduced to Apps Script. We believe it has an exciting future ahead of it.
