How do you retrieve data from a Google Docs Spreadsheet?

Question

I found this wonderful example of pulling data from a Google spreadsheet in JSON: http://code.google.com/apis/gdata/samples/spreadsheet_sample.html

However, I can't seem to get it to work for a spreadsheet I created. I've made the doc public and published it, and tried every possible tweak on the key and sheet "name". Has anyone successfully used this? I've seen the Zend GData library, but would rather use JSON.

Thanks in advance for any tips.

Solution 1

I tried to work with the JSON feed a few months ago and I remember having to jiggle the handle a little to get the feed URL to work. I don't recall exactly what I did, but the bigger problem is that once you get it working the feed itself is kind of a mess.

I found that a much better solution (for my purposes, at least) was to use the CSV feed and then convert that to JSON on the server. I actually wrote a blog post outlining the steps a few weeks ago:

http://www.ravelrumba.com/blog/json-google-spreadsheets/

Solution 2

Google Spreadsheet as JSON can be done using Google Apps Script !

In Google Spreadsheet go to Tools > Scripts > Script Editor

Insert this code and hit the play button, use generated URL for generating JSON.

function getValues() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var json = Utilities.jsonStringify(range.getValues())
  Browser.msgBox(json);
}​

also give it a try for Simple example of retrieving JSON feeds from Spreadsheets Data API