Access Google-apps public spreadsheet via Javascript

Question

Spent a bunch of time looking at this.. It seems that what little info there was about accessing a Google-apps spreadsheet is not very well maintained.. At Google IO this year there was an announcement of enhanced Google-apps script. Including UI elements..

That got me to thinking of creating a widget based on data in Google spreadsheets, no data writing just a simple reading/look up and display calculations.. Then I realized the UI feature was only available for Premier account.. Not a huge deal at only $50/yr and some free trial time up front. It seems that the ui feature may be somewhat restrictive.

But then I began to think about all the little things I might have to do,, so I started to investigate how to just access the spreadsheets from Javascript, in which case I think they could be a plain I-Google gadget.. an I-Google gadget is quite powerful and flexible in what it can do. And this could allow a lot more flexibility.. In short I've come up short.. anyone else out there? This sort of looked like a clue http://almaer.com/blog/gspreadsheet-javascript-helper-for-google-spreadsheets and this one which I could not fetch a current spreadsheet http://code.google.com/apis/gdata/samples/spreadsheet_sample.html but has not been touch for a long time and I could not make it work on a current spreadsheet.

Here is a current "public" read only spreadsheet. http://spreadsheets1.google.com/ccc?key=tzbvU7NnAnWkabYmGo4VeXQ&hl=en This is in what Google now refers t as it's old format, I've tried both (old and new).. don't know if that makes any difference..

Answer 1

Google provide a documented way to access google spreadsheet via JSONP that works for normal gmail.com accounts. In short:

• Create a spreadsheet
• Click on the dropdown next to "Share" and select "Publish as a web page"
• Copy and paste out the key from the URL that shows (i.e. the bit after &key=)
• Go to https://spreadsheets.google.com/feeds/cells/0AmHYWnFLY1F-dG1oTHQ5SS1uUzhvTnZTSHNzMjdDaVE/od6/public/values?alt=json-in-script&callback=myCallback replacing "0AmHYWnFLY1F-dG1oTHQ5SS1uUzhvTnZTSHNzMjdDaVE" with whatever key you cut out of the url

To access this from within JavaScript you'll have to insert a HTML script tag into your document:

<script src="https://spreadsheets.google.com/feeds/cells/0AmHYWnFLY1F-dG1oTHQ5SS1uUzhvTnZTSHNzMjdDaVE/od6/public/values?alt=json-in-script&callback=myCallback"></script>

And you'll need to implement the callback function in your webpage:

function myCallback(spreadsheetdata) {
  // do something with spreadsheet data here
  console.log(spreadsheetdata);
}

You can simplify this with jQuery:

var url = "https://spreadsheets.google.com/feeds/cells/0AmHYWnFLY1F-dG1oTHQ5SS1uUzhvTnZTSHNzMjdDaVE/od6/public/values?alt=json-in-script&callback=?";
$.getJSON(url,{}, function (d) { console.log(d); });