Google Sheet API with Nodejs
Wajiha Abid
Software Engineer | Nodejs | Typescript | GraphQL | Socket.io | Laravel | React/Vue | Freelancer
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:
STEP 1: Setup the Service Account
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
领英推荐
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:
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:
?
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!