Google Sheets Data As JSON & JSONP

Google Sheets is a pretty simple way to make free spreadsheets and to quickly store data. Did you know that you can actually retrieve this data via JSON & JSONP? Here’s how you can do it in just a few easy steps:

1. Open the desired Google Spreadsheet.
2. Go to File » Publish to the web….
3. At the bottom of the Public to the web dialog click and expand the Published content & settings option and then click the Start publishing button and after confirming that you want to publish the document you can close this dialog.
4. Pull the Google spreadsheet ID from the URL (eg. 1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY from https://docs.google.com/spreadsheets/d/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/edit#gid=0)
5. Navigate to https://spreadsheets.google.com/feeds/{{VIEW-TYPE}}/{{SPREADSHEET-ID}}/{{TAB-NUMBER}}/public/values, replacing {{SPREADSHEET-ID}} with the spreadsheet ID from the previous step and {{TAB-NUMBER}} with the number of the tab within the workbook (eg. if it is the first tab you would replace it with 1).
6. If you would like each cell’s value including the cells in the first row, replace {{VIEW-TYPE}} with the word cells. If you are fine with the value of each cell within each row being indexed by the corresponding value in the first row, replace {{VIEW-TYPE}} with the word list.
7. If you want the output to be JSON you can add ?alt=json at the end of the URL. If you want the output to be JSONP you will need to add ?alt=json-in-script&callback= followed by the name of the function to which the JSON object will be passed (eg. ?json-in-script&callback=myFunc).
8. Navigate to this URL to see the JSON representation of your spreadsheet. Here are some working example URLs:
   • JSON Cells View
   • JSONP (call to myFunc) Cells View
   • JSON List View
   • JSONP (call to myFunc) List View

Admittedly this is the old way of retrieving Google Spreadsheet data so this solution may not work forever, but it does for now. Happy coding! 😎