How to use the google-spreadsheet package to connect with Google Sheets API

Disclaimer

Before jumping in, I want to preface that my code may not be “optimal” or refactored exactly as it should be.

As someone new to back-end development, I’m focused on creating things that work, before worrying about how scalable the application may be.

I also should mention that this article is created specifically for those using Node.js, although I’m sure it can be tailored for other backends. I won’t go through the set-up in other back-end languages.

What will be covered in this blog

1. Basic comparison between Google Sheets API v3 and v4
2. How to enable the Sheets API and create a Service Account to work with
3. How to integrate google-spreadsheet package into your NodeJS application

What will not be covered in this blog

1. This blog will not teach you How to use the google-spreadsheetspackage and all it’s features
2. It’ll not cover detailed comparisons on Google Sheets API v3 and v4
3. How to use Node.js or Express.js from the ground up
4. How to use the API across other back-end languages

There are multiple ways to connect to the Google API

1. Google Sheets HTTP requests (v3)
2. Google OAuth2 (v4)
3. Google Developer console (service account) and google-spreadsheet package (v4)

Note: This blog is going to discuss about the 3rd method i.e. Service account and google-spreadsheet package

Comparison between v3 and v4

Reasons to use v3

1. It’s super simple to set up

Reasons to use v4

1. v3 will be deprecated anyway (Stay up-to-date)
2. Better response structure
3. More flexibility in your requests
4. You get read/write permissions. (v3 only provides read access)

Setting up the Google Sheet and service account

Here comes the juicy part, we’re now going to go through all steps for connecting your Service account with google-spreadsheet package and your sheet

Step 1: Installing the necessary packages

For the simplicity of this tutorial, we’re going to install minimal packages and wrap up with a simple set up

npm install express google-spreadsheet

Step 2: Importing all the necessary packages and files

Import these packages in index.js

const { GoogleSpreadsheet } = require('google-spreadsheet');
const credentials = require('./client_credentials.json');

We’ll talk about client_credentials.json later in this tutorial

Step 3: Logging into Google Developer Console and create a new project

1. Visit the Developer Console and select the proper Gmail account. Click on Select a project in the navigation bar and New Project will create a brand new project for you.

Create a new project

2. Give a name to your project and proceed to create the project

3. After your project is created. Search for Google Drive API in the search bar and navigate to this page. Click on the Enable button, after the API is enabled it’ll redirect you to the API dashboard in your newly created project.

4. Here you’ll have to create credentials to use the API in your NodeJS application. Click on the CREATE CREDENTIALS button

5. On this page, select the same options unless you’ve other specific needs and finally click on the What credentials do I need button below

6. On the next page, you’ll have to configure the Service account permissions. Give your service account a name (preferably the same as your project name to avoid confusion) and then select the Editor role from the dropdown. This will give your service account read/write permissions.

Leave the Key type as JSON. Click on Continue and the credentials file will be automatically downloaded on your machine.

And wallah!! Your service account is created

Note: Don’t share thisyour_project_name.json file with anyone. This file contains all the necessary keys to access your instance of Google API

Rename this file to `client_credentials.json` to match our filename defined in the second step. The credentials file looks similar to the below image

Step 4: Sharing your Google Sheet with the service account

In this step, you’ll be giving the service account access to your Google Sheet. If your sheet is not shared with the service account it’ll not be able to perform any actions

1. Now is the time to use the credentials.json that was automatically downloaded on your machine. Open the file in a text editor and copy the value of the field named client_email. In this case, it’s demo-project@crypto-snow-300303.iam.gserviceaccount.com
2. Click on the Share button in your Google Sheet and paste this email address. Click on the dropdown option and save the changes.

Connecting to Sheets API using google-spreadsheet package

Defining a route

app.get('/google-sheet', async (req, res) => {
  // Pass the google sheet id for accessing/reading data
  const doc = new GoogleSpreadsheet('1ZuVnfliwQAQtnJ14GUDR0mfG4ml3xDo1jNvIjSymdDo');

  // Authentication
  await doc.useServiceAccountAuth(credentials);
  
  // Loading the enitre Google spreadsheet with all the individual Sheets ('Sheet1', 'Sheet2', etc)
  await doc.loadInfo();
  
  // Accessing the entire sheet tab
  let sheet1 = doc.sheetsByTitle['Sheet1'];
  ...
});

You can read about all the available methods in the official documentation for google-spreadsheet

Conclusion

Using google-spreadsheet package is the simplest way to interact with the Google Sheets API. Let me know how was your experience with it.

While deploying the application on Heroku you’ll run into some errors that’ll crash the Google Sheets functionality completely. To know more about how to fix this issue. Read my blog on How to deploy NodeJS application using Google Sheets API (google-spreadsheet) on Heroku