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
}
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)
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.