Google Sheets

Description

Complete module for operating with Google Spreadsheets: read, write, create, search and replace data. Includes multiple sub-nodes for different operations.

Requirements

• Google OAuth credential configured with Google Sheets permissions (spreadsheets)

Configuration

Available Sub-nodes

1. Read Sheet

Reads data from a spreadsheet as an array of arrays (raw format).

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
range	text	No	Range in A1 notation. Default: Sheet1!A:Z
outputKey	text	No	Output field. Default: sheetData

Output:

{
  "sheetData": [
    ["Nombre", "Email", "Telefono"],
    ["Juan", "juan@mail.com", "555-0001"],
    ["Maria", "maria@mail.com", "555-0002"]
  ]
}

2. Read as Objects

Reads data as an array of objects using the first row as headers/keys.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
range	text	No	Range in A1 notation. Default: Sheet1!A:Z
outputKey	text	No	Output field. Default: rows

Output:

{
  "rows": [
    { "Nombre": "Juan", "Email": "juan@mail.com", "Telefono": "555-0001" },
    { "Nombre": "Maria", "Email": "maria@mail.com", "Telefono": "555-0002" }
  ]
}

3. Write Sheet

Writes data to a sheet with overwrite or append modes.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
sheetName	google_sheet	Yes	Destination sheet
writeMode	select	Yes	append (add to end) or overwrite (overwrite)
startCell	text	No	Start cell for overwrite mode (e.g.: A1). Only if overwrite
valuesKey	text	Yes	Field with data to write. Default: values
headers	text	No	Columns separated by comma (for objects)
includeHeaders	boolean	No	Include header row. Default: true

Supported data formats:

• Array of arrays: [["Juan", "juan@mail.com"], ["Maria", "maria@mail.com"]]
• Array of objects: [{"nombre": "Juan", "email": "juan@mail.com"}]
• Simple object: {"nombre": "Juan", "email": "juan@mail.com"} (written as one row)

4. Append Rows

Shortcut to add rows to the end of existing data.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
range	text	No	Destination sheet. Default: Sheet1
valuesKey	text	No	Field with the rows. Default: values

5. Clear Range

Clears (deletes) data from a specific range.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
range	text	Yes	Range to clear. Default: Sheet1!A2:Z

Tip: Use Sheet1!A2:Z to clear data without deleting row 1 headers.

6. Get Sheet Info

Gets spreadsheet information: sheets, dimensions, title.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet

7. Create Spreadsheet

Creates a new spreadsheet in Google Drive.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
title	text	Yes	Spreadsheet title. Default: Nueva Hoja de Calculo
sheetTitles	text	No	Sheet names separated by comma. Default: Sheet1

Output:

{
  "createdSpreadsheet": {
    "spreadsheetId": "1aBcDeF...",
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1aBcDeF.../edit",
    "title": "Reporte Q1 2024",
    "sheets": ["Datos", "Resumen"]
  }
}

8. Add Sheet

Adds a new sheet (tab) to an existing spreadsheet.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
sheetTitle	text	Yes	New sheet name

9. Find & Replace

Searches and replaces text across the entire spreadsheet.

Field	Type	Required	Description
credentials_id	credentials	Yes	Google OAuth account
spreadsheetId	google_spreadsheet	Yes	Spreadsheet
find	text	Yes	Text to search for
replace	text	No	Replacement text (empty = delete)
matchCase	boolean	No	Case sensitive. Default: false
matchEntireCell	boolean	No	Match entire cell. Default: false

Usage Example

Sync form data to Sheets

[Form Trigger] -> [Read as Objects] -> [Condition] -> [Append Rows]
  | new record      | read existing     | no duplicate    | add row

Generate report and send it

[Cron Trigger] -> [Query MySQL] -> [Create Spreadsheet] -> [Write Sheet] -> [Send Email]
  | every Monday    | sales data      | new file           | write data     | attach link

Clear and update data

[HTTP Trigger] -> [Clear Range] -> [Write Sheet]
  | new data       | clear A2:Z      | write updated data

Common Errors

Error	Cause	Solution
Spreadsheet not found	Incorrect ID or no access	Verify the ID and permissions
Unable to parse range	Invalid range format	Use A1 notation: Sheet1!A1:D10
Sheet not found	The sheet doesn’t exist in the spreadsheet	Verify the exact sheet name
No values to write	The data field is empty	Verify the previous node generates data in the specified field

Related Nodes

• Google Calendar - Manage calendar events
• Google Drive - Manage files in Drive