Latest Tweet

How to access Google Spreadsheets with Node

For simple applications, you don’t always need a powerful backend with complex query mechanisms.
It’s often enough to store the information in a file or spreadsheet.
Using spreadsheets has the additional advantage that even non-developers understand and can make changes to the data and maintain your app.
I’ll show you how you can use private Google Spreadsheets to store your data and make the securely accessible from within your Node app.

Creating a service account

There are several ways to programmatically connect to a private Google Spreadsheet.
If you ever implemented client authorization through Google’s OAuth2 login, you probably know how hard and painful it can be to get such a simple thing as a login working.
Luckily, there is an easy way to access Google Spreadsheets through Google Service Accounts.

Click on ”Create a service account”. Give the service account a name. This will determine its ”Service Account ID” which is the email your service account will have. Enter the role of ”Project” -> ”Editor”. Tick ”Furnish a new private key”. Click ”Create”. The credentials will now be downloaded as a JSON file.

Now the service account is created, but we still need to enable the Google Drive API for the project in order to access Spreadsheets.

Go to the APIs & Services Page of the project. Search for Google Drive API and click on enable.

Share the Spreadsheet with the service account

The last step is to share the spreadsheet with the Service Account ID ...@gserviceaccount.com. The service account will automatically accept the invitation.

Accessing the Spreadsheet with Node

Rename the credentials file you downloaded when creating the Service account to service-account.json, and place it in the NPM root directory. (If it’s a git repo, you should add it to your .gitignore because the private key should not be leaked.)

The service-account should contain info about the project, the service account, and its private key:

Some explanation here: The SPREADHSHEET_ID is part of the google spreadsheet URL, like so https://docs.google.com/spreadsheets/d/15k0AIsePcdbLNglRyhm3slOvagFdYQcgLYS4aw3z-kM/.

I’m using promisify to convert the Node callback-style functions from google-spreadsheet to return a Promise instead. This makes the code much more readable, without Promises and async / await you’ll end up with deeply nested code blocks.

Reading rows

“The list feed contains all rows after the first row up to the first blank row. The first blank row terminates the data set.”

Which means you won’t get the header row with the getRows function and as soon as there’s an empty row, you don’t get any rows after it.
That’s why I recommend using the getCells function instead which gives you full control over what you want to read.

This will read the first two columns of rows 1 to 5, and return the individual cells. You can also write to the individual cells and save the updates. This way, you can easily read and write to your spreadsheet backend.