Mission Data Blog
Louisville-based Web Development & Software Engineering

I was tickled when I ran across the GoogleLookup and GoogleFinance functions in Google Spreadsheets. I was very tickled when I found there was a REST API for interacting with the spreadsheets.

So not only can you lookup the current volume of Google stock in a spreadsheet (=GoogleFinance(”GOOG”, “volume”)) or find out Roger Clemens’s ERA (=GoogleLookup(”Roger Clemens”,”earned run average”)) you can access those values real time using an open API. I cobbled together a quick ruby program that can retrieve values from a private or public (published) Google spreadsheet.

The public sample should work for everyone since I published that spreadsheet for the world to see. You will need to provide your own credentials and spreadsheet key to use the authenticated example. I will caution that this is just a proof of concept that has limited functionality, weak parsing, and no error checking.

#!/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
  def initialize(spreadsheet_key)
    @spreadsheet_key = spreadsheet_key
    @headers = nil
  end

  def authenticate(email, password)
    url = URI.parse('https://www.google.com/accounts/ClientLogin')
    response = Net::HTTPS.post_form(url,
      {'Email'=>email,
       'Passwd'=>password,
       'source'=>"ruby-ss-example-1",
       '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

  private
  def request(path)
    http = Net::HTTP.new('spreadsheets.google.com', 80)
    response,data = http.get(path, @headers)
    data
  end
end

gs = GoogleSpreadSheet.new('p39irgfu5fsFokTPyPCG8Lg')
puts gs.evaluate_cell("A1")
puts gs.evaluate_cell("A2")
puts gs.evaluate_cell("B1")

gs = GoogleSpreadSheet.new(<your spreadsheet key>)
gs.authenticate(<your email>, <your password>)
puts gs.evaluate_cell("E132")

The fun really just starts here. You can edit existing spreadsheets too (although you can ‘t create or delete them yet). As Rich pointed out to me a few days ago, it won’t be too long before we are adding ‘export to Google spreadsheet’ functions to our web applications.

               

This entry was posted on Wednesday, December 6th, 2006 at 10:51 am by steveny and is filed under google, ruby. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.