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


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


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.


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


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

    • 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!


    • 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.

  • Amit Sood

    Hi, I am able to read the google sheet content using your code but when i try to append data then i get “The API returned an error: Error: Request had insufficient authentication scopes.” error. Any idea why i am getting this

  • Mathew Sebastian

    Thanks, helped me a lot. Your addSheet function is to create a spreadsheet and not to add a sheet to an existing spreadsheet right? Maybe you can add the below function, its to add a sheet to an existing spreadsheet.

    function addSheet(auth) {
    var sheets = google.sheets(‘v4’);

    auth: auth,
    spreadsheetId: ‘spreadsheet-id-here’,
    resource: {
    requests: [{‘addSheet’:{‘properties’:{‘title’: ‘name-of-sheet’}} }],
    }, (err, response) => {
    if (err) {
    console.log(‘The API returned an error: ‘ + err);
    } else {
    console.log(“Created sheet”);

  • Wes Henrie

    Hi there! Thanks for the guide! I have been running into trouble trying to get this to work. When i run my index.js file i keep getting the following error:

    Unhandled promise rejection (rejection id: 2): TypeError: auth.OAuth2 is not a constructor

    I originally was getting this error:
    UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 2): TypeError: googleAuth is not a constructor

    I looked on the google-auth-library github and it had mentioned setting up the code as:
    const {GoogleAuth} = require(‘google-auth-library’);
    const auth = new GoogleAuth();

    When I made the appropriate changes in the authentication.js file I started getting the auth.OAuth2 error rather than the googleAuth error.

    Any ideas why this might be happening?

  • Stephen Rolfe Nielsen

    Excellent post! Someone needed to write these examples out clearly aside from the google docs!

    Now, no matter what I try, your append/write example produces an error. All my code is exactly as specified from this post. Can you reproduce or am I crazy?

    The API returned an error: Error: 'valueInputOption' is required but not specified

    • Tiago

      I had this problem to Error: ‘valueInputOption’ is required but not specified

    • David U Sunico

      Hi, change the googleapis package version ^25.0.0 to version 24.0.0. It should work as expected. There must have been a bug introduced in a recent update.

    • Stephen Rolfe Nielsen

      Ah! That did the trick. Thanks for replying!

  • Yegeon Yeggy Seo

    Hey, Thank you a lot for this. I just have one question though. Lets say I have a bunch of rows of data in a spreadsheet. If I want to update one specific row using the code above, how does spreadsheet know what row to update? or is this one of “google’s magic”??

    • Paul Shan

      “range” parameter helps here.

  • Luis Eduardo Granados

    I’m stuck here:

    Authorize this app by visiting this url:
    https://…(rest of the url)
    Enter the code from that page here:

    So, I go to the website… I select the ID, then click “Allow” and says:
    Cannot get /

    Any ideas?

  • LogaKrishnan

    How to update multiple ranges with multiple data?

  • Buse Odacı

    Hey, how can we convert this project as a Rest API and reach this API from Angular ?