I am spending time hacking away on Google APIs to really see what it is like.
I remember seeing the Google Spreadsheet Data API that allows you access to spreadsheets in Google Docs & Spreadsheets.
There is a full API that gives you access to create and modify spreadsheets even to the level of REST requests for each cell.
I was also surprised at the number of formulas available.
Suddenly I realised that I could create a spreadsheet and use a cell to do various calculations for me, so I hacked up a ruby script to do this:
% gspreadsheet [insert formula]
e.g.
% gspreadsheet ‘GoogleFinance(”GOOG”)’
467.3
% gspreadsheet ’sin(0.2)’
0.19866933079506
Behind the scenes the script used the Google APIs to put the formula in a cell, and then read from that field to get the calculated output.
Writing the script
To get this all working I just had to:
- Find a Ruby library to the Google APIs
- Work out how to authenticate using the Google ClientLogin API
- Work out the location for the REST requests
- Work out why I was getting a 404 error
Find a Ruby library to the Google APIs
I was surprised to not be able to find a nice API to Google Spreadsheets. I did find this script that helped a lot though.
I am working on packaging a gdata-ruby module that I will place in rubyforge soon. It will start out with just APIs such as ClientLogin and Spreadsheets, but hopefully we can grow it to cover more of them.
Work out how to authenticate using the Google ClientLogin API
The key to authentication is using the ClientLogin API to get the auth token, and hiding it away in a member variable so other requests will add it to the headers:
response = Net::HTTPS.post_form(https://www.google.com/accounts/ClientLogin, {'Email' => email, 'Passwd' => password, 'source' => "formula", 'service' => 'wise' }) @headers = { 'Authorization' => "GoogleLogin auth=#{response.body.split(/=/).last}", 'Content-Type' => 'application/atom+xml' }
Work out the location for the REST requests
To find out the location for feeds it helps to GET the feeds themselves and look for the post URLs in link tags.
To get the A1 cell (a.k.a. R1C1) you would use something like:
/feeds/cells/#{@spreadsheet_key}/1/#{@headers ? “private” : “public”}/basic/A1″
- The spreadsheet key is the magic key for each of your spreadsheets that looks something like: pSYwzniwpzSFfn0KFRg9oWB.
- The 1 right after that is the worksheet id.
- The private/public check get changed in this based on if the code is authenticating you or not (and if you have allowed public access to the spreadsheet in question).
- The ‘basic’ is a projection value. basic means just basic atom. ‘values’ means a full feed minus formula data, and ‘full’ means a full read/write feed with everything
- Finally we give the A1 cell info
For reading this cell we just used basic projection, but for writing data into the cell we need to use the URL:
“/feeds/cells/#{@spreadsheet_key}/1/#{@headers ? ‘private’ : ‘public’}/full”
Notice that we use full here (as we want full access) and yet we do not put in the cell in question. This is because we will POST or PUT an entry piece of XML:
<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'> <gs:cell row='1' col='1' inputValue='=sin("0.2")' /> </entry>
This has the row and column and input value (in this case a formula).
Work out why I was getting a 404 error
At first I was getting 404 errors when I posted data up. The reason was that I wasn’t putting the full namespaces in the entry doc:
<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
vs.
<entry>
XML always seems so frustrating and when these things happen. Really? You couldn’t work out what to do without that namespace? Really? Nice and forgiving. Give me JSON or YAML ;)
The full code
It was fun to be able to be productive with these APIs immediately because they are just basic REST actions that just require Net::HTTP to access. I will work on getting some nice helper libraries so the low level stuff doesn’t even need to be done.
For those that are interested, here is the quick hack as one script file. The code is ugly… I am sorry. The library version is a lot nicer (and is just a few lines of code after the require’s). You can also get weird behaviour if you use single ticks. For now use ” and all is well.
gspreadsheet
#!/usr/bin/env ruby require 'net/http' require 'net/https' require 'uri' require 'rubygems' require 'hpricot' # # Make it east to use some of the convenience methods using https # module Net class HTTPS < HTTP def initialize(address, port = nil) super(address, port) self.use_ssl = true end end end class GoogleSpreadSheet GOOGLE_LOGIN_URL = URI.parse('https://www.google.com/accounts/ClientLogin') def initialize(spreadsheet_key) @spreadsheet_key = spreadsheet_key @headers = nil end def authenticate(email, password) $VERBOSE = nil response = Net::HTTPS.post_form(GOOGLE_LOGIN_URL, {'Email' => email, 'Passwd' => password, 'source' => "formula", 'service' => 'wise' }) @headers = { 'Authorization' => "GoogleLogin auth=#{response.body.split(/=/).last}", 'Content-Type' => 'application/atom+xml' } end def evaluate_cell(cell) path = "/feeds/cells/#{@spreadsheet_key}/1/#{@headers ? "private" : "public"}/basic/#{cell}" doc = Hpricot(request(path)) result = (doc/"content[@type='text']").inner_html end def set_entry(entry) path = "/feeds/cells/#{@spreadsheet_key}/1/#{@headers ? 'private' : 'public'}/full" post(path, entry) end def entry(formula, row=1, col=1) <<XML <?xml version='1.0' ?> <entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'> <gs:cell row='#{row}' col='#{col}' inputValue='=#{formula}' /> </entry> XML end def add_to_cell(formula) #puts entry(formula) set_entry(entry(formula)) end private def request(path) response, data = get_http.get(path, @headers) data end def post(path, entry) get_http.post(path, entry, @headers) end def get_http http = Net::HTTP.new('spreadsheets.google.com', 80) #http.set_debug_output $stderr http end end if __FILE__ == $0 formula = ARGV.first || 'sin(0.2)' gs = GoogleSpreadSheet.new([INSERT YOUR SPREADSHEET KEY]) gs.authenticate('you@gmail.com', 'your password') gs.add_to_cell formula puts gs.evaluate_cell('A1') end
March 22nd, 2007 at 6:26 pm
Sweet, thanks!
May 21st, 2007 at 4:50 am
Ported to Python
http://gspreadsheet.blogspot.com/
July 27th, 2007 at 12:22 am
a hungry man is an angry man
September 25th, 2007 at 2:16 am
The Delaware Lottery Web site was constructed to comply with the accessibility guidelines developed through the WAI and the Web Presentation Guidelines for …
Visit duplasena to buy your lucky ticket.
October 22nd, 2007 at 7:36 pm
Very helpful – thanks. One note: evaluate_cell seems to require the cell in “R1C1″ format (the little test at the bottom didn’t work until I changed the evaluate_cell call that way).
May 19th, 2008 at 1:24 am
thanks for info
April 10th, 2009 at 5:40 am
Fascinating- are you available for hire or will you teach my programmer to do this stuff :)
May 27th, 2009 at 6:42 pm
thanks for sharing,generous blogger,the little test at the bottom didn’t work until I changed the evaluate_cell call that way