reshuffle-smartsheet-connector

Code | npm | Code sample

npm install reshuffle-smartsheet-connector

Reshuffle Smartsheet Connector

This package contains a Reshuffle connector to access to online spreadsheets at smartsheet.com.

The following example tracks changes to an online spreadtsheet. Changes are reported at the sheet level, row level and cell level:

const { Reshuffle } = require('reshuffle')
const { SmartsheetConnector } = require('reshuffle-smartsheet-connector')

const app = new Reshuffle()

const ssh = new SmartsheetConnector(app, {
  apiKey: process.env.SMARTSHEET_API_KEY,
  baseURL: process.env.RESHUFFLE_RUNTIME_BASE_URL,
})

ssh.on({ sheetId: sheet.sheetId }, async (event) => {
  console.log('Smartsheet event:', event)
})

async function main() {

  const sheet = await ssh.findOrCreateSheet('Reshuffle Events Example', [
    { title: 'Name', type: 'TEXT_NUMBER', primary: true },
    { title: 'Quest', type: 'TEXT_NUMBER' },
    { title: 'Color', type: 'TEXT_NUMBER' },
  ])

  if (sheet.created) {
    await ssh.addRowToBottom(sheet.sheetId, [
      { columnId: sheet.columns[0].id, value: 'Arthur' },
      { columnId: sheet.columns[1].id, value: 'Find the Holy Grail' },
      { columnId: sheet.columns[2].id, value: 'Blue' },
    ])
  }

  console.log(`Please visit ${sheet.permalink} and make some changes`)
}

app.start()
main().catch(console.error)

Table of Contents

Configuration Configuration options

Connector events

sheetChanged Sheet changed

Connector actions

addRows Add rows to a sheet

addRowToBottom Add one row at the bottom of a sheet

addRowToTop Add one row at the top of a sheet

createSheet Create a new sheet

deleteRow Delete one row

findOrCreateSheet Find or create a sheet

getImage Get image from a sheet cell

getSheetById Get sheet data by sheet id

getSheetIdByName Find sheet id by its name

getSheetByName Get sheet data by sheet name

getSimpleSheetById Get a simple sheet object by ID

getSimpleSheetByName Get a simple sheet object by name

getRow Get row information

listSheets List all sheets

listRows List rows in a sheet

update Update a sheet

sdk Get direct SDK access

Configuration options

const app = new Reshuffle()
const smartsheetConnector = new SmaetsheetConnector(app, {
  apiKey: process.env.SMARTSHEET_API_KEY,
  baseURL: process.env.RESHUFFLE_RUNTIME_BASE_URL, // optional but required
                                                   // for events
})

Connector events

Sheet Changed event

Example:

async (event) => {
  console.log('Smartsheet event:', event)
})

This event is fired when a Smartsheet webhook is triggered. Triggers include sheet update, row update, cell update and more.

See event.js for an example of defining and handling sheet events.

Connector actions

Add Rows action

Add rows to a sheet.

Definition:

(
  sheetId: number | string,
  rows: object,
) => void

Usage:

await smartsheetConnector.addRows(4583173393803140, [
  {
    toTop: true,
    cells: [
      {
        columnId: 7960873114331012,
        value: true,
      },
      {
        columnId: 642523719853956,
        value: 'New status',
        strict: false,
      },
    ],
  },
  {
    toBottom: true,
    cells: [
      {
        columnId: 7960873114331012,
        value: true,
      },
      {
        columnId: 642523719853956,
        value: 'New status',
        strict: false,
      },
    ],
  },
])

Add Row To Bottom action

Add one row after the last row of a sheet.

Definition:

(
  sheetId: number | string,
  cells: object[],
) => void

Usage:

await smartsheetConnector.addRowToBottom(4583173393803140, {
  { columnId: 7960873114331012, value: true },
  { columnId: 642523719853956, value: 'New status' }
})

Add Row To Top action

Add one row before the first row of a sheet.

Definition:

(
  sheetId: number | string,
  cells: object[],
) => void

Usage:

await smartsheetConnector.addRowToTop(4583173393803140, {
  { columnId: 7960873114331012, value: true },
  { columnId: 642523719853956, value: 'New status' }
})

Create Sheet action

Create a new sheet.

Definition:

(
  name: string,
  columns: object[]
) => object

Usage:

await smartsheetConnector.createSheet('My Sheet', [
  { title: 'Name', type: 'TEXT_NUMBER', primary: true },
  { title: 'City', type: 'TEXT_NUMBER' },
])

Delete Row action

Delete a single row from the specified sheet.

Definition:

(
  sheetId: number | string,
  rowId: number | string,
) => void

Usage:

await smartsheetConnector.deleteRow(4583173393803140, 1234567890123456)

Find Or Create Sheet action

This action offers the same interface as createSheet above, but checks first whether a sheet with the specified name exists. If so, that sheet is returned. Otherwise, a new one is created.

The action returns an object with the following fields:

  accessLevel: string
  columns: object[]
  created: boolean
  name: string
  permalink: string
  sheetId: number

Definition:

(
  sheetId: number | string,
  rowId: number | string,
) => object

Usage:

await smartsheetConnector.findOrCreateSheet('My Sheet', [
  { title: 'Name', type: 'TEXT_NUMBER', primary: true },
  { title: 'City', type: 'TEXT_NUMBER' },
])

Get Image action

Get an image stored in a sheet cell. sheetId and rowId specify the specific row to query. columnIdOrIndex is treated as an index if it is a number smaller than 1024, otherwise it is treated as a column id.

The returned image data includes a unique ID, the alternative text (usually the original file name) and a download URL. The URL is valid for half an hour.

Use the optional width and height arguments to get a link to a resized image.

Definition:

(
  sheetId: number | string,
  rowId: number | string,
  columnIdOrIndex: number | string,
  width?: number,
  height?: number,
) => object

Usage:

const img = await smartsheetConnector.getImage(
  4583173393803140,
  000000000000000,
  3,
)
console.log(img.id, img.text, img.url)

Get Sheet By ID action

Get full sheet data for the sheet with the specified id.

Definition:

(
  sheetId: number | string,
) => object

Usage:

const sheetData = await smartsheetConnector.getSheetById(4583173393803140)

Get Sheet ID By Name action

Lookup the sheet ID for the sheet with the specified name. If a sheet with that name is not found then an Error is thrown.

Definition:

(
  name: string,
) => number

Usage:

const sheetId = await smartsheetConnector.getSheetIdByName('My Sheet')

Get Sheet By Name action

Get full sheet data for the sheet with the specified name. If a sheet with that name is not found then an Error is thrown.

Definition:

(
  name: string,
) => object

Usage:

const sheetData = await smartsheetConnector.getSheetByName('My Sheet')

Get Simple Sheet By ID action

Get a SimpleSheet object representing the sheet with the specified id. This object provides the following methods:

getColumnIdByTitle(
  columnTitle: string,
): number // Get column ID by column title
getUpdater(): object // Create an updater object
pivot(
  pivotColumn: string,
  property: string,
  matchColumns: string[],
  includeRowIDs?: boolean,
): object // Build a pivot table
toSCV(): string // Create a CSV representation

An updater object provides the following methods:

addUpdate(
  columnTitle: string,
  rowId: number | string,
  value: string,
) // Add a cell value to be updated
getSheetId(): number // Get the sheet ID
getUpdates(): object // Get the updates for using with the update action

Definition:

(
  sheetId: number | string,
) => object

Usage:

const sheet = await smartsheetConnector.getSimpleSheetById(4583173393803140)
const updater = sheet.getUpdater()
updater.addUpdate('My Column', 000000000000000, 'New Value')
await smartsheetConnector.update(updater.getSheetId(), updater.getUpdates())

Get Simple Sheet By Name action

Get a SimpleSheet object representing the sheet with the specified name. See getSimpleSheetById for details.

Definition:

(
  name: string,
) => object

Get Row action

Get information about the specified row in the specified sheet. Row information is detailed here.

Definition:

(
  sheetId: number | string,
  rowId: number | string,
) => object

Usage:

const row = await smartsheetConnector.getRow(
  4583173393803140,
  1234567890123456,
)

List Rows action

Get a list of row Ids in the specified sheet.

Definition:

(
  sheetId: number | string,
) => number[]

Usage:

const rowIds = await smartsheetConnector.listRows(4583173393803140)

List Sheets action

Get a list of all sheets in the connected Smartsheet account. For each sheet, the following information is returned:

• id - Sheet ID
• name - Sheet name
• accessLevel - Usually 'OWNER'
• permalink - Link to the sheet's online page
• createdAt - Creation time stamp
• modifiedAt - Modification time stamp

Definition:

() => object[]

Usage:

const sheets = await smartsheetConnector.listSheets()

Update action

Update the data in a sheet. The update object uses the format defined here. You can use the Simple Sheet object to create an updater object that will construct the rows array.

Definition:

(
  sheetId: number | string,
  rows: object[],
) => void

Usage:

await smartsheetConnector.update(
  4583173393803140,
  [
    {
      id: '0000000000000000',
      cells: [
        {
          columnId: '0000000000000000',
          value: 'My Value',
        },
      ],
    },
  ],
)

sdk action

Get the underlying SDK object.

Definition:

() => object

Usage:

const client = await smartsheetConnector.sdk()