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

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,

Answer 2

I made it work using opensheet.

Steps:

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