Mashing up Google Spreadsheets with IBM Mashup Center

Nicole Carrier  December 4 2009 09:39:42 AM

Recently, I have had several inquiries as to how one could 'mashup' information that is sitting in the cloud in a Google spreadsheet. Thanks to a little help from a colleague (thanks, Bill Y!) I was able quickly turn a Google spreadsheet into an RSS or ATOM feed, CVS file, embeddable HTML, etc. From there, making a mashup was a piece of cake!

For those of you interested in how to do this, I created a very short video and posted it to youtube. You can also watch the video embedded below. Every so often, folks tell me that the embedded video doesn't work. If it doesn't work for you, just go directly to Youtube

One thing I mention in the video, but don't go into detail about, is the format of the Google Atom Feed. What I expected to see what an element name for each column in the spreadsheet. Instead, what google produces for each row in the spreadsheet are only two elements that contain the data. The first is an element called title, which contains the data in the first column. The second element is called content, and it contains the rest of the information in the remaining columns.

To give an example, my spreadsheet has columns in it called Customer Name, Address, Zip, Ticker, URL. The first row of data in my spreadsheet has data in it like follows: IBM, New Orchard Rd Armonk New York, 10504, IBM, http://www.ibm.com. When the ATOM feed is generated from Google for this spreadsheet, the data comes out in the following manner: Title = IBM, Content = address: New Orchard Rd Armonk, New York, zip: 10504, ticker: IBM, url: http://www.ibm.com.

If we wanted to use this ATOM feed in a real mashup, we would need to figure out (through a data mashup + Reg Expressions likely), how to parse out the data from the element names. Not so easy for a non-programmer.

As shown in the video, the answer is to use the CSV option instead. Google lets you check off an option that if the data changes, it will be automatically republished. This is cool in that the data in the spreadsheet can change, and it can ripple through to your mashup - without any manual intervention.

Mash on

--nicole
Comments Disabled