Google Sheets CustomInstall

Summary

Read data from a specified range in a Google Sheets spreadsheet using either refresh token or bearer token authentication.

Inputs

• Authentication (required) — Choose between 'Refresh Token (Auto-Refresh)' to automatically refresh the access token or 'Bearer Token (Manual)' to provide a token manually.
• Custom Refresh Token — Optionally override the refresh token from credentials when using refresh token authentication.
• Spreadsheet ID (required) — The ID of the Google Sheets spreadsheet to read from.
• Range (required) — The A1 notation range of cells to read, e.g., 'Sheet1!A:Z' or 'Sheet1!A1:D10'.
• Options — Optional settings for value representation and date/time formatting, including Value Render Option (Formatted Value, Unformatted Value, Formula) and Date Time Render Option (Serial Number, Formatted String).

Output shape

a list of JSON objects, each representing a row from the specified sheet range with columns mapped as 'col1', 'col2', etc.

If the specified range contains values, each row is converted into a JSON object with keys as column identifiers (col1, col2, and so forth). If no values are found, the raw API response is returned as a single JSON object. Errors during execution may cause the node to fail or continue based on 'Continue On Fail' setting.

Examples

Example 1: Read the range 'Sheet1!A1:C3' from a spreadsheet using refresh token authentication to retrieve formatted cell values as JSON objects per row.

Authentication: Refresh Token (Auto-Refresh); Spreadsheet ID: 'your-spreadsheet-id'; Range: 'Sheet1!A1:C3'; Options: Value Render Option = Formatted Value, Date Time Render Option = Formatted String.