Google Spreadsheets Go Camping

07Mar07

Being such a pretty day in Birmingham, I decided to take google spreadsheets camping. I’m starting to use Google Spreadsheets more and more and have very little to complain about. However, the interface leaves much to be desired. My chief problem is the reliance on opening up multiple tabs( windows for non firefoxers ) for each spreadsheet. For instance:

What I’d really like is a simple:

And something lightweight would be great. First off, getting a list of the available sheets borrowing some code from the gdata rubyforge project

require 'net/http'
require 'net/https'
require 'uri'
require 'hpricot'

module Net
  class HTTPS < HTTP
    def initialize(address, port = nil)
      super(address, port)
      self.use_ssl = true
    end
  end
end

class GoogleSpreadsheet
  attr_accessor :id, :links, :title, :link, :key
  SPREADSHEETS_ROOT = "http://spreadsheets.google.com/ccc"
  URL_ROOT = "#{SPREADSHEETS_ROOT}?key="
  NEW_LINK = "#{SPREADSHEETS_ROOT}?new"
  def initialize(entry)
    @id = entry['id'][0]
    @key = @id.split('/').last
    @links = entry['link']
    @link = URL_ROOT + @key.to_s
    @title = entry['content']['content']
  end

  def document
    XmlSimple.xml_in(@link)
  end
end

class GoogleUser
  GOOGLE_SPREADSHEET_LIST_URL = '/feeds/spreadsheets/private/full'
  GOOGLE_LOGIN_URL = URI.parse('https://www.google.com/accounts/ClientLogin')
  attr_accessor :email, :password, :token, :spreadsheets
  def initialize(email, password)
    self.email=email
    self.password=password
    self.authenticate
  end

  def authenticate
    $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 get_spreadsheets
    the_list = XmlSimple.xml_in(spreadsheets_list)
    @spreadsheets = []
    the_list['entry'].each{|entry|
      @spreadsheets << GoogleSpreadsheet.new(entry)
    }
    @spreadsheets
  end

  def spreadsheets_list
    request(GOOGLE_SPREADSHEET_LIST_URL)
  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
  end
end

Then for the single file camping interface:

#!/usr/bin/ruby
%w(rubygems camping gspreadsheet).each { |lib| require lib }

Camping.goes :CampingInSheets

module CampingInSheets::Controllers
  class Index < R '/'
    def get
      render :index
    end
    def post
      @user = GoogleUser.new(input.username, input.password)
      @spreadsheets = @user.get_spreadsheets
      render :index
    end
  end
  class Style < R '/styles.css'
    def get
      @headers["Content-Type"] = "text/css; charset=utf-8"
      @body = %{
          iframe{width: 100%; height: 80%;}
      }
    end
  end
end

module CampingInSheets::Views
  @@mab = Markaby::Builder.new
  SPREADSHEETS_ROOT = "http://spreadsheets.google.com/ccc"
  NEW_LINK = "#{SPREADSHEETS_ROOT}?new"
  def spreadsheet_link spreadsheet
    link_to spreadsheet.title, spreadsheet.link, :target => 'sheets'
  end
  def spreadsheet_dropdown spreadsheets
    options = spreadsheets.map{|x| "<option value='#{ x.link }'>#{x.title}</option>"}
    options = "<option value='#{NEW_LINK}'>New Spreadsheet</option>" + options.join
    options = "<option></option>" + options
    @@mab.html{
     form{
      select(  nchange => "var l = this.options[this.selectedIndex].value; if( l != '' ){ sheets.location.href=l; }"){
       self << options
      }
     }
    }
  end
  def layout
    html do
      head do
        title 'Camping in Sheets'
        link :href=>R(Style), :rel=>'stylesheet', :type=>'text/css'
      end
      body do
        div.content do
          self << yield
        end
      end
    end
  end
  def index
    if @user
      h1 "#{@user.email.gsub(/@.*/,'')} google spreadsheets"
      self << spreadsheet_dropdown(@spreadsheets)
      iframe(:name => 'sheets')
    else
      _login
    end
  end
  def _login
    h1 'Google Email and Password needed to Camp'
    form :action => R(Index), :method => 'post' do
      label 'Username', :for => 'username'; br
      input :name => 'username', :type => 'text'; br

      label 'Password', :for => 'password'; br
      input :name => 'password', :type => 'password'; br

      input :type => 'submit', :name => 'login', :value => 'Login'
    end
  end
end

And hardcore campers could rightly say that the code is horribly inefficient but it works for me. Combining this with a mousehole script to proxy all request to google spreadsheets to the camping interface would make this even better.

In order to make the two files work together, install the gems necessary for camping, save the second code snippet as camping_in_sheets.rb, the first as gspreadsheet.rb, and then from the command line type:

camping camping_in_sheets.rb

and enjoy. If anyone gets it going with mousehole, let me know.

---

Possibly related posts: (automatically generated)

• JSON like ruby object
• Little bitty capacitor model
• First contribution from Brian: a class for recursive time-based operations,…

Filed under: Uncategorized   |  1 Comment