⌙ Google , Google Apps , Google Apps Script , Google Sheets

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

Martin Hawksey

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!

chevron_left
chevron_right

Join the conversation

comment 12 comments

  • ruslan

    is there app to input/collect data to spreadsheet via slider bar?
    greetings
    ruslan

    • Martin Hawksey

      With this method you can use any input tools you like, just create a HTML form, add a slider input (I guess there is a jQuery lib you could use) and direct the form to submit to the Spreadsheet’s ‘publish as service’ url. At the spreadsheet end you’ll need to script how the data is handled.
      Hope this helps,
      Martin

  • ruslan

    Hi Martin,
    Thanks for your comment.
    The main problem here is that I am a med.doctor with no programming skills. I´m looking for somebody who could help to set up an application on Google sites containing visual data input via slider bar (0-100), data storage in google spreadsheet and on demand report via google chart. I would need this for a medical use to measure self estimated conditions for ex feeling of temperature, etc
    Is there any existing user friendly app to do this job, could anyone help to set up such an app for fee?
    Greetings
    ruslan

    • Martin Hawksey

      Hi Ruslan – I’m not an expert in this area but I know a lot of people use http://www.surveymonkey.com which does have free as well as paid for accounts. If you can find someone to host it there is an opensource tool which is almost exactly the same http://www.limesurvey.org/ (it’s free to download but as I say you need to install it somewhere).
      Martin

  • zap

    Please show us how to use this script
    thanks alot

  • amargono

    Is there a away that we can use the google script to download or copy a google spreadsheet regularly to my local drive?
    e.g, I tried use the “wget” (linux/unix cmd), but it failed.
    Thanks,
    amargono

    • Martin Hawksey

      If you create a web service in apps script that creates a Blob of the spreadsheet you might be able to get it then. Other way (which you might have tried) is File > Publish to the web which would expose a url for different formats

  • Javier Ramos

    Hi Martin! i was wondering if this script could help me to get the active user email without being part of the spreadsheet form..i mean, i would like to retrieve the email from the active user on a spreadsheet that it’s form only contains a “name” and a “suggestion” field..can you help me Martin!?

Comments are closed.

css.php