Repository files navigation

A free API for getting Google Sheets as JSON.

Tutorial blog post:benborgers.com/posts/google-sheets-json

If you have questions:benborgers.com/contact

This API returns a given Google Sheet’s rows as JSON data. In order to use it, the first column of your Google Sheet should be column headers.Here’s an example.

The format for this API is:

https://opensheet.vercel.app/spreadsheet_id/sheet_name

For example:

https://opensheet.vercel.app/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/Test+Sheet

You can also replacesheet_name with the sheet number (in the order that the tabs are arranged), if you don’t know the name. For example, to get the first sheet:

https://opensheet.vercel.app/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/1

Take note that the first sheet in order is numbered1, not0.

This section is only necessary if you want to fork opensheet or host your own instance of it onVercel. If you don’t want to deal with that, you’re welcome to use my hosted instance atopensheet.vercel.app.

If you host opensheet in your own Vercel account or make a fork, you’ll need to get your own Google Sheets API credentials:

1. Go to theGoogle Cloud Console and create a new project from the top navigation bar.
2. Search for “Google Sheets API” and enable it.
3. On the left bar, go to Credentials and click “Create Credentials” → “Service account”.Service accounts are Google’s concept for a Google account that you can control programmatically.
4. Fill in any reasonable name, and skip the next two optional steps. Click “Done” to create the set of credentials, which will allow you to access Google Sheets using the API.
5. Click on this newly created service account, and then go to the “Keys” tab. Create a key of type JSON.
6. A JSON file containing the service account’s credentials will be downloaded. Open up that file, and copy its entire contents.You should paste this whole thing into theGOOGLE_SERVICE_ACCOUNT environment variable on the Vercel dashboard for your own deployment of opensheet.

Install theVercel CLI, and then run:

vercel dev