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 ?

[See here for detailed answer](http://stackoverflow.com/a/26774243/1922144) — davidcondrey, Nov 06 '14 at 07:51
Could you help me in the following problem? https://stackoverflow.com/questions/57023533/is-there-any-way-to-have-google-sheets-cell-values-as-dynamic-variable-into-a-js — KDVM943, Jul 22 '19 at 18:15

score 8 · Accepted Answer · answered May 20 '14 at 13:43

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

score 1 · Answer 2 · answered Nov 12 '17 at 18:13

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,

score 0 · Answer 3 · answered May 02 '22 at 10:39

I made it work using opensheet.

Steps:

Make sure the sheet as read access - Anyone with the link can view it.
Sheet should have First row as Header row with titles
Then get the sheet data as json using the following link - https://opensheet.elk.sh/spreadsheet_id/sheet_name
Replace spreadsheet_id with your sheet id and sheet_name with your sheet name. No authentication is required.

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

3 Answers3

Linked