Upload data to a Google Sheet with Node.js

Easy way to upload and update data in Google Sheets

Sometimes I have to get data and post it in Google Sheets, why? Because it is easier to read and understand for stakeholders, specifically non-devs.

So, I started to read about Google Sheets API and I found this guide and I started to work with it. But I found that it is not easy to understand and I want to make it easier for everyone.

So, I created this repository to help you to upload data to Google Sheets with Node.js.

Setup

1. Create a Google Sheet
2. You will need to create a Google Cloud Platform project and enable the Google Sheets API.
3. Create a service account and download the JSON credentials file.
4. Create a .envfile in the root of the project and add the following:

GOOGLE_APPLICATION_CREDENTIALS=YOUR_CREDENTIALS_HERE
SHEET_ID=YOUR_SHEET_ID_HERE
SHEET_RANGE=YOUR_SHEET_RANGE_HERE

The JSON Credentials may have be stringify and have to replace \\n with \n in the credentials, otherwise you will get an error. And clear "at the start and the end.

Delete “ and replace \\n for \n.

5. Set the credentials in the Secrets repository in GitHub.

6. Google gives you a google service account email. You have to share your google sheet with this email.

7. Finally, you have to set your preferences to run it automatically with GitHub Actions, you must do this in the file .github/workflows/update.yml

Run locally

1. Run npm install to install the dependencies.

2. Run npm startto start the program.

Example

I created a repository with an example of how to use this library.

1. I work with this API to get data from CMF Chilean Indicators. You can use any API you want. If you need to understand the API that I used, see the documentation. I get the data on example.js file.

2. Then I want to upload the data to this Google Sheet.

First you have to set the SCOPES and authorize in GCP. Then we used a function to validate the JSON, because we’ll use to authenticate.

Then we delete the data and upload the new ones.

Function to clear data.

Function that includes clearData and uploads the new data.

3. In index.jsyou have to change the function transformDatato match your data with Google Sheets API format (array of arrays).

4. Finally, main function gets the data, transforms it and uploads it to google sheets.

5. Run npm start to upload the data to your Google Sheet.

This clears the data and then loads the new data, in case you want to load data with a different range each time.