Google Spreadsheets - Publish to the web

A key component to this framework will rely heavily on the "Publish to the web" function within Google Docs.

The concept behind this feature is pretty straight forward: You select which sheets to publish and the format that you would like, and Google provides a link to said output. You can even publish a single spreadsheet cell if you choose.

I experimented with several combinations of output format to determine which would meet the needs of this project.

Here's what I learned about the various output formats:

(Web Page)  - will allow you to publish all sheets (worksheets) within your spreadsheet or any one sheet.
The output is intended to be used as-is. Parsing out individual pieces isn't really worthwhile and restyling any of the elements is further complicated due to the heavy usage of inline styles.

(HTML to embed in a page) - This just spits out the same page as the previous option, but it wraps it in an iFrame. Meh.

(CSV) - Comma separated values. Parsing a CSV stream can be a real pain, but there are functions within PHP that can make it more straight forward. I didn't spend much time with this option because it seemed too likely to cause problems in the future should I decide to change the order of my columns.

(TXT) - Plain Text. This obviously has limited implementations. I found it perfect for a single-cell output.

(PDF) - Didn't try this one.

(ATOM & RSS) - These are separate output options, that behave very similarly. This is where I spent a lot of time because these feeds are typically easy to parse and work with. They do have a good amount of overhead (data in the feed that we will ignore), but that's a small price to pay for the benefits of these formats. It should be noted that these formats allow output either as 'List' or 'Cells'. For this project, I am using 'List'.

While this worked fine at first, I started noticing some strangeness as my testing progressed. The Goole interface, when this output format is selected, de-activates the sheet-selection drop-down. In other words, if you need to get a feed of any sheet other than the first sheet, you're out of luck. Rats!

I have since found a solution to this, but it is convoluted and will require its own post.

Since I NEED to get feeds of individual worksheets within my spreadsheet, I will need to engineer a different solution. So after some further testing, I found that I can get individual worksheet feeds to happend using the CSV output.

Now, anyone who has spent time parsing CSV in javascript can tell you that it can be a pain in the ass. And since I will have to send the feed through a local proxy (to avoid cross domain scripting restrictions), I might as well convert it to a JSON object before I spit it back out. And that's what I did. I wrote a simple PHP script that takes two arguments. The first is the feed url, the second is whether to use the first row as column headers.

This actually worked great! I could get at any worksheet that I needed, and the JSON data was super easy to work with within JavaScript.

But wait, there's more!