As a JAMStack developer, I have often found a situation where all I need is a way to get data and display it on a website. There are a lot of options out there, like creating a database with an API, using serverless technology like Firebase, or many other options. But that often seems a little overkill.
Sometimes, all I need is a spreadsheet as a read-only data source. Google Sheets is an appealing option because it is familiar to many people, is available as a web application, and makes storing and organizing data a breeze. Wouldn't it be nice to use this as a data source?
One solution I have used to get Google Sheet data was Tabletop.js which was written several years ago and no longer works. Other solutions out there cost money. Some seem a little questionable when you hand over your Google credentials allowing them access to your Google Drive.
Introducing: Extract GSheet
Since I wasn't happy with any of these solutions, I decided to write and publish my own library to extract data from a Google Sheet.
Extract GSheet is a front-end JavaScript library that extracts your published Google Sheet data making it available as a JavaScript object. It can even pull data from multiple tabs within the same document! From there, you can display the content however you would like. Unlike many other libraries, this library does not require an API key.
Example
Take a look at the following CodePen to see how easy it is to extract data from this published Google Sheet data.
Features
- Extract GSheet will pull out all text data in the table and convert it as needed. For example, "1" ⇒ 1, "TRUE" ⇒ true, and will convert checkboxes to a true/false value.
- It will use the first row in the sheet as the key for the key/value pairs.
- It will remove empty rows and columns to keep the data clean.
- If there is no column labeled
id, it will add an id for each non-empty row.
What This Won't Do
- Extract GSheet is intended to only read data from a Google Sheet. It cannot write data to a Google Sheet. If you need to update information on your website, you will need to edit the Google Sheet. Any changes on your Google Sheet should automatically publish once changed. However, some changes may take up to 5 minutes to reflect on your page.
- Extract GSheet relies on
DomParserandfetchwhich are not native to Node.js. Because of this, it will only work in a browser. - Extract GSheet will not extract non-text items such as images, comments, and charts.
- It does not work in Internet Explorer.
Documentation and Examples
You should have a pretty good idea of how easy it is to implement based on the CodePen above, but if you still need additional help, you can find more information in the documentation.
I also have examples of formatting data using different libraries and frameworks such as jQuery, Vue, React, and more. You can find those examples at https://ngblaylock.github.io/extract-gsheet/.
Questions?
If you have questions, drop them in the comments here, or let me know on GitHub. I hope you find this useful.
Top comments (0)