Categories:

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.

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.

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' ;

Posted in Command Line, Data, Perl, Tips.

## 0 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

Some HTML is OK

(required)

(required, but never shared)