Google Sheet to json

A free API to transform any Google Sheet to a JSON file

GS2JSON is a free api which will allow you to transform any Google Sheet to a JSON file.

Simply append your public spreadsheet url to: https://labs.zero.sh/api/gs2json?url=your-public-speadsheet-url - Nothing else needed!

It's free to use and doesn't require an API key or authentication. The code is available on Github

With Zero you can build great interactive dashboards pulling data directly from a Google Sheet using this API. It's free! Read more in our blog post: Create a dashboard with Google Sheets

1. Make sure that your sheet is public

This API can read only public sheets. To make your sheet public open it and then click on:

  1. File (On the top left of the Google Sheet window)
  2. Publish for the web
  3. Publish

At this point you can close this popup and copy the full page URL. Directly from your browser's address bar.

2. Format your request

This API accepts only GET requests to this endpoint: https://labs.zero.sh/api/gs2json

The following query parameters are allowed:

Since the spreadsheet url often contains a hashtag symbol (#) make sure you add the URL as the last parameter or you risk that the others will be ignored, for example:

  • Do ?format=2&url=https://docs...
  • Don't do ?url=https://docs...&format=1

The following parameters are accepted:

  • format: You can chose the format of your response. Possible values are 1,2 & 3. Read about these below.
  • sheet: The number of the sheet in the spreadsheet (Defaults to the first sheet if nothing is passed)
  • url: This is the full spreadsheet url, something like: https://docs.google.com/spreadsheets/d/12345
  • id: Instead of the full url, you can also simply pass the id like: 12345

3. Format your response

You can chose to have your data returned in 3 different ways, use the format parameter to chose the response your prefer.

  1. An object without columns names
  2. An object with column names
  3. An Array of the rows with mapped column names

Example:

With the following spreadsheet: https://docs.google.com/spreadsheets/d/1zdJnlYIO2dEMMDLJ5J_ny1uetbTVfQtinYW0QWAv9I8

NameAge
Sara35
Tom50

The response will be:

Questions?

Feel free to send us an email at support@zero.sh or open an issue on Github

Last Updated: April 8, 2021

Need Help?

Have a look at the docs or the examples above

Questions?

Chat with us or leave a message below