Skip to main content

How to use Google Sheets from NodeJS

  • We are using google-spreadheet for accessing data to google sheets.
  • Utility functions are provided in OCS under Utility/googleSheet.js

Creating a Google Sheet

  • Google Sheet can be created by devops team using no-reply@oriserve.com. Creds for the google sheet is present in OCS in "helpers/constants.js".
  • Make sure to invite the client_email present in creds and provide it with Editor Access.

Connecting to Google Sheets

const { connectToDoc } = require("./Utility/googleSheet");

// Pass in your google sheet id, present in the url
// This will return a doc which will return a doc 
// which can be used to access sheets and cells
const doc = connectToDoc(GOOGLE_SHEET_ID);

Accessing Sheet

// Sheet can be accessed using sheet name
const sheet = doc.sheetsByTitle[sheetName];

// Sheet can be accessed using sheet index
const sheet doc.sheetsByIndex[index];

Loading Cells

const { loadCellsByIndices } = require("./Utility/googleSheet");

// This will load all the cells formed by the rectangle of (2,2) & (4,4)
// Make sure rowIndex2 >= rowIndex1 & columnIndex1 >= columnIndex2
await loadCellsByIndices(sheet, { 
    rowIndex1: 2, 
    columnIndex1: 2, 
    rowIndex2: 4, 
    columnIndex2: 4: 
  }
);

// Another way is to use string, for example "B2:D4", This well also
// load the same cells as the above example
await sheet.loadCells("B2:D4");
Note

All cells are not loaded into RAM on connection, only load those cells which you need

Note

Trying to access a cell which isn't already loaded will throw an error.

Accessing Cells

// Access cells using a zero-based index
const a1 = sheet.getCell(0, 0); 
// or A1 style notation
const c6 = sheet.getCellByA1('C6');

// Read value of cell
console.log(a1.value);

// Change value of cell
a1.value = "Something Else";

Saving The Changes

// This saves all the changes made to any cell
await sheet.saveUpdatedCells();