This is a simple example of how to upload data to a Google Sheet using Node.js.
- Create a Google Sheet
- You will need to create a Google Cloud Platform project and enable the Google Sheets API. See this guide for more information.
- Create a service account and download the JSON credentials file. See this guide for more information.
- Create a
.env
file in the root of the project and add the following:The JSON Credentials file should be saved in env file as
GOOGLE_APPLICATION_CREDENTIALS
. See this guide for more information. Then you have to replace\\n
with\n
in the credentials, otherwise you will get an error.
GOOGLE_APPLICATION_CREDENTIALS=YOUR_CREDENTIALS_HERE
SHEET_ID=YOUR_SHEET_ID_HERE
SHEET_RANGE=YOUR_SHEET_RANGE_HERE
- Set the credentials in Secrets repository in GitHub. See this guide for more information.
- Google gives to you an google service account email. You have to share your google sheet with this email. See this guide for more information.
- 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
.
- I work with this API to get data from CMF Chilean Indicators. You can use any API you want.
Documentation I get the data on
example.js
file. - Then I want to upload the data to this Google Sheet.
- In
index.js
you have to change the functiontransformData
to match your data with Google Sheets API format (array of arrays).Set values in range of a spreadsheets, documentation.
- 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.
- Run
npm install
to install the dependencies. - Run
npm start
to start the program.