Wednesday, March 9, 2011

JSON and Google Docs

If you're following along, at this point I am using a PHP script that I wrote which takes the CSV output from Google Docs and spits out a lovely JSON object.

While doing additional research for the project, I came across a page that explained how to do this without my local PHP proxy. It seems that the bright minds at Google already addressed this issue and provided a way to attain a JSON feed of any document, AND they wrap the returned data in SCRIPT tags. You can also add a parameter to declare the name of the callback function that should be called when the data is finished loading.

The example in the documentation uses Google Calendars, but with some trial and error, I constructed a URL that works with the Spreadsheets....

Putting that as the SRC attribute of our script tag will initiate the transaction and will call my custom JavaScript function "dataLoaded()" once the data is finished loading.

You can change which individual worksheet is retrieved by changing the number "1" in the URL.
Changing the "1" to a "0" will retrieve the first/leftmost sheet and changing it to "2" will grab the third sheet from the left. Make sense?

There are inherent problems, though, with that. If you change the order of the sheets at any time down-the-road, your scripts will ask for the wrong sheet and will break. But don't worry, there is a solution for that as well. But it's a little convoluted, so it shall require its own post.

From here on out, I will use this method of retrieving data from Google Doc Spreadsheets for this project.
If that changes, I'll be sure to post about it.

No comments:

Post a Comment

Please email files to