Google Sheet API with Nodejs

Google sheet is a platform where you can maintain your spreadsheet and can share with other people. Changes are tracked realtime and anybody can edit who have permission to do so. As it is free, requirements for google sheet integration is increasing day by day. Integrating it with nodejs is a 3 step game.

Pre-requisites:

• You should have a basic knowledge of nodejs.

STEP 1: Setup the Service Account

1. Enable the google API service from google cloud platform

2. Create the service account

4. After creating the service account, you'll be redirected to home screen where all your service account are listed.

On clicking at your service account, it will take to the following screen where you have to go to keys tab and click on add key. Choose the json option and on hitting at create button, it will download a json file with credentials.

5. Share the sheet with the service account and make it editor.

STEP 2: Initialize the Google JWT Auth

1. Copy the credential json file to the project directory.
2. Initialize the google JWT authentication and google sheet.

const google = require("googleapis").google
const {client_email,private_key} = require("../your-json.json")


//authenticate
const auth = new google.auth.JWT({
? ? email: client_email,
? ? key: private_key,
? ? scopes: ["https://www.googleapis.com/auth/spreadsheets"],
})


const sheet = google.sheets("v4");

        

STEP 3: Define the Add/Update Controllers

Import the sheet and the auth. Define the controller to append the row to the sheet. Append function of google sheet takes following parameters:

• auth
• spreadSheetId
• range (if you dont know the exact cell after which you want to append, just pass the name of sheet it will append in the last).
• valueInputOption
• includeValuesInResponse (it is false by default, to get the cell range do make it true)
• requestBody (object hold the array of values)

let response = await sheet.spreadsheets.values.append({
? ? ? ? ? ? spreadsheetId: process.env.SHEETID,
? ? ? ? ? ? auth: auth,
? ? ? ? ? ? range: "Sheet1",
? ? ? ? ? ? valueInputOption: "RAW",
? ? ? ? ? ? includeValuesInResponse: true,
? ? ? ? ? ? requestBody: {
? ? ? ? ? ? ? ? values: [Object.values(req.body)] //it accepts the array of array
? ? ? ? ? ? }
? ? ? ? })
        

This how the values will append.

Updating a particular row could be challenge if we dont track the appended row range. batchUpdateByDataFilter takes a filter property in which on passing a specific range will update the row with respect to the range. Other parameters in this method are same but the requestBody is different. It holds the followings properties:

• valuesInputOption
• data (its the array of filters)

?
let response = await sheet.spreadsheets.values.batchUpdateByDataFilter({
? ? ? ? ? ? spreadsheetId: process.env.SHEETID,
? ? ? ? ? ? auth: auth,
? ? ? ? ? ? requestBody: {
? ? ? ? ? ? ? ? valueInputOption: "RAW",
? ? ? ? ? ? ? ? data: [


? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? majorDimension: 'ROWS',
? ? ? ? ? ? ? ? ? ? ? ? dataFilter: {
? ? ? ? ? ? ? ? ? ? ? ? ? ? a1Range: req.body.range
? ? ? ? ? ? ? ? ? ? ? ? },
? ? ? ? ? ? ? ? ? ? ? ? values: [Object.values(req.body.change)]
? ? ? ? ? ? ? ? ? ? },
? ? ? ? ? ? ? ? ],


? ? ? ? ? ? }
? ? ? ? })
        

And we update the range A6:B6 which we have added recently, this how it is updated.

Conclusion:

Above only two methods are elaborated, google sheet do offer more methods too, like get all row values or updating the rows in bulk. For exploring all methods check out the google sheet documentation and for whole code check out this repo. The article above is the simplest representation for the google sheet, share your experience and solution if you have gone through more complex scenario.

Thanks for reading!