@avoylenko/node-red-contrib-google-sheets 2.0.0

Node-RED node to get, update, append, and clear google sheets.

npm install @avoylenko/node-red-contrib-google-sheets

node-red-contrib-google-sheets

A Node-RED node to get, update, append, and clear cell values in a Google Sheet using a Google service account.

Install

Install from the Node-RED palette manager (Menu → Manage palette → Install), or from the command line inside your Node-RED user directory (typically ~/.node-red):

npm install @avoylenko/node-red-contrib-google-sheets

Requirements

• Node.js >= 18
• Node-RED >= 1.0

Auth

To set up auth this node uses a Google service account:

1. Create a new service account from this page.
2. Download a JSON credentials key for the service account.
3. Enable the Google Sheets API for the project.
4. Paste the entire contents of the JSON key file into the Credentials field of the node's config.
5. Share your sheet with the email address of the service account, e.g. [email protected]. Viewer access is enough for Get Cells; Append Row, Update Cells, and Clear Cells need Editor access.

The node only requests the https://www.googleapis.com/auth/spreadsheets OAuth scope.

Sheets

The sheet ID can be found in the URL of your Google Sheet, for example in

https://docs.google.com/spreadsheets/d/1UuVIH2O38XK0TfPMGHk0HG_ixGLtLk6WoBKh4YSrDm4/edit#gid=0

the ID would be 1UuVIH2O38XK0TfPMGHk0HG_ixGLtLk6WoBKh4YSrDm4.

Cells

Google Sheets uses A1 notation to reference a tab and a range of cells of the worksheet.

The format is Sheet1!A1:C3, where Sheet1 is the sheet (tab) name, followed by a !, then the grid of the first cell e.g. A1, then a :, and finally the grid of the last cell e.g. C3.

A range of cells can be part of a column (A1:A5), part of a row (A1:E1), or a block (A1:C3). A sheet name on its own (Sheet1) refers to all of its cells.

Methods

Get Cells

Reads the given range and sends the values as msg.payload — an array of rows, where each row is an array of cell values. An empty range produces []. Ticking the Flatten Matrix option flattens the result by one level, which is convenient when reading a single row or column.

Append Row

Appends msg.payload as new row(s) after the last row with data in the given range. On output, msg.payload contains a summary of the change (updatedRange, updatedRows, updatedCells, ...).

Update Cells

Writes msg.payload to the given range, overwriting existing values. On output, msg.payload contains a summary of the change (updatedRange, updatedRows, updatedCells, ...).

Clear Cells

Clears all values from the given range; msg.payload is not used as input. On output, msg.payload contains the spreadsheetId and the clearedRange.

Input payload for Append Row and Update Cells

msg.payload can be:

• a single value — written to a single cell
• an array of values — written as one row
• an array of arrays — written as multiple rows (one inner array per row)

Values are written with the USER_ENTERED input option: they are parsed as if typed into the sheet by a user, so numbers, dates, and formulas such as =SUM(A1:A5) are interpreted.

Message properties

Values configured in the node take precedence over message properties.

Example flow

An example flow demonstrating all four methods ships with the package: Menu → Import → Examples → @avoylenko/node-red-contrib-google-sheets.

Changes in 2.0

• Published under a new name: @avoylenko/node-red-contrib-google-sheets. The original node-red-contrib-google-sheets package remains at 1.1.2. Uninstall it before installing this one — both register the same GSheet/gauth node types, and Node-RED cannot load two packages that provide the same types.
• Requires Node.js >= 18.
• Migrated from the monolithic googleapis package to the much smaller @googleapis/sheets client.
• The Google Drive OAuth scope is no longer requested; only the spreadsheets scope is used.
• Get Cells now returns [] instead of failing when the range contains no values.
• No changes to node configuration or message properties — existing flows keep working.

Credits & license

Originally created by Sam Machin, now maintained by Anton Voylenko. Licensed under the MIT license.