Read, write, update & create google spreadsheets by node.js with googleapis v4

As you’ve landed in this article, I’m sure all you want is how to code to connect google spreadsheets and you already know enough about how node.js and npm works (if you don’t have node.js installed, please install before you proceed). So instead of wasting time on theoretical talks, let me directly jump into the how to part step wise.

Get auth credentials

To make your node.js application connect to and manipulate your google spreadsheet, you need three things. client_id, client_secret and refresh_token. To get client_id and secret you need to perform the following tasks.

  • Login to your google account in your browser.
  • Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API.
  • After landing to the console page, click Continue button.
  • Now click on Go to credentials button.
  • On the Add credentials to your project page, click the Cancel button.
  • Click on the OAuth consent screen tab.
  • Select an Email address, enter a Product name if not already set, and click the Save button.
  • Select the Credentials tab (probably it’s already selected by now), click the Create credentials button and select OAuth client ID.
  • Select the application type Other and enter the name “WhatsoeverYouWant”, and click the Create button.
  • Click OK to dismiss the resulting dialog.
  • You should see a download icon at the right side. Click on this to download the json file containing credentials.
  • Rename the file as credentials.json and save in the root directory of your node application.

The above steps will complete generating the two important auth credentials; i.e. client_id and client_secret. So we need one more thing, which is refresh_token. Well, fetching refresh_token will be the part of our program itself. So let’s move to the code Now.

Setting up the program

First you need to install two npm packages. Both of them are by google.

npm install google-auth-library –save
npm install googleapis –save

I hope you’ve already added the credentials.json in the root folder of your node application. now create a file named authentication.js and paste the following code in it.

The file above will help generating and saving refresh_token. Once the token is saved, it will not ask again.

Now it’s time for an index.js where we will code to read/write the google sheet.

Read, Write, Create, Update operations

Hope you already have a spreadsheet created in your google account. Get the id of that sheet. You will find the id in the url of the sheet between spreadsheets/d/ and /edit#gid. Also remember the worksheet name, which you will find in the tabs at the bottom of your spreadsheet. Generally the default worksheet name is Sheet1.

Reading from spreadsheet

index.js

The code is very much self explanatory. The sheets.spreadsheets.values.get is the function to fetch your data. It takes the authentication object passed from our authentication.js and also the id of your spreadsheet. There’s an option called range which takes the name of the worksheet along with the details of your row and column from where you want to start reading. In this example I set the range as the Column A to column C; row 2 onwards in Sheet1. Hope this explains range for you.

Append or write

index.js

As you can see, in case of write or append you need to use sheets.spreadsheets.values.append(). Apart from the params which .get() was taking while reading, it takes two more params. valueInputOption which indicates how the data was inserted. This param is optional. The main param is resource which holds the data in its value parameter. You need to send the data as an array of arrays.

Update

In case of update just use .update instead of .append and it will replace the values with the newly provided one in the appropriate range.

Create a new sheet

index.js

The response object after creating the spreadsheet will contain a spreadsheetId in it. You can use that for your future use.

Hope the article above helped you to write a node.js program to communicate with your google spreadsheets. For any confusion or difficulty post a comment in the comment section below and I will try to address that.

About This Author

Hello! I am Paul Shan, a JavaScript Expert, Full Stack and DevOps Engineer cum Consultant based out of Bengaluru, India.