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:

before

What I’d really like is a simple:

after camping

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(:onchange => "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.

Advertisements


2 Responses to “Google Spreadsheets Go Camping”

  1. You cannot expect the most beautiful flower arrangement should
    you give the florist only ten dollars. Even men who have
    often found themselves on the receiving end after forgetting their significant others important days will find the service invaluable.
    Those meanings include ‘bonds of affection’; ‘health and energy’; ‘fascination’; and ‘woman love’.


  1. 1 Adminspotting.net » Links for March 07, 2007

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: