How to use Google Sheets API with NodeJS?

14 August 2021

Overview

When we want to access spreadsheet data or want to modify spreadsheet data programmatically then Google sheet API service is required for the same. It provides different inbuilt methods to access or modify spreadsheet content.

Follow below steps to use Google sheet API service:

• To use Google sheet API functionality we need to first enable Google sheet API service from the Google developer console for this we first need to create a project on Google developer console.
• After creating a project on the Google developer console, you will find the option to enable Google sheet API.

We are now able to use the google sheet API services.

• After that, Login into your Gmail account and create one spreadsheet document. In the spreadsheet URL you will see the spreadsheet ID which will be required to access the spreadsheet.
  
• We will need to create a service account to configure the Google sheet API service. Go to your Google developer console project and open the google sheet API service. You will find the credentials tab in the sidebar option. Click on the credentials tab and fill in all information to create a service account. Here you will get a service account ID which will help to access the spreadsheet with Google sheet API service. Service account ID we can use as email ID just like we share spreadsheet access with others email IDs.
  
• In the next step, we need to generate a credentials JSON file that will be required for the API authentication. You will get an option to create a key while creating a service account ID. Download credentials file in directory as “credentials.json” file.
• Share the spreadsheet access with a service account ID.
  
• Now we need to write NodeJS code for spreadsheet access.
  First, install googleapis NPM package.

  > npm i googleapis

• Save downloaded “credentials.json”. file in the NodeJS project directory.
• Keep spreadsheet ID, range, scope details in the project environment file.
  
• Write a function to generate a token for authentication. Here, we are creating a new token and storing it into the “token.json” file in the NodeJS project directory.
  
• After that, write a function for authentication. Here, we set a token generated in the previous step.
  
• After successful authenticate call function to fetch spreadsheet data. Using googleapis NPM package, set spreadsheet ID and range details (fetch from environment file). We will receive spreadsheet data in JSON response. Also first import the ‘googleapis’ package on top of the page.

  const {google} = require('googleapis');