Google Sheets Connector

Reshuffle Google Sheets Connector

npm install reshuffle-google-connectors

This is a Reshuffle connector that allows you to Interact with online Google Sheets.

The connector is designed to work with Google Sheets in the form of a table, where the header row (first row) is defined.

The following example listens to changes in online spreadsheet:

const { GoogleSheetsConnector } = require('reshuffle-google-connectors')

const myGoogleSheetsConnector = new GoogleSheetsConnector(app, {
  credentials: {
    client_email: '<your_client_email>',
    private_key: '<your_private_key>',
  },
  documentId: '<your_documentId>',
})

const myHandler = (event, app) => {
  // event is { oldRows, newRows, worksheetsRemoved: WorkSheetChanges[], worksheetsAdded: WorkSheetChanges[], worksheetsChanged: WorkSheetChanges[] }
  // WorkSheetChanges is { worksheetId, rowsRemoved, rowsAdded, rowsChanged }
  console.log('New rows detected!')
  event.options.sheetIdOrTitle &&
    console.log(
      `'sheetIdOrTitle' is set in event options so it only checks for changes in sheet ${event.options.sheetIdOrTitle}`,
    )

  event.newRows.forEach(({ worksheetId, rows }) => {
    console.log(`workSheetId: ${worksheetId}`)

    rows.forEach((row, index) => {
      let rowString = `line ${index + 1}\t`
      Object.values(row).forEach((val) => (rowString += `${val}\t`))
      console.log(rowString)
    })
  })

  event.worksheetsChanged[0] &&
    event.worksheetsChanged[0].rowsAdded[0] &&
    console.log(
      `Example of new line values ${JSON.stringify(
        event.worksheetsChanged[0].rowsAdded[0],
      )}`,
    )
}

/** Trigger a handler when changes are detected in document <sheetId> (it will check for changes every 10 seconds) */
myGoogleSheetsConnector.on({}, myHandler)

app.start()

Configuration Options:

interface GoogleSheetsConnectorConfigOptions {
  credentials: ServiceAccountCredentials
  sheetId: string
}

Connector events

rows changed

This event is fired when rows have changed in the online Sheets.

You can trigger a handler on rows changed by providing the following options to the on method:

interface GoogleSheetsConnectorEventOptions {
  interval?: number
  sheetIdOrTitle?: string
  keyColumn?: string
}

Event is an object containing attribute:

oldRows: cellValue[]
newRows: cellValue[]
worksheetsRemoved: WorkSheetChanges[]
worksheetsAdded: WorkSheetChanges[]
worksheetsChanged: WorkSheetChanges[]

export interface cellValue {
  headerName: string
  value: string
}

export interface WorkSheetChanges {
  worksheetId: string
  rowsRemoved: any[]
  rowsAdded: any[]
  rowsChanged: RowChange[]
}

export interface RowChange {
  prev: any
  curr: any
}

Example:

const myHandler = (event, app) => {
  console.log('New rows detected!')
  event.options.sheetIdOrTitle && console.log(`'sheetIdOrTitle' is set in event options so it only checks for changes in sheet ${event.options.sheetIdOrTitle}`)

  event.newRows.forEach(({worksheetId, rows}) => {
    console.log(`workSheetId: ${worksheetId}`)

    rows.forEach((row, index) => {
      let rowString = `line ${index + 1}\t`
      Object.values(row).forEach(val => rowString += `${val}\t`)
      console.log(rowString)
    })
  })

  event.worksheetsChanged[0]
    && event.worksheetsChanged[0].rowsAdded[0]
    && console.log(`Example of new line values ${JSON.stringify(event.worksheetsChanged[0].rowsAdded[0])}`)
}

/** Trigger a handler when changes are detected in document <sheetId> (it will check for changes every 10 seconds) */
myGoogleSheetsConnector.on({}, myHandler)

/** Check for changes every minute (it overrides the default timer set to 10 sec) */
const aMinuteMs = 60 * 1000
myGoogleSheetsConnector.on({interval: aMinuteMs}, myHandler)

/** Check for changes in a specific sheet by id */
myGoogleSheetsConnector.on({sheetIdOrTitle: 0}, myHandler)

/** Check for changes only in a specific sheet by title and every 30 seconds */
myGoogleSheetsConnector.on({sheetIdOrTitle: 'Sheet1', interval: 30 * 1000}, myHandler)

/** Check for changes using a key column for more accuracy, see Known Issues below */
myGoogleSheetsConnector.on({keyColumn: 'Identifier'}, myHandler)

Known Issues:

Since Google Sheets does not provide row identifiers, it is hard to identify which events caused the changes, for example when deleting a row which is not the last one in the spreadsheet, the rows below are shifted up. This can be interpreted as an update of all the rows below including the "deleted" row and a deletion of the last row. These are the options to handle such cases: 1. Add or delete rows just from the bottom of the spreadsheet. 2. Treat event.oldRows and event.newRows as before and after snapshots. You may compare these yourself to ascertain which changes your code is interested in. 3. Set a Key column in your spreadsheet with a unique identifier and provide its name as the GoogleSheetsConnectorEventOptions.keyColumn. When this option is used, the connector can differentiate added and deleted rows based on these unique identifiers. This removes the ambiguity described above. Note that these identifiers must be unique. If duplicates exist in your spreadsheet, the connector will not behave as expected.

When using the keyColumn and having multiple sheets, the same column name should be used in all the sheets. In every sheet the column can have different types of identifiers but the column name should be the same, for example keyColumn is set to be 'RS-Identifier', in Sheet1 it can be email address while in Sheet2 it can be a serial number.

Connector actions

This connector provides all the common actions to interact with a Google Sheets document. It also provides the sdk (which return a Google Sheets instance) for more advanced operations.

getRows

Get all rows of a sheet id|title. This action returns an array of rows. Each row is an object, with properties matching the column names set by the header row.

Example

const sheetTitle = 'Sheet1'
const rows = await myGoogleSheetsConnector.getRows(sheetTitle)
console.log(`Sheet ${sheetTitle} contains ${rows.length} row(s)`)
rows.forEach(row => console.log(row._rawData))

getCell

Get data of a cell at rowIndex/columnIndex of a sheet id|title

Example

const rowIndex = 2
const columnIndex = 1
const cell = await myGoogleSheetsConnector.getCell(sheetId, rowIndex, columnIndex)
console.log(`Cell details [value: ${cell.value}, a1Address: ${cell.a1Address},` +
   ` rowIndex: ${cell.rowIndex}, columnIndex: ${cell.columnIndex}, a1Row: ${cell.a1Row}, a1Column:${cell.a1Column}]`)

getCellByA1

Get data of a cell by a1 address of a sheet id|title

Example

const a1Address = 'B2'
const cell = await myGoogleSheetsConnector.getCellByA1(sheetTitle, a1Address)
console.log(`Cell has properties ${JSON.stringify(cell)}`)

getCells

Get cells data for range in sheet by id|title

Example

const range = {startRowIndex: 1, endRowIndex: 5, startColumnIndex: 0, endColumnIndex: 2}
const rows = await myGoogleSheetsConnector.getCells(sheetTitle, range)
console.log(`Sheets contains ${rows.length} row(s)`)
rows.forEach(row => console.log(row))

addRow

Adds a new row to a sheet identified by its id|title

Example

const sheetId = 0
const values = ['value 1', 'value 2', 'value 3']
await myGoogleSheetsConnector.addRow(sheetId, values)

getInfo

Get document info (returns an object with sheetCount and sheetsByIndex)

Example

const docInfo = await myGoogleSheetsConnector.getInfo()
console.log(`Document has ${docInfo.sheetCount} sheets.`)
docInfo.sheetsByIndex.forEach(
  sheetDetails => console.log(`[id: ${sheetDetails.sheetId}, title: ${sheetDetails.title}]`)
)

sdk

returns a Google Sheets instance for sheetId

See documentation in github: https://github.com/theoephraim/node-google-spreadsheet

Examples

/** Add new sheet using the sdk */
const newSheetTitle = 'new sheet'
const newSheetProperties = {title: newSheetTitle, headerValues: ['header 1', 'header 2', 'header 3']}
const doc = await myGoogleSheetsConnector.sdk()
await doc.addSheet(newSheetProperties)

/** Delete sheet by id|title using the sdk */
const sheetTitleToDelete = 'new sheet'
const doc = await myGoogleSheetsConnector.sdk()
const sheet = doc.sheetsByTitle[sheetTitleToDelete]
// const sheet = doc.sheetsByIndex[2] // Use this line for deleting by sheet id
await sheet.delete()

More examples on how to use this connector can be found here.