{"id":804,"date":"2012-02-08T12:33:50","date_gmt":"2012-02-08T12:33:50","guid":{"rendered":"http:\/\/blog.soton.ac.uk\/webteam\/?p=804"},"modified":"2012-02-08T12:33:50","modified_gmt":"2012-02-08T12:33:50","slug":"getting-live-google-spreadsheets","status":"publish","type":"post","link":"https:\/\/blog.soton.ac.uk\/webteam\/2012\/02\/08\/getting-live-google-spreadsheets\/","title":{"rendered":"Getting Live Google Spreadsheets"},"content":{"rendered":"

So, I’ve been using Google Spreadsheets as a way to let staff easily maintain data which gets passed through the Grinder<\/a> 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.<\/p>\n

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.<\/p>\n

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

A bit of fiddling and I can do it with Curl & Perl.<\/p>\n

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.<\/p>\n

The next gotcha is you have to specify the service, and Google uses codes for these which are… not intuitive<\/a>.<\/p>\n

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?<\/p>\n

Anyhow, the following code is placed in the Public Domain without warrenty in the hope it’ll be useful.<\/p>\n

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!<\/p>\n

#!\/usr\/bin\/perl\r\nuse strict;\r\nuse warnings;\r\n\r\nmy $doc = '0AqodCQwjuWZXdDZqcm0tYmFGMVpDOG1obnctUXdhb0E';\r\nmy $format = 'tsv';\r\nmy $username = 'open.data.southampton@gmail.com';\r\nmy $password = 'XXXXXXXXX';\r\nmy $gid = 0;\r\n\r\nmy @result = `curl -s https:\/\/www.google.com\/accounts\/ClientLogin --data 'Email=$username&Passwd=$password&scope=https:\/\/spreadsheets.google.com\/feeds\/&service=wise&session=1'`; \r\nmy $auth;\r\nforeach my $line ( @result )\r\n{\u00a0\u00a0 \u00a0\r\n\u00a0\u00a0 if( $line =~ s\/^Auth=\/\/ )\r\n\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0 chomp $line;\r\n\u00a0\u00a0 \u00a0 $auth = $line;\r\n\u00a0\u00a0 }\r\n}\r\n<\/pre>\n
die \"Failed to authenticate $username\" if( !defined $auth );<\/pre>\n
$auth =~ s\/[^A-Z0-9-_]\/\/i; # sigh, better safe than sorry.\r\n\r\nprint `curl -s -H 'Authorization: GoogleLogin auth=$auth' 'https:\/\/spreadsheets.google.com\/feeds\/download\/spreadsheets\/Export?key=$doc&exportFormat=$format&gid=$gid' `;<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[73,352,4225,74],"tags":[],"class_list":["post-804","post","type-post","status-publish","format-standard","hentry","category-command-line","category-data","category-perl","category-tips"],"_links":{"self":[{"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/posts\/804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/comments?post=804"}],"version-history":[{"count":2,"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/posts\/804\/revisions"}],"predecessor-version":[{"id":807,"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/posts\/804\/revisions\/807"}],"wp:attachment":[{"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/media?parent=804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/categories?post=804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.soton.ac.uk\/webteam\/wp-json\/wp\/v2\/tags?post=804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}