How to retrieve data from Google Spreadsheet to Javascript or JSON?

Question

This is public spreadsheet created using Google Drive: https://docs.google.com/spreadsheets/d/1hA4LKZn9yKoqnSzaI6_73GQSj_ZVpB3O0kC93QM98Vs/pubhtml

How to retrieve data from Google Spreadsheet to Javascript or JSON with new Google Spreadsheets API version 3.0 ?

Answer 1

You can access a cell-based basic feed using the following URL structure: https://spreadsheets.google.com/feeds/cells/1hA4LKZn9yKoqnSzaI6_73GQSj_ZVpB3O0kC93QM98Vs/od6/public/basic?alt=json . By default the feed is in XML form, however, you can request JSON format using alt=json query parameter.

This feed also supports JSONP requests, so an example of fetching this data from a browser with jQuery might look like:

var url = "https://spreadsheets.google.com/feeds/cells/1hA4LKZn9yKoqnSzaI6_73GQSj_ZVpB3O0kC93QM98Vs/od6/public/basic?alt=json";
$.ajax({
    url:url,
    dataType:"jsonp",
    success:function(data) {
        // data.feed.entry is an array of objects that represent each cell
    },
});

Answer 2

If, alternatively, you want to keep it all in the Google environment. if you're looking for a more controlled JSON generator, check out this gist:

JSONPuller

It takes in a Spreadsheet sheet and returns an array of objects, with the row that you decide as the key (defaults to whichever row is frozen)

Cheers,