Another PLT Scheme, Google Spreadsheets API

Some time ago, I saw this post where Eli Barzilay presented a Google Spreadsheets API for PLT Scheme. My mind was blown. In hind sight, this kind of API integration isn't all that hard to imagine as it's just HTTP and XML. But still, to demonstrate the relative ease in which you can pull useful data from Google Spreadsheets, or Google APIs in general, and provide a clean interface to it. Well, wow.

This example has been cooking in the back of my mind, but I haven't really had much need for it. That is, until I realized that the spreadsheet I use to track my hours on, could be processed by Scheme to save us some time when I do accounting for our business. I couldn't resist building on what Eli put in place and adding a friendly list centric interface to it. Once I had the API, I could put together a couple of quick scripts to automate some of the tedium away.

You can grab the code for the API here and here.

Here's some toy usage:

#lang scheme
(require "gdata.ss"  ; for g-authenticate
         "spreadsheets.ss" ; for Spreadsheet access
         (only-in srfi/1 find))

;; Authenticate to start off. `wise' is the keyword for the 
;; spreadsheet service.
(parameterize ([google-auth (gui-g-authenticate "wise")])

  ;; Get a list of all your spreadsheets. A spreadsheet is a struct
  ;; of a title and URL to the document
  (define spreadsheet (get-spreadsheets))
  
  ;; Get the list of worksheets associated with a spreadsheet URL.
  ;; The link below is to my online runner journal.
  (define worksheets 
    (get-worksheets 
     "http://spreadsheets.google.com/feeds/worksheets/pPkcBhkzcZSZoCavYhQaxbg/private/full"))
  
  
  ;; Ask for a particular sheet, and return only a specific set of Cell objects, in this case in the 
  ;; range C3 to C10. Map the cell-value struct getter to see what's in the cells
  (map cell-value 
       (get-cells "http://spreadsheets.google.com/feeds/cells/pPkcBhkzcZSZoCavYhQaxbg/od7/private/full"
                  #:range "C3:C10"))
  
  ;; A more intereseting example. Get all the cells on a spreadsheet
  (let ([data (get-cells
               "http://spreadsheets.google.com/feeds/cells/pPkcBhkzcZSZoCavYhQaxbg/od6/private/full")])
    
    ;; using the standard filter, and some predicats defined in spreadsheets.ss, pull
    ;; out only those cells who have a specific value and a specific row number.
    ;;
    ;; THen map cell-row against them to convert from a cell object to a row number.
    (let ([rows-of-interest (map cell-row (filter (and* (has-value? "20092") (has-col? 8)) data))])
      (for ([r rows-of-interest])
        ;; For every row...
        ;;  Find the date by looking for the cell that has the right row and column of intrest
        ;;  Repeat for the distance
        (let ([date (find (and* (has-row? r) (has-col? 2)) data)]
              [distance (find (and* (has-row? r) (has-col? 4)) data)])
          (printf "On ~a I ran ~a\n" (cell-value date) (cell-value distance)))))))