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!

1 Response to “Collecting any data in a Google Spreadsheet using GET or POST by publishing as a service [#dev8d]”


Leave a Reply

About

This blog is authored by Martin Hawksey e-Learning Advisor (Higher Education) at the JISC RSC Scotland N&E.

mhawksey [at] rsc-ne-scotland.ac.uk | 0131 559 4112 | @mhawksey

JISC RSC Scotland North & East logo

If you would like to subscribe to my monthly digest please enter your email address in the box below (other ways are available to subscribe from the button below):

Subscribe to MASHe to monthly email updates

Loading...Loading...


The MASHezine (tabloid)

It's back! A tabloid edition of the latest posts in PDF format (complete with QR Codes). Click here to view the MASHezine

Preview powered by:
Bluga.net Webthumb

The MASHezine (eBook)

MASHe is also available in ebook and can be downloaded in the following formats:

Powered by NEWSTOEBOOK.COM

Archives

Opinions expressed in this blog are not necessarily those of the JISC RSC Scotland North & East.

JISC Advance Logo

JISC Advance is a new organisation that brings together the collective expertise of established JISC services:

For further information visit www.jiscadvance.ac.uk

Creative Commons Licence
Unless otherwise stated this work is licensed under a Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License