October 14, 2021
Google Sheets can be a great place to store content for a website, since it's structured and easy to update (especially for non-coders).
Google used to have an obscure way of getting an API for reading a Google Sheet, but it stopped working in August 2021 when they deprecated the Sheets API v3.
However, Iâve built a free API that you can use for this, which doesnât require authentication or complicated permissions.
opensheet is an open source project I built for fetching a Google Sheet as live JSON data.
The URL format for using the API is:
https://opensheet.elk.sh/spreadsheet_id/tab_name
Replace spreadsheet_id
with the ID in the URL of the Google Sheet. Hereâs the part you should copy and paste:
Next, replace tab_name
with the name of the tab that you to want to get the values from. You can find and rename the tabs at the bottom of Google Sheets:
And thatâs it! You can click here to see an example API response.
The data gets returned as an array of objects, where the key is the name of the column and the value is a cellâs value.
This example uses fetch
, a modern built-in way to make HTTP requests with JavaScript.
fetch(
"https://opensheet.elk.sh/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/Test+Sheet"
)
.then((res) => res.json())
.then((data) => {
data.forEach((row) => {
// Do something with each row here.
});
});
You can check out the code for opensheet here, but youâre welcome to use my hosted instance at opensheet.elk.sh
.
You can also check out the documentation for some more information, including how to fetch sheets by their number/index instead of their name.
If you have any questions about how to use this little utility API, please reach out ([email protected])! Iâd love to help.
Also, if youâre not a programmer and youâd like help integrating Google Sheets, send me a quick message (again, [email protected]) and I can help out!
Tufts Meal Plan Wrapped
Mar 2, 2024
Building an e-ink picture frame that displays an iCloud photo album
Jan 9, 2024
2023 in review
Jan 5, 2024
Subscribe to my newsletter for a monthly round-up of new blog posts and projects Iâm working on!