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..

Mark Fowler · Accepted Answer · 2011-03-07 11:46:50Z

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); });

dartdog · Accepted Answer · 2011-03-08 04:58:14Z

0

I have implemented a fairly complete example and the code is at https://bitbucket.org/tbrander/ggadget/wiki/Home Code is BSD license (except for Trademarks and institutional markings which are all rights reserved) It is reasonably well commented... It is in operation at http://acre.cba.ua.edu/ (bottom of page) Stand alone at : http://acre.cba.ua.edu/mobiletool/res.html

It functions across IE, Chrome FF i-Phone and Android Your hints above are close but I was looking for yet more... as You can now see,, But I will explore the Jquery syntax as the current implementation is pure JS

answered Mar 8, 2011 at 4:58

dartdog

10.9k22 gold badges75 silver badges122 bronze badges

3 Comments

Arsen Mkrtchyan Over a year ago

Links are broken :(

dartdog Over a year ago

Stuff ages sorry

dartdog Over a year ago

Stuff ages sorry but the code in the answer should get you going. (I think)

Collectives™ on Stack Overflow

Access Google-apps public spreadsheet via Javascript

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related