How to read or modify spreadsheets from Google Sheets using Node.js ?

First of all, a brief overview of our use case. Let's say I have a spreadsheet on Google Sheets which is not public and I want to be able to read/modify programmatically through some batch process running on my local machine or some server. This is something I had to do recently with a Node.js application and I found the authentication part a bit tricky to understand. So I thought of sharing my solution and I hope it helps someone in need. There might be better ways of doing this but I am sharing what worked best for me.

Since there is no user interaction involved in our use case, we don't want to use the OAuth process where user needs to open a browser and sign in to their Google account to authorize the application. For scenarios like this, Google has a concept of service account. A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs. Just like a normal account, a service account also has a email address (although it doesn't have an actual mailbox and you cannot send emails to a service account email). And just like you can share a google sheet with a user using their email address, you can share a google sheet with a service account as well using their email address. And this is exactly what we are going to do in this tutorial. We will create a spreadsheet on Google Sheets using a regular user, share it with a service account (that we will create) and use the credentials of the service account in our Node.js script to read and modify that sheet.

Pre-requisites

This tutorial assumes that you have:

• Experience working with Node.js
• A Google account
• A project setup on Google developers console where you have admin priveleges

Steps Overview

Here is the list of steps we will be following through this tutorial:

1. Create a spreadsheet on Google sheets
2. Enable Google Sheets API in our project on Google developers console
3. Create a service account
4. Share the spreadsheet created in step 1 with the service account created in step 3
5. Write a Node.js service to access the google sheets created in step 1 using the service account credentials
6. Test our service written in step 5

Now that we have an outline of what all we are going to do, let's get started

Step 1: Create a spreadsheet on Google Sheets

This one doesn't really need any instructions. You just need to login to your google account, open Google Drive and create a new Google Sheet. You can put some random data in it. One thing that we need to take note of is the sheet's id. When you have the sheet open in your browser, the url will look something like this: https://docs.google.com/spreadsheets/d/1-XXXXXXXXXXXXXXXXXXXSgGTwY/edit#gid=0. And in this url, 1-XXXXXXXXXXXXXXXXXXXSgGTwY is the spreadsheet's id and it will be different for each spreadsheet. Take a note of it because we will need this in our Node.js script to access this spreadsheet. For this tutorial, here is the data we have stored in our spreadsheet:

Step 2: Enable Google Sheets API in our project on Google developers console

We need to enable Google Sheets API for our project in order to be able to use it. This tutorial assumes that you already have a project in Google developers console so if you don't have one, you can create a new one very easily. Once you have the project on Google developers console, open project dashboard. There you should see a button Enable APIs and Services.

Click on it and search for Google sheets API using the search bar. Once you see it, click on it and then click on Enable

Step 3: Create a Service Account

Once you enable Google Sheets API in your project, you will see the page where you can configure the settings for this API. Click on Credentials tab on the left sidebar. Here you will see a list of OAuth client IDs and service accounts. By default there should be none.

Click on Create Credentials button at the top and select Service Account option

Enter the name and description of the service account and click Create button.

Click Continue on the next dialog

On the next dialog, you get an option to create a key. This is an important step. Click on the Create Key button and choose JSON as the format. This will ask you to download the JSON file to your local machine.

For this tutorial, I have renamed the file and saved it as service_account_credentials.json on my local machine.

Keep it somewhere safe. This key file contains the credentials of the service account that we need in our Node.js script to access our spreadsheet from Google Sheets.

Once you've followed all of these steps, you should see the newly created service account on the credentials page

Take a note of the email address of the service account. We will need to share our spreadsheet with this account.

Step 4: Share the spreadsheet created in step 1 with the service account created in step 3

Now that we have a service account, we need to share our spreadsheet with it. It's just like sharing a spreadsheet with any normal user account. Open the spreadsheet in your browser and click on the Share button on top right corner. That will open a modal where you need to enter the email address of the service account. Uncheck the checkbox for Notify people since this will send an email and since service account does not have any mailbox, it will give you a mail delivery failure notification.

Click OK button to share the spreadsheet with the service account.

This completes all the configuration steps. Now we can get to the fun part :-)

Step 5: Write a Node.js service to access the google sheet using the service account credentials

We will create our script as a service that can be used as a part of a bigger project. We will call it googleSheetsService.js. It will expose following APIs:

• getAuthToken
• getSpreadSheet
• getSpreadSheetValues

The function getAuthToken is where we will handle the authentication and it will return a token. Then we will be using that token and pass it on to other methods.

We will not be covering writing data to the spreadsheet but once you get the basic idea of how to use the API, it will be easy to extend the service to add more and more functions supported by the Google Sheets API.

We will be using the googleapis npm module. So, let's get started by creating a directory for this demo project. Let's call it google-sheets-demo.

cd $HOME
mkdir google-sheets-demo
cd google-sheets-demo

Copy the service_account_credentials.json file that we created in step 3 to this directory (google-sheets-demo). And create our new file googleSheetsService.js. Paste the following lines to the file:

// googleSheetsService.js

const { google } = require('googleapis')

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

async function getAuthToken() {
  const auth = new google.auth.GoogleAuth({
    scopes: SCOPES
  });
  const authToken = await auth.getClient();
  return authToken;
}

module.exports = {
  getAuthToken,
}

For now our service has only one function that returns the auth token. We will add another function getSpreadSheet soon. First let us see what our function does.

First, we require the googleapis npm module. Then we define SCOPES. When we create an auth token using google APIs, there is a concept of scopes which determines the level of access our client has. For reading and editing spreadsheets, we need access to the scope https://www.googleapis.com/auth/spreadsheets. Similarly, if we only had to give readonly access to spreadsheets, we would have used scope https://www.googleapis.com/auth/spreadsheets.readonly.

Inside the getAuthToken function, we are calling the constructor new google.auth.GoogleAuth passing in the scopes in the arguments object.

This function expects two environment variables to be available, GCLOUD_PROJECT which is the project ID of your Google developer console project and GOOGLE_APPLICATION_CREDENTIALS which denotes the path of the file containing the credentials of the service account.

We will need to set these environment variables from the command line. To get the project ID, you can get it from the url of the project when you open it in your web browser. It should look like this

https://console.cloud.google.com/home/dashboard?project={project ID}

And GOOGLE_APPLICATION_CREDENTIALS must contain the path of the service_account_credentials.json file. So, go to the terminal and from the google-sheets-demo directory, run the following commands to set these environment variables:

export GCLOUD_PROJECT={project ID of your google project}
export GOOGLE_APPLICATION_CREDENTIALS=./service_account_credentials.json

You need to make sure that you have the credentials file copied in the current directory.

Now we will add two more functions to our service:

• getSpreadSheet
• getSpreadSheetValues

The first one will return metadata about the spreadsheet while the second one will return the data inside the spreadsheet. Our modified googleSheetsService.js file should look like this:

// googleSheetsService.js

const { google } = require('googleapis');
const sheets = google.sheets('v4');

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

async function getAuthToken() {
  const auth = new google.auth.GoogleAuth({
    scopes: SCOPES
  });
  const authToken = await auth.getClient();
  return authToken;
}

async function getSpreadSheet({spreadsheetId, auth}) {
  const res = await sheets.spreadsheets.get({
    spreadsheetId,
    auth,
  });
  return res;
}

async function getSpreadSheetValues({spreadsheetId, auth, sheetName}) {
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId,
    auth,
    range: sheetName
  });
  return res;
}


module.exports = {
  getAuthToken,
  getSpreadSheet,
  getSpreadSheetValues
}

At the top we have added a line

const sheets = google.sheets('v4');

This is to use the sheets API. Then we have added the two new functions getSpreadSheet and getSpreadSheetValues. To see all the supported API endpoints for Google Sheets API, check this link https://developers.google.com/sheets/api/reference/rest.

For our demo, we are only using two of those. The getSpreadSheet function expects auth token and the spreadsheetId as its parameters. And the getSpreadSheetValues expects one additional parameter that is the sheetName from which to fetch the data. By default, a spreadsheet only contains a single sheet and it is named as Sheet1. Finally we export the newly added functions via module.exports.

This completes our googleSheetsService. If you need to support more API functions, you can check the reference using the link above, add the corresponding wrapper functions in this service and export it using module.exports. For any consumer of this service, they will first need to call the getAuthToken function to get the auth token and then pass on that token to the subsequent functions like getSpreadSheet, getSpreadSheetValues, etc. Now that we have our service ready, we just need to test it to make sure it is working fine

Step 6: Test our service

So we have our service ready. But does it work? Let's check that out.

While typically, we would use a testing framework to run unit tests, to keep this tutorial simple, we are going to write a simple Node.js script. From our project's directory, create a new file called test.js and copy paste the following contents:

const {
  getAuthToken,
  getSpreadSheet,
  getSpreadSheetValues
} = require('./googleSheetsService.js');

const spreadsheetId = process.argv[2];
const sheetName = process.argv[3];

async function testGetSpreadSheet() {
  try {
    const auth = await getAuthToken();
    const response = await getSpreadSheet({
      spreadsheetId,
      auth
    })
    console.log('output for getSpreadSheet', JSON.stringify(response.data, null, 2));
  } catch(error) {
    console.log(error.message, error.stack);
  }
}

async function testGetSpreadSheetValues() {
  try {
    const auth = await getAuthToken();
    const response = await getSpreadSheetValues({
      spreadsheetId,
      sheetName,
      auth
    })
    console.log('output for getSpreadSheetValues', JSON.stringify(response.data, null, 2));
  } catch(error) {
    console.log(error.message, error.stack);
  }
}

function main() {
  testGetSpreadSheet();
  testGetSpreadSheetValues();
}

main()

This file contains two test functions and a main function that is calling those test functions. At the bottom of the file, we are executing the main function. This script expects two command line arguments:

• spreadsheetId (this is the ID that we got from step 1)
• sheetName (this is the name of the worksheet for which you want to see the values. When you create a new spreadsheet, it is Sheet1)

Also, ensure that the env variables GCLOUD_PROJECT and GOOGLE_APPLICATION_CREDENTIALS are set properly.

Now, from the terminal, run this script

node test.js <your google sheet's spreadsheet id> <sheet name of the worksheet>

If you have followed all the steps correctly, you should see output like this:

output for getSpreadSheet {
  "spreadsheetId": "1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY",
  "properties": {
    "title": "test-sheet",
    "locale": "en_US",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "Asia/Calcutta",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false
      }
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    }
  ],
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1-jG5jSgGTwXXXXXXXXXXXXXXXXXXY/edit"
}
output for getSpreadSheetValues {
  "range": "Sheet1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "Name",
      "Country",
      "Age"
    ],
    [
      "John",
      "England",
      "30"
    ],
    [
      "Jane",
      "Scotland",
      "23"
    ],
    [
      "Bob",
      "USA",
      "45"
    ],
    [
      "Alice",
      "India",
      "33"
    ]
  ]
}

If you get an error, it means you have not followed all the steps correctly. For this tutorial, the version of googleapis npm module was 43.0.0. You might face issues if you are using older version of the module. Make sure the spreadsheetId and sheetname are correct and the enviroment variables are set properly. If you still get error, you should check the error message and code to see what might be causing the problem.

References

• Documentation for Google API Node.js client
• Official Google Sheets API reference

I would definitely recommend checking out these references (especially the Official Google Sheets API reference) to get a more in depth understanding of the sheets API and how to use the Node.js client.

Hope you found this tutorial helpful. Thanks and happy coding :-)