Google Sheet API with Nodejs

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

No alt text provided for this image

2. Create the service account

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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

No alt text provided for this image

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.

No alt text provided for this image

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

No alt text provided for this image

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.

No alt text provided for this image
No alt text provided for this image

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.

No alt text provided for this image
No alt text provided for this image

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!

要查看或添加评论,请登录

Wajiha Abid的更多文章

  • phonenumbers library in python

    phonenumbers library in python

    Get different requirements daily, so the recent requirement I got was to extract the carriers of phone numbers…

    1 条评论
  • Express Servers communicating through RabbitMQ

    Express Servers communicating through RabbitMQ

    WHAT IS RABBITMQ? RabbitMQ is a message broker . It gives your applications a common platform to send and receive…

社区洞察

其他会员也浏览了