onFormSubmit – Raising Web Scale Events in Google Spreadsheets

What happens if you want to actually do something with a particular response from a web survey form at the time it is submitted, other than just collect it?

One of the handy things about Google Spreadsheets is the ability to create interactive web survey forms that can collect data that is then posted into a corresponding spreadsheet. Around the time of the Google I/O event, several event related features were released as part of Google Apps script, the javascript scripting framework that supports an increasing number of Google apps. And by “event” I don’t mean something like the upcoming Isle of Wight Festival – I mean computational events, that can be used to trigger other computational actions…

One of the new events is onFormSubmit, which I finally got round to playing with last night. Here’s my “Hello World” example:

So here’s the code:

//Test function for Google Apps Script onFormSubmit
//Two sheets in a single spreadsheet doc
//First sheet corresponds to form
//Second sheet just displays one of the elements from the most recent form submission
// the function testOnSub() has a trigger associated with it: 'From spreadsheet' 'On form submit'
function testOnSub() {
  var ss = SpreadsheetApp.openById(SPREADSHEET_KEY);
  var sheet=ss.getSheets()[1];
  var form=ss.getSheets()[0];
  var lr=form.getLastRow();
  var el=form.getRange(lr,2,1,1).getValue();
  var t=el;
  sheet.getRange(1,1,1,1).setValue(t);
}​

Here’s how to set it…

What next? Earlier this week, I watched a compelling presentation from @progrium, based around the following slide deck:

Among the handy tools demonstrated (I loved the idea of clickhooks (src), clickable links with webhook callback actions) was a webhook debugging tool, postbin. What this tool does is just capture and redisplay stuff that is posted to it… which makes it ideal for a quick demo…

So for example, suppose I have a Google form set up, and I want to perform a particular action using a third party webservice on some element contained in the form submission, or maybe only on certain items according to what information was submitted via the form, as soon as the form is submitted. Here’s one way of doing that (code on gisthub):

// Simple spreadsheet, with first sheet containing form submission repsonses
// when the form is submitted:
// 1) grab the latest response,
// 2) post it to a third party service via an HTTP POST
function testWebhook() {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var form=ss.getSheets()[0];
  var lr=form.getLastRow();
  var el=form.getRange(lr,2,1,1).getValue();
  var t=el;
  //The following escape palaver is gleaned from a Google help forum...
  var p="val1="+encodeURIComponent(t).replace(/%20/g, "+")+"&val2="+encodeURIComponent(form.getRange(lr,3,1,1).getValue()).replace(/%20/g, "+");

  // Here's where we do the callback...
  var x=UrlFetchApp.fetch('http://www.postbin.org/YOURPASTEBINID',{method: 'post', payload: p});
}​

Attach the on form submit trigger event to the function, and here’s the response when we submit a form:

Clever, eh?

So what does this mean? It means that I can set up a Google Survey form and as soon as anyone posts a submission, I can process it, either within the Google Apps environment using Google Apps script, or using third party services that accept an HTTP post input.

As Jeff Lindsay suggests, the evented web is increasingly a reality…