Collecting any data in a Google Spreadsheet using GET or POST by publishing as a service [#dev8d]

I’m at Dev8D which is a JISC funded event targeted at educational developers/coders. 

The aim of Dev8D is to bring together developers from across the education sector and wider in order to learn from one another and ultimately create better, smarter technology for learning and research. Dev8D is the major UK event in the year for educational developers from further and higher education.

I could write reams and reams about why Dev8D is an important and valuable event, instead I’m just going to share something I learned/made as a result of being here. What I’m about to show you is one of the building blocks for a bigger idea that Tony Hirst and I are chatting about and hopefully I’ll come back to that idea at a later date.

Essentially what we wanted to do was capture data to a Google Spreadsheet. There are obviously a number of ways to get data into a Spreadsheet like using Forms or fetching a url, but we needed something more flexible. Something you can do with Google Apps Script/Google Spreadsheet is ‘publish as service’.

There isn’t a whole load of documentation about this but the Google guide on Apps Script User Interfaces provides some hints on what is possible. The bit that interested me was capturing data from any HTML form using GET or POST. This means you can forgo the Google Forms interface and completely design your own forms. Here’s the handy bit of Google Apps Script code you need in your Spreadsheet to capture the data:

function doPost(e) { // if you want to use GET data use doGet(e)
  var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active')); // if you need to write anything to the sheet you'll need this to set the active sheet
  //http://www.google.com/support/forum/p/apps-script/thread?tid=04d9d3d4922b8bfb&hl=en
  var app = UiApp.createApplication();
  var panel = app.createVerticalPanel();
  for( p in e.parameters){ // this is just clycling through the parameters passed to the sheet. You can replace this to write to a particular cell
    panel.add(app.createLabel(p +" "+e.parameters[p]));
  }
  app.add(panel);
  return app;
}
//http://www.google.sc/support/forum/p/apps-script/thread?tid=345591f349a25cb4&hl=en
function setUp() { // you need to manually run this script once to collect the spreadsheet ID
  ScriptProperties.setProperty('active', SpreadsheetApp.getActiveSpreadsheet().getId());
}

Enjoy!