Lance's TextBox

Remixing data from Google Docs & Spreadsheets

Right after Google Spreadsheets came out, I got a ton of downloads from a little class library I wrote for interacting with it over HTTP.  Problem is, Google didn't provide a true API - the solution was to use HTTP to manually perform the gets and posts required to manage each spreadsheet.  Now its much easier thanks to RSSBus!

Here is a sample Google spreadsheet, it looks like this:

Google Docs already allows me to create an RSS feed of this document, but its not very smart.  Its only useful in a feed reader - I can't use this feed in an application without a lot of pain.  But, if I want to create a rich feed of this document, I can do this using the GsheetOps RSSBus Connector (note:  use the RSSBus Excel Operations to work with Excel Spreadsheets).  Here are some examples of things I can do with this Google Spreadsheet and RSSBus:

• Generate a simple RSS feed of the data contained in the spreadsheet
• Sort the data in the spreadsheet how I like it
• Pipe the data into other applications like Excel, Access, a database, etc.
• Pipe the data to Internet services like Amazon S3, FTP/mail/etc servers, Salesforce.com, etc.

Generate a simple RSS feed of the data

The gsheetQuery operation (shown in the image above) allows me to provide my Google email and password (or optional SID that can be generated once with gsheetAuth to avoid repeated authentications) and point to a spreadsheet by specifying its name.  Then, clicking "call operation" shows me a preview of the RSS feed that will be generated by this call, which contains a series of items that look like this:

gsheet:date
2007-07-24T18:56:27.447Z

gsheet:edituri
http://spreadsheets.google.com/feeds/list/o03623.5323249/od6/private/full/

gsheet:hours
10

gsheet:ipm
0.0033

gsheet:items
2

gsheet:name
Bingley

The above example shows only one item, which has 6 elements, all with the gsheet prefix.  Now clicking the "create feed" will generate an .rsb file which will make this feed accessible over HTTP.   Optionally, I can modify this .rsb file to customize my feed to my liking.  Here's what the .rsb looks like by default:

<rsb:info title="gsheetQuery" description="A feed of data from a Google Spreadsheet.">
  <input name="name" description="The spreadsheet name. If left empty the edituri is required" default="myuploadedsheet" /> 
  <input name="sheetname" description="The worksheet name. If left empty the edituri is required" default="sheet1" /> 
</rsb:info>

<rsb:set attr="email" value="youremail@gmail.com"/>
<rsb:set attr="password" value="yourpassword"/>

<rsb:call op="gsheetQuery" output="out">
  <rsb:push title="[rss:title | def('untitled item')]">
    [out.* | tohtml()]
    <hr>(auto-generated by RSSBus Feed Wizard - www.rssbus.com)
  </rsb:push>
</rsb:call>

The .rsb file itself is very simple.  The first part, the rsb:info section, is just simple markup that describes what the feed is and what inputs it takes (in this case, just a spreadsheet name and a worksheet name).  Next, the rsb:set keyword is used to set some constant values - a gmail email and password that the gsheetQuery operation can use to authenticate.  Finally the rsb:call keyword is used to call the gsheetQuery operation, and all of the output item attributes (out.*) are pushed out (rsb:push) as as html.

You can use this script with your own Google Docs account just by modifying the spreadsheet name, worksheet name, email, and password specified in each script.

Sort the Data

Now I can pipe this gsheetQuery call into a feedSort call, and get a feed sorted by whichever column I like.  To do this, I can use the rsb:pipe keyword to pipe the output of gsheetQuery into the feedSort operation, like this:

<rsb:info title="gsheetQuery" description="A feed of data from a Google Spreadsheet.">
  <input name="name" description="The spreadsheet name. If left empty the edituri is required" default="myuploadedsheet" /> 
  <input name="sheetname" description="The worksheet name. If left empty the edituri is required" default="sheet1" /> 
</rsb:info>

<rsb:set attr="email" value="youremail@gmail.com"/>
<rsb:set attr="password" value="yourpassword"/>

<rsb:pipe>
  <rsb:call op="gsheetQuery" output="out" />
  <rsb:call op="feedSort?sort=gsheet:hours&type=numeric" />
</rsb:pipe>

Now I get a feed of items sorted numerically by the number of hours.  The rsb:pipe keyword is very handy, and I can continue the pipe as long as I want.  I could add another call after the feedSort to pipe its output to an operation to send an email, post a blog entry, send a paypal or credit card payment, create a row in an Excel spreadsheet or a database, or lots of other things.  For a full list of available RSSBus operations check out the connector list at http://www.rssbus.com.

More

Note the other operations included in GsheetOps:

gsheetAddRow - Insert/add a new row in a Google Worksheet.

gsheetAuth - Authenticate to Google Spreadsheets service.

gsheetDeleteRow - Delete an existing row from a Google Worksheet.

gsheetEditCell - Edit a particular cell value in a Google Worksheet.

gsheetGetRange - Get a specified range from a Google Worksheet.

gsheetListSpreadsheets - List the Spreadsheets in a Google Spreadsheets account.

gsheetListWorksheets - List the Worksheets in a Google Spreadsheet.

Print | posted on Thursday, May 15, 2008 12:34 PM

Copyright © Lance Robinson

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski