Google Sheets API Integration with Node.js part one(1)

5 min readFeb 19, 2023

The cloud-based spreadsheet program Google Sheets is popular for data management and teamwork. With the help of the robust Google Sheets API, developers may include Google Sheets’ functionality into their programs, simplifying the management of data and automating procedures.

In this tutorial, we’ll go over how to produce and access data from Google Sheets using the Node.js programming language. The googleapis package will be used to communicate with the API and carry out the required tasks.

Make sure you have a Google account and a Google Cloud Platform project created before we start. In order to utilize the Google Sheets API, you must activate it in your project and create credentials.

you can achieve that through the following steps :

Create a blank spreadsheet

Step 1

Step 2

Step 3

The SpreadSheet Id is represented by the Highlighted numerals and letters.

the highlighted numbers and letters is the SpreadSheet Id

To get started, refer to the Google Sheets API documentation.

Enable the sheets API

step 1

https://console.cloud.google.com/apis/api/sheets

select a project and create a new project

step 2

step 3

Give the Project name a title relevant to the project you’re working on, then click the Create button.

step 4

select the “ENABLE APIS AND SERVICES” button.

step 5

search for spreadsheet

step 6

click on the Google sheet API

step 7

Enable the Google Sheets API

step 8

Credentials page

To create a Service Account, click on IAM & Admin and then Service Account.

step 9

To create your credentials, click CREATE SERVICE ACCOUNT.

step 10

Fill in the Service account details with the project details.

step 11

Select a role to grant access to the service account.

step 12

step 13

To create an account, click the Done button.

step 14

step 15

step 16

step 17

step 18

step 19

Rename the downloaded json file to “credentials.json”

step 20

To provide access to the Google Sheet, click Share.

step 21

Rename the spread sheet and save

step 22

Copy the client email and paste it into the ‘add people or groups’ box, then click done to grant the client email access.

To get started, refer to the Google Sheets API documentation.

Once you have set up your project and obtained the necessary credentials, we can begin integrating the Google Sheets API into our Node.js application.

First, install the googleapis package by running the following command in your terminal

npm install googleapis

Next, create a new Node.js file and import the googleapis package:

import { google } from 'googleapis';

To authenticate with the Google Sheets API, we will create an OAuth2 client using our Credentials.json. Replace spreadsheetId, CLIENT_SECRET, and REDIRECT_URI with your own values:


const googleAuth = new google.auth.GoogleAuth({
  keyFile: 'credentials.json',
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

const client = googleAuth.getClient();  
const spreadsheetId = '00000jspq3i4104we012qewados10o4qw000000'

Once we have authenticated, we can create a new instance of the Google Sheets API:

const googleSheets = google.sheets({
  version: "v4",
  auth: client,
});

Now that we have set up authentication and created an instance of the Google Sheets API, we can start interacting with our Google Sheet.

To create a new sheet, we can use the spreadsheets.create method:

async function createSheet() {
  const res = await sheets.spreadsheets.create({
    requestBody: {
      properties: {
        title: 'My New Sheet'
      },
    },
  });

  console.log(res.data);
}

createSheet();

The above code creates a new Google Sheet with the title “My New Sheet”. The response data includes the spreadsheet ID, which we can use to retrieve data from the sheet.

To retrieve data from a sheet, we can use the spreadsheets.values.get method:

async function getSheetData(spreadsheetId, range) {
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range,
  });

  console.log(res.data.values);
}

getSheetData('SPREADSHEET_ID', 'Sheet1!A1:B2');

The above code retrieves data from the range A1:B2 of the sheet with the ID “SPREADSHEET_ID”. The response data includes the values in the specified range.

In conclusion, the Google Sheets API is a powerful tool for managing data in Google Sheets. With the help of the googleapis package, it is easy to integrate the API into a Node.js application and perform various operations on a sheet, including creating and retrieving data.

Google Sheets API Integration with Node.js part one(1)

Written by Chidinma Onyemelukwe