Updating Google Calendars from a Google Spreadsheet

I got a request today along the lines of:

We’re in the process of creating a master calendar of events spreadsheet relevant to [various things]. These [various things] will all then have their own Google calendar so they can be looked at individually, embedded etc and everyone could of course have access to all and view them all via their personal Google calendar, turn different calendars on or off, sync with Outlook etc. etc.

X said “wouldn’t it be great if we made the master spreadsheet with Google docs and it could somehow automate and complete the calendars”.

Sigh…;-) So – is it possible?

I’ve only had a quick play so far with Google Apps script, but yes, it seems to be possible…

Take one spreadsheet, liberally sprinkled with event name, description, start and end times, an optional location, and maybe a even a tag or too (not shown):

The time related columns I specified as a date type using the “Data Validation…” form from the Tools menu:

Now take one Google apps script:

function caltest1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar();
  for (i in data) {
    var row = data[i];
    var title = row[0];  // First column
    var desc = row[1];       // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
    cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
 }
}

(This was largely a copy and paste from Sending emails from a Spreadsheet Tutorial, which I’d half skimmed a week or two ago and seemed to remember contained a howto for bulk mailing from a spreadsheet, and the Apps script Calendar class documentation.)

And here’s the result of running the function:

The email tutorial adds a bit of gloss that allows a further column to contain state information about whether an email has already been set; we could do something similar to specify whether or not an event has been automatically added to the calendar, and if not, add it when the function is run.

Because it can be a pain having to go into the script editor to run the function, it’s easier to just create a menu option for it:

Here’s how:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Shove stuff in calendar", functionName: "caltest2"} ];
  ss.addMenu("OUseful", menuEntries);
}

I had a little play to see if I could trivially get an RSS feed into the spreadsheet using an =importFeed() formula, and use the details from that to populate the calendar, but for some reason the feed importer function didn’t appear to be working?:-( When I tried using CSV data from a Yahoo RSS2CSV proxy pipe via a =importData() formula, the test function I’d written didn’t appear to recognise the date format…

PS Arghh… the test formula assumes a Date type is being passed to it… Doh!

Hack round importFeed still not working by grabbing a CSV version of the feed into the spreadsheet:
=importdata(“http://pipes.yahoo.com/ouseful/proxy?_render=csv&url=http%3A%2F%2Fopen2.net%2Ffeeds%2Frss_schedule.xml”)

Tweak the calendar event creation formula:

cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc});

Run the function:

Heh heh :-)

PS it’s also possible to move content from a Google Calendar to a Google spreadsheet, as Grabbing Google Calendar Event Details into a Spreadsheet shows…

PPS it strikes me that the spreadsheets2calendar route provides one way of generating an iCal feed from a list of event times held in a spreadsheet, by popping the events into a Calendar and then making the most of its output formats? A bit like using Yahoo pipes as a quick’n’easy KML generator?

[UPDATE: related, via @mhawksey – Using Google Apps Script for a event booking system (Spreadsheet to Calendar & Site | Form to Spreadsheet, Email and possible Contacts)]

Re: not creating duplicate entries, check out this thread

Grabbing Google Calendar Event Details into a Spreadsheet

A comment to Updating Google Calendars from a Google Spreadsheet, where I showed how to get events described in a Google spreadsheet into a Google Calendar wondered if the other way around is possible? … It would be great for logging hours for projects or declaring working hours ;-)

Yes:-) Twenty seconds ago, this spreadsheet was empty:

A quick run of a script and I populated it from my default calendar using Google Apps script. Once again, all I did was peek through the docs and pull out the fragments I needed: Here’s the script:

function caltest3(){
  //http://www.google.com/google-d-s/scripts/class_calendar.html#getEvents
  // The code below will retrieve events between 2 dates for the user's default calendar and
  // display the events the current spreadsheet
  var cal = CalendarApp.getDefaultCalendar();
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var events = cal.getEvents(new Date("March 8, 2010"), new Date("March 14, 2010"));
  for (var i=0;i<events.length;i++) {
    //http://www.google.com/google-d-s/scripts/class_calendarevent.html
    var details=[[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime()]];
    var row=i+1;
    var range=sheet.getRange(row,1,1,4);
    range.setValues(details);
  }
}

So now not only can we use a spreadsheet as a database we can also use a calendar in a similar way, and if necessary, link them all together?

Maintaining a Google Calendar from a Google Spreadsheet, Reprise

In the post Updating Google Calendars from a Google Spreadsheet, I described a recipe for adding events to a Google Calendar from a Google Spreadsheet using Google Apps Script. After a quick chat with the person who was compiling a spreadsheet they wanted to use to populate a set of calendars, I revisited the script to make a few tweaks and hopefully increase its usability.

So here’s a glimpse of the spreadsheet they’re using to list dates for various campaigns and channels where related activity might occur. Firstly, we have some columns relating to the event or activity, and the dates on which they occur. The first column (added to calendar) is a control switch that identifies that the calendar details have been updated for that event:

Within the spreadsheet, I set the two date columns to have the Date type (from the Tools menu, set the Data Validation option to Date). I’m not sure how the spreadsheet is (correctly) identifying the US date format (MM-DD-YY) – maybe from a US timezone as a global setting for the spreadsheet?

As well as various other admin columns, there are columns relating to whether or not a channel will be used to support a particular event:

From what I could ascertain, the way the spreadsheet is supposed to work goes along the lines of: someone adds details of an event and the associated channels for the event to the spreadsheet. “Add” in a channel column says that event is to be added to that channel calendar. When the updating script is run, for each event it checks the control column A to see that an event hasn’t been added to the various channel calendars, and if it hasn’t checks the channel columns; if a channel column is set to “Add” the event details are added to that event calendar.

So – how do I need to modify the original script? Firstly, the original script use the default calendar. In this case, we need a separate calendar for each channel, so in Google Apps I created one calender per channel:

We can grab a calendar by name from a spreadsheet apps script using a call of the form:

 var cal_broadcast=CalendarApp.openByName("broadcastDemo");

When the script runs, we need to grab the appropriate range of cells from the spreadsheet to see which calendars to update. For testing purposes, I only grabbed a few rows…

var startRow = 2;  // First row of data to process
var numRows = 4   // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 26);
var data = dataRange.getValues();

for (i in data) {
  var row = data[i];
  var title = row[1];
  var desc=row[15];
  var added = row[col_added];  //Check to see if details for this event have been added to the calendar(s)
  var tstart = row[2]; //start time - I have defined the column in the spreadsheet as a Date type
  var tstop = row[3]; //start time - I have defined the column in the spreadsheet as a Date type
  var broadcast=row[col_broadcast]; // is this event one to "Add" to the broadcast calendar?
  var itunes=row[col_itunes]; // is this event one to "Add" to the itunes calendar? etc
  var youtube=row[col_youtube];
  if (added!="Added") { //the calendar(s) have not been updated for this event
    if (broadcast=="Add") {
      cal_broadcast.createEvent(title, tstart,tstop, {description:desc}); //add the event to the "broadcast" calendar
    }
    if (itunes=="Add"){
      cal_itunes.createEvent(title, tstart,tstop, {description:desc});
    }
    // etc for each channel
    var v = parseInt(i)+2; // +2 is an offset to do with the numbering of rows and the "blank" header row 0;
    sheet.getRange(v, 1, 1, 1).setValue("Added"); //set the fact that we have updated the calendars for this event
  }
}

In order to identify which columns to use to identify the broadcast, itunes, etc channels, I went defensive (the following bit of code comes before the previous snippet; what is does is to look at each column heading, and then set the column number for each channel appropriately based on its name; I should probably use a similar technique to identify the start/stop dates. What this approach does is accommodate changes to the spreadsheet in terms of the insertion of additional columns or the reordering of columns, for example, at a later date):

var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); //need a routine to set active sheet by name?
//go defensive
var col_broadcast,col_itunes, col_youtube=1;
var maxcols=sheet.getMaxColumns();
for (var j=1;j<=maxcols;j++){
  var header= sheet.getRange(1, j, 1, 1).getValue();
  switch(header){
   case "Added to Google (Y/N/Hold)":col_added=j-1;
   case "Broadcast":col_broadcast=j-1; break;
   case "iTunes":col_itunes=j-1; break;
   case "YouTube": col_youtube=j-1; break;
   default:
  }
}

Running the combined function thus searches the spreadsheet for the appropriate channel columns and control column, checks the control column for each event entry to ensure that the event hasn’t been added to the selected calendars, and then adds the event to the appropriate channel calendars if required.

Playing with the script, it seemed a little bit clunky, so I tweaked it to update the channel cells with the word “Added” if it had been set to Add, and the calendar had been updated:

if (broadcast=="Add") {
  cal_broadcast.createEvent(title, tstart,tstop, {description:desc});
  dataRange.getCell(parseInt(i)+1,col_broadcast+1).setValue('Added'); // Replace "Add" with "Added"; +1 is offset for sheet numbering
}

It also struck me that if the settings of a channel was updated to “Add” after that event was updated, that channel’s calendar would never get updated. So I created a variant of the updating function that would just run on a per column basis and update a calendar entry for an event if it was set to “Add”, rather than checking the control column:

function caltestAddtoCal_broadcast(){ caltestAddtoCal("broadcast"); }
function caltestAddtoCal(addCal){
  //...
  if (addCal!="") {
    if ((addCal=="broadcast")&&(broadcast=="Add")) {
      cal_broadcast.createEvent(title, tstart,tstop, {description:desc});
      dataRange.getCell(parseInt(i)+1,col_broadcast+1).setValue('Added'); //+1 is offset for sheet numbering
    }
  // ...
  }
}

What this means is is that a channel controller can update entries in their calendar by running the script just for that channel and adding “Add” to any event they want adding to the calendar, the list of “Added” entries showing which events have already been added to that calendar:

Having doodled a script that sort of works, it’s now time to hack it around it so it looks a little more elegant. Which means refactoring.. sigh… and another reprise in a day or two, I guess…?!

Writing 2D Data Arrays to a Google Spreadsheet from Google Apps Script Making an HTTP POST Request for CSV Data

Just a quick post to log another Google Apps script how to – this time how to grab a 2D array of CSV data from a URL and then paste it into a spreadsheet. For the trivial case, we could just as easily do this with an =importData() formula, but I’m still working out what bits of glue might be useful on the Apps script front…;-)

So – the demo function will take a blank spreadsheet and do this to it:

That is, shove some data from a query run over a data.gov.uk SPARQL endpoint into it.

Here’s the script – it shows how to HTTP POST a query from the Apps script to the SPARQLproxy endpoint, grab the results back as CSV, then convert them crom the CSV array to a range that can be posted into the spreadsheet.

function dgtest(){

  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getActiveSheet();
  var sc = ss.getActiveCell();

  //I really need to tweak this so we can enter a SPARQL query and then generate the args string from it...
  var args="query=prefix+sch-ont%3A+%3Chttp%3A%2F%2Feducation.data.gov.uk%2Fdef%2Fschool%2F%3E%0D%0ASELECT+%3Fschool+%3Fname+%3Fdate+%3Fdistrict+WHERE+{%0D%0A%3Fschool+a+sch-ont%3ASchool%3B%0D%0Asch-ont%3AestablishmentName+%3Fname%3B%0D%0Asch-ont%3AopenDate+%3Fdate%3B%0D%0Asch-ont%3AdistrictAdministrative+%3Fdistrict.%0D%0A}+ORDER+BY+DESC%28%3Fdate%29+LIMIT+15&output=csv&callback=&tqx=&service-uri=http%3A%2F%2Fservices.data.gov.uk%2Feducation%2Fsparql";

  var x=UrlFetchApp.fetch('http://data-gov.tw.rpi.edu/ws/sparqlproxy.php',{method: 'post', payload: args});
  var ret=x.getContentText();
  ret = CSVToArray( ret, "," );

  var arr = [];
  var c = [];
  for (var i=0;i < ret.length-1;i++) {
    c=[];
    for (var j=0; j< ret[0].length;j++){
      c.push(ret[i][j]);
    }
    arr.push(c);
  }

  var destinationRange = ss.getRange(1, 1, i, j);
  destinationRange.setValues(arr);
}

The CSV2Array function is one I found on Stack Overflow

Screenscraping With Google Spreadsheets App Script and the =importHTML Formula

Exciting news: Google Apps script is now available for all Google spreadsheet users…, so it seems I was timely in starting to get to grips with playing with this stuff…;-)

So what can we do with Google Apps Script? I’ve been posting a few ideas already, but here’s something I was working on last night – a script that automates table screenscraping using the Google spreadsheet =importHTML() formula.

Here’s the setting: Lichfield council lists details of the last round of council elections on a summary results page:

and a set of individual pages detailing comprehensive results from each ward (example).

What I wanted was a big table listing all the results by ward.

It’s easy enough to pull these results into a Google Spreadsheet using the =importHTML(“URL”,”table”,N) formula (where N is the number of the table in the page), but pulling results in for all 26 wards and then processing them separately would be laborious, so here’s what I did…

First of all, pull in the summary table to get a list of all the wards:

The results by ward page all live on a commonly structured URL (e.g. http://www.lichfielddc.gov.uk/site/custom_scripts/electiondetail.php?ward=2&category=1) with the ward parameter identifying the ward. This URL isn’t picked up by the table scraper, so I had to do a bit of hand finishing, adding an ID column to which I added the appropriate ward number of each ward as used in the ward results URLs.

So, having got a sheet that listed the wards, and a (hand added) identifier for each ward, I could write a script that would create a separate sheet for each ward and add an appropriately customised =importHTML() formula to it:

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var sheet = ss.getSheetByName("Wards");
  var wardNameID=[];
  var wardIDName=[];
  if (sheet != null) {
    var col_ward=getColNumber(sheet,"Ward");
    var col_ID=getColNumber(sheet,"ID");
    var startRow = 2;  // First row of data to process
    var numRows = getMaxRows(sheet);
    var maxcols= getMaxColumns(sheet);
    
    var dataRange = sheet.getRange(startRow, 1, numRows, maxcols);
    var data = dataRange.getValues();
    for (i in data) {
     var row = data[i];
     var wardName=row[col_ward];
     var wardID=row[col_ID];
     //Browser.msgBox(wardName+" "+wardID);
     wardNameID[wardName]=wardID;
     wardIDName[wardID]=wardName;
    }
  }
  for (var i=2;i<numRows;i++){//28
    var sname=wardIDName[i];
    ss.insertSheet(sname, 1);
    sheet = ss.getSheetByName(sname);
    addTableImporter(sheet,i)
  }
}

This function uses a couple of utility functions – getMaxRows() and getMacColumns() which I superstitiously added to get the number of populated rows/columns from a sheet:

function getMaxRows(sheet){
  var maxrows = sheet.getMaxRows();var max =maxrows;
  for (var j=1;j<maxrows+1;j++){
    var header= sheet.getRange(j, 1, 1, 1).getValue();
    if (header==""){
       max=j; j=maxrows+1;
    }
  }
  return max;
}
      
function getMaxColumns(sheet){
  var maxcols=sheet.getMaxColumns(); var max=maxcols;
  for (var j=1;j<maxcols+1;j++){
    var header= sheet.getRange(1, j, 1, 1).getValue();
    if (header==""){
       max=j; j=maxcols+1;
    }
  }
  return max;
}

and the more generally useful getColNumber(), which gets the number of a column in the sheet given its header:

function getColNumber(sheet,colName){
  var colNum=-1;
  var maxcols=sheet.getMaxColumns();
  for (j=1;j<maxcols+1;j++){
    var header= sheet.getRange(1, j, 1, 1).getValue();
    if (header==colName) colNum=j-1;
  }
  return colNum;
}

The addTableImporter() function is the one that adds the importTable() formula to each sheet as required:

function addTableImporter(ss,n) {
 var sc = ss.getActiveCell();

 var arr = [];
 var c = [];
 c[0]='=importHTML("http://www.lichfielddc.gov.uk/site/custom_scripts/electiondetail.php?ward='+n+'&category=1","table",1)';;
 arr[0]=c;

 var destinationRange = ss.getRange(1 , 1, 1,1);

 destinationRange.setValues(arr);
}

Running the addSheets() function creates one sheet per ward, imports the appropriate table, and names the sheet as the name of the Ward.

We’re now in a position to pull together a monolithic table that aggregates data from all the wards:

function aggregator(){
  // for each ward spreadsheet, load in data by column
  // dataRow=[wardName, candidate, votes]
  // if votes=="n/a", votes =100;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var table=[];
  table.push(["Ward","Candidate","Party","Vote"]);
  var writeRow=[];
  var bigsheet = ss.getSheetByName("Aggregate");
  var wards=getWardNames();
  for (var i=0;i<wards.length;i++){
    //get sheet
    var sheet = ss.getSheetByName(wards[i]);
    if (sheet!=null){
      var maxrows=getMaxRows(sheet);
      for (var j=2;j<maxrows+1;j++){
        var candidate=sheet.getRange(j, 1, 1, 1).getValue();
        var party=sheet.getRange(j, 2, 1, 1).getValue();
        var vote = sheet.getRange(j, 3, 1, 1).getValue(); if (typeof vote != 'number') vote=100;
        if (candidate!=""){
          writeRow=[wards[i],candidate,party,vote];
          table.push(writeRow);
        }
      }
    }
  }
  var destinationRange = bigsheet.getRange(1, 1, table.length, 4);
  destinationRange.setValues(table);
}

Again, I make use of a utility function, this time to grab the names of the wards from the ward spreadsheet:

function getWardNames(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
     
   var sheet = ss.getSheetByName("Wards");
   var wardNames=[];
   if (sheet != null) {
       var col_ward=getColNumber(sheet,"Ward");
       var startRow = 2;  // First row of data to process
       var numRows = getMaxRows(sheet);
       var dataRange = sheet.getRange(startRow, 1, numRows, 1);
       var data = dataRange.getValues();
       for (i in data) {
        var row = data[i];
        wardNames[wardNames.length]=row[col_ward];
       }
   }
  return wardNames;
}

Running the aggregator function pulls all the data into one sheet:

Finally, for completeness, I added a routine that will delete the separate ward sheets:

function deleteWardSheets(){
     var ss= SpreadsheetApp.getActiveSpreadsheet();
     var wardNames=getWardNames();
     for (var i=0;i<wardNames.length;i++){
       ss.setActiveSheet(ss.getSheetByName(wardNames[i]));
       ss.deleteActiveSheet();
     }
  }

Supposedly, it’s possible to share scripts by submitting them to an Apps script gallery:

On submitting a script, it appears to get passed into an approvals process, so I’m not sure what happens next…?

Anyway – the code is all provided above if you want to try it out. Remember, what it’s doing is looking at a list of Ward names and identifiers, creating a sheet for each Lichfield local council ward, importing the election results table for each ward from the Lichfield site into the appropriate sheet, then providing another function that generates an aggregated datatable containing all the results, annotated with the name of the appropriate ward.

I was wondering whether this could all be done with from single function call, but I got twitchy that if I called a sheet before the imported data was loaded everything would break.

The take home from me about this is that with a little bit of creativity you can mix and match spreadsheet formula and Javascript functions. (I don’t know if spreadsheet formula can be accessed directly from the apps script?) That is, you can run calculations either in Javascript/Apps script, or if it’s more convenient to use a spreadsheet formula, you can…

Steps Towards a Volcanic Ash Advisory Google Maps Mashup Using Met Office Data

Sigh…;-)

Hi Tony,
In looking for authoritative news on the cloud about to tr-ash my holiday, I found this advisory:

http://www.metoffice.gov.uk/aviation/vaac/vaacuk.html

There’s a string of coordinates which appear to mark the extent of the cloud at “FL200” (20000 feet?). E.g. N6343 W01935 is 63.43N 19.35W.

It looks ripe for your skills, and if you could get a map and a how to …

Don’t you realise it’s a race weekend – and if I tinker on such a weekend, I tinker F1data?!;-)

Okay – so here’s a half hour hack (I timed it)…

The advisory data from the Met Office looks like this:

Rather than mess around with any scraping, I just copies and pasted some of the data into a text editor (I use TextWrangler on a Mac) and ran a few regular expressions over it. Starting at the bottom of the list:

– remove end of line characters and replace them with a space;
– remove the “-” and any whist space around it and replace it with a return (new line) character;
– replace the space with a tab character.

The result of the regular expression processing is a two column tab separated list of co-ordinates.

These can then be copied and pasted into a spreadsheet. For rapid prototyping purposes, I pasted the data into a Google spreadsheet because I know I can get a access to a CSV output from there, and I also know that I can get access to a map widget that will plot markers given lat/long data in that environment.

BIG OOPS… The next step was to decode the position data. How to read a Volcanic Ash Advisory gave a couple of clues, and a quick test suggested the data is direction, degrees, minutes, seconds concatenated. I’m guessing that in the general case the degrees are always two digits, the minutes one or two digits and the seconds zero or two digits but in the data I looked at the length was always 5 characters for latitude (dDDMM), 6 for longitude (dDDMSS), so I just worked with those fixed lengths.

Most of the mapping tools I use require lat/long co-ordinates in a decimal format, so a quick check of Stack Overflow turned up a function to convert from degrees, minutes, seconds to the decimal version (Converting latitude and longitude to decimal values).

UPDATE: I think the data format is actually just a decimal format to 2 dp. So the above and code below is all, in this case, nonsense… but I’m going to leave the content here anyway…

I then used a variant of this code to hack a formula using Google Apps script to run the conversion:

Using the apps script function I had defined as a spreadsheet formula, I could convert the lat/long data in the format provided by the advisory note into the more typical digital representation. Highlighting the converted lat/long data and Inserting a Google Maps Gadget gave me a mapped preview over the data.

Here’s the code (needs generalising bearing in the 1/2 digits for minutes; I had a problem with substring (not sure what – I only ever got one character out) which is why I used substr for the parsing):>

//http://stackoverflow.com/questions/1140189/converting-latitude-and-longitude-to-decimal-values

/***************************************
*** BROKEN CODE - left in for reference purposes only ***

****************************************/
function ConvertDMSToDD_BROKEN_STUPID(degrees, minutes, seconds, direction) {
    var dd = degrees + minutes/60 + seconds/(60*60);

    if (direction == "S" || direction == "W") {
        dd = dd * -1;
    } // Don't do anything for N or E
    return dd;
}

function ParseDMS(input) {
  var parts = [];
  if ((input.substring(0)=="N")||(input.substring(0)=="S")){
    parts[0]=parseInt(input.substr(1,2));
    parts[1]=parseInt(input.substr(3,2));
    parts[2]=0;
    parts[3]=input.substring(0);
  } else {
    parts[0]=parseInt(input.substr(1,2));
    parts[1]=parseInt(input.substring(3));
    parts[2]=parseInt(input.substr(4,2));
    parts[3]=input.substring(0);
  }
  var coord = ConvertDMSToDD(parts[0], parts[1], parts[2], parts[3]);
  return coord;
}

You can see the [UPDATE: corrected] spreadsheet here: Volcanic Ash Advisory mapping demo.

I replaced the lat/long conversion formula with one that:
– adds a decimal place after the first two digits;
– adds a minus sign for W/S.

Here’s the corrected formula:

function ParseDMS(input) {
  var parts = [];
  parts[0]=input.substring(0);
  parts[1]=input.substr(1,2)+'.'+input.substr(3);
  var coord = parseFloat(parts[1]);
  if ((parts[0]=='S')||(parts[0]=='W')) coord=coord*-1;
  return coord;
}

So what would be next?

– tidy up the lat/long conversion code so that it works in general case;
– find a way of pulling data into the spreadsheet live.

It would also be interesting to try to take into account altitude data, and then view the data in 3D in something like Google Earth. A heat map view rather than separate markers might also be fun to do (e.g. using HeatMapAPI).

But for now, back to the weekend…

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…

Google apps script - spreadsheet events

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:

Pastebin response from Google spreadsheet onFormSubmit callback

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…