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.

  • Thiago Franca

    Hi Paul, great article !! I am having a issue when trying to write to the sheet: The API returned an error: Error: Request had insufficient authentication scopes. Any clue what can be happening ? Thanks

  • Digi Pro

    Ran into issue. I was using Node.js command prompt and had to type the code. Confused I and l. Now I cant get the program to run. It is giving error: UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 1): TypeError: Cannot read property ‘access_token’ of null

    • Paul Shan

      You probably haven’t given the right path of the file where you’ve stored the access_token

  • Manju

    Paul the article helped me a lot to create some intensive bots. I’ll buy you a cup of coffee. I’m from bengaluru too. You can reach me at dmanjunath@live.com

    • Paul Shan

      haha… Great to see my posts helping others 🙂

  • Manish askani

    Thanks a ton mate!! Done a lot of surfing, but in the end your article helped me a lot! 🙂

  • Great compliment to Google’s API document!

    I have a related question, how can I get the file ID for my Google sheet ID given its name. I now can manually copy the id from the URL, but as an application, it’s much more user friendly to read the sheet file by its name.

    I surfed on the net, it seems that I may need to use Google drive API for the purpose? Do I need to do another round of OAuth for the separate API of Google drive? It would be great if you could share an example of reading a sheet file by its name.

    Thanks a lot!

    Yu

    • Paul Shan

      Couldn’t got anything from sheet api, regarding name base reading.
      I don’t think even drive api (even sheet is a drive file only) will have such feature, as handling things with id is more reliable than name.
      I can of extreme need, you probably can fetch all files of your drive and get the matching fileName by iterating on them. let me know as well if you find any solution for this scenario.
      All are v4 apis only, no need for two oAuth.

  • huesoamz

    Thanks for share!, very good tutorial, only miss :
    node authentication.js
    node index.js

    and explain how take the “token” from the url and copy in the console window.

    Anyway…I have some doubts, Only once I need the token and its done forever?

  • Mangesh Dareakar

    Thanks .. great work
    can you please post delete row code also.