Our Blog. We have some things we'd like to share.

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.