Getting Live Google Spreadsheets

So, I’ve been using Google Spreadsheets as a way to let staff easily maintain data which gets passed through the Grinder to make RDF data. The problem is that I do it using the “Publish to web” option, which makes a public version of the document available at a URL. All fun and dandy, but that URL doesn’t update the moment you modify a cell, it updates every 5 minutes or so. ish. more-or-less.

This isn’t ideal as we want a situation where a staff member can finish editing some data and hit a ‘republish on datasite’ button (on data.southampton.ac.uk somewhere behind a password) which immediately downloads the latest version and converts that.

It seems that if you download the document with the API using a username/password you get a live copy — yay!

A bit of fiddling and I can do it with Curl & Perl.

So gotcha one; you have to do it in two steps, the first to get an authentication token, the second to ask for the file.

The next gotcha is you have to specify the service, and Google uses codes for these which are… not intuitive.

The last gotcha was that downloading a spreadsheet needs a different service key (“wise”) than getting the information about documents (“writely”). See what I mean about the daft codes?

Anyhow, the following code is placed in the Public Domain without warrenty in the hope it’ll be useful.

I’ll probaby rewrite it properly one day, but just knowing this is possible gets me a step closer to the Chef publishing the staff resturant menu each day!

#!/usr/bin/perl
use strict;
use warnings;

my $doc = '0AqodCQwjuWZXdDZqcm0tYmFGMVpDOG1obnctUXdhb0E';
my $format = 'tsv';
my $username = 'open.data.southampton@gmail.com';
my $password = 'XXXXXXXXX';
my $gid = 0;

my @result = `curl -s https://www.google.com/accounts/ClientLogin --data 'Email=$username&Passwd=$password&scope=https://spreadsheets.google.com/feeds/&service=wise&session=1'`; 
my $auth;
foreach my $line ( @result )
{    
   if( $line =~ s/^Auth=// )
   {
     chomp $line;
     $auth = $line;
   }
}

die "Failed to authenticate $username" if( !defined $auth );

$auth =~ s/[^A-Z0-9-_]//i; # sigh, better safe than sorry.

print `curl -s -H 'Authorization: GoogleLogin auth=$auth' 'https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=$doc&exportFormat=$format&gid=$gid' `;