How to read or modify spreadsheets from Google Sheets using Node.js ?
Tags: googleapi, node, googlesheets
Published: 2019-09-25 17:12:40


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
1. Enable Google Sheets API in our project on Google developers console
1. Create a service account 
1. Share the spreadsheet created in step 1 with the service account created in step 3
1. Write a Node.js service to access the google sheets created in step 1 using the service account credentials
1. 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:

![Screenshot-from-2019-09-21-13-19-40](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-13-19-40.png)

### 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_

![Screenshot-from-2019-09-21-12-02-33](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-02-33.png)

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

![Screenshot-from-2019-09-21-12-03-16](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-03-16.png)

Click on _Create Credentials_ button at the top and select _Service Account_ option

![Screenshot-from-2019-09-21-12-03-26](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-03-26.png)

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

![Screenshot-from-2019-09-21-12-04-01](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-04-01.png)

Click _Continue_ on the next dialog

![Screenshot-from-2019-09-21-12-04-15](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-04-15.png)

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.

![Screenshot-from-2019-09-21-12-04-56](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-04-56.png)

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

![Screenshot-from-2019-09-21-12-05-42](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-12-05-42.png)

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. 

![Screenshot-from-2019-09-21-13-49-42](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-13-49-42.png)

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`)

![Screenshot-from-2019-09-21-20-16-13](http://codingfundas.com/content/images/2019/09/Screenshot-from-2019-09-21-20-16-13.png)

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](https://github.com/googleapis/google-api-nodejs-client)
- [Official Google Sheets API reference](https://developers.google.com/sheets/api/reference/rest)


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 :-)