techno.blog("Dion")

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('[email protected]', 'your password')
gs.add_to_cell formula
puts gs.evaluate_cell('A1')
end