Hello and welcome to this tiny (but hopefully mighty) utility package that will help you fetch, read and process data from a Google Sheet without the faff of having to deal with the Google Sheets API. Note: v2.0.0 of this package introduced breaking
changes and a significant rewrite of the internals of the package. This is due to Googles end of support for the Google Sheets API v3, forcing a migration to v4. If you follow this readme doc you'll be fine, the biggest change will be that you need to create your own API key using Google Cloud Console. Skip to the how to use section or read on for more details. Google Sheets offers a really straightforward means to access data held in a Google Sheet via a special script call appended to a Sheet's published URL. That is, the Google Sheet must be made public to be able to call it. This returns a JSON-style bundle of data which can be processed without the need for using the more complex Google Sheets API
library and jumping through the fairly complex hoops to get it working. The Google Sheets Reader allows you to simply call a function, pass in some options (including the Sheet id and an API key) and run a callback function with the returned, processed, formatted results. This package is a great option if some of the following needs suit your application: On the other hand, if you have some of the needs below
apply more to you, then you might need to explore the Google Sheets API instead: Right, the good part - getting started and using the reader! The essential setup steps look like this: First things first, let's get things installed.
Run the following npm command from your favourite command line: or, if you're more Yarn inclined (like I am), you'll need:
And that's all there is to it. Set up a Google SheetIf you have an existing Google Sheet you'd like to use, great! Otherwise, make a new one, enter your data and then make it public by following the publishing steps below:
This is what your settings screen should look like... Next, you'll need to make the sheet available to anyone with the link (in read-only mode):
This is what the box looks like: And that's all the Sheet setup work done! Get your Sheet idYou'll need the id value of your particular Sheet in order to fetch the results. You can either get this from the URL of the open Sheet, or by clicking the 'Share' button and then 'Get shareable link'. Either way, what you'll end up with is a URL that looks like this: https://docs.google.com/spreadsheets/d/1_IpENDkoujmWr-B0M2ZVcyvgPQGeKwYxfHX_JYTDtRc/edit you want the part between the /d/ and the next / character. So, for the above URL, the Sheet id would be:
Generate a Google Sheets API keySince Google introduced Sheets API v4, you'll need to create a Google Cloud Project and generate an API key to access any information from a Google Sheet. Effectively you need to set up a new Google Cloud Project, then enable the Google Sheets API access for that project, and finally, generate an API key.
There is more information in these guides:
Call the reader functionNow the exciting part - calling the actual reader! First, import or require the package in your JavaScript file:
The reader uses the JavaScript Because we're dealing with JavaScript Promises, in order to call and use the reader, you'll need to pass in an options object (explained below) and a callback function that will be passed the returned results from your Sheet. GSheetReader( options, results => { // do something with the results here }, error => { // OPTIONAL: handle errors here }); Handling errorsThere's a good change something might go wrong at the fetching from GSheets end. It could be that you've entered an incorrect Sheet Id, that the Sheet isn't publicly available yet (it's not an instant publish process from Google), or some other in-transit error. To handle this, we've got a couple of options:
Using a |