Google Sheets Module

Read, look up, add, update and delete Google Sheets rows from your Processes, by header name, with a one-click Google sign-in.

The Google Sheets Module adds three Tasks. Google Sheets read returns a tab as rows keyed by its header row ({rows.rows.0.email}), finds the rows where a column equals a value, reads a raw range, or exports a tab as a CSV file. Google Sheets write appends rows given by header name, updates or deletes the rows that match, and writes or clears a range. Google Sheets spreadsheet reads a spreadsheet's tabs, creates spreadsheets and adds or deletes tabs. You paste a spreadsheet's link as copied from the browser; the Module reads the id (and the tab, from #gid=) out of it.

What you can build

• Lead capture a Form or chat flow ends with Add rows ({"Name": "{name}", "Email": "{email}", "Source": "website"}) on a shared Leads sheet.
• Lookups in a conversation Find rows by Email = {email}, then answer with {found.first.status} or branch when {found.count} is 0.
• Status updates Update rows where Order = {orderId} sets {"Status": "shipped", "Shipped": "{today}"}; with Add the row when nothing matches it becomes an upsert.
• Reports a Scheduler start reads a tab, File Query aggregates the exported CSV, and the result goes out by email.
• Spreadsheet per customer Create spreadsheet with headers, then Add rows into it using {spreadsheet.spreadsheetId}.

Quick start

1. Open the Environment's Google Sheets settings and click Connect Google. Sign in with the account whose spreadsheets the flows use and approve access to Google Sheets. Back in the portal, confirm the connection.
2. Optionally paste a Default spreadsheet link. Tasks that leave their Spreadsheet empty use it.
3. In the Process Editor, add a Google Sheets read or Google Sheets write Task, pick the Operation, paste the spreadsheet link (or leave it empty) and name the tab.
4. Deploy. The deploy log says which account the Environment is connected as, or that it is not connected yet.

If the Connect Google button is not shown, the platform has no Google sign-in set up: use a service account instead.

How it works

Sign-in

Connect Google runs on the platform's shared connect flow: the sign-in (a Google refresh token) is kept on the engine, encrypted, never in the Environment configuration or the browser, and the hourly access token is renewed automatically. The connected account is what Google sees, so a spreadsheet the flow should use must be one that account owns or that is shared with it (as Editor for writes). Disconnect revokes the sign-in at Google.

A Google account can be connected to several Environments, and different Environments (Development, Production) can use different accounts.

Rows as objects

With First row holds the headers on (the default), the header row names the columns:

Name	Email	Status
Anna	anna@acme.test	new

reads as {rows.rows.0.Name}, {rows.rows.0.Email}, {rows.rows.0.Status}: keys are the header texts, trimmed, exactly as written in the sheet. Each row also carries _row, its row number in the tab. An empty header becomes columnB (its letter); a repeated header becomes Name_2. Header matching when writing is case-insensitive ("email" finds the Email column); a key that matches no header is an error that lists the headers, so a typo never silently drops a value.

With the switch off, rows are lists of cells and columns are named by letter (columnA...). Match columns can always be given as a header name or as a column letter.

Values

Reads return typed values by default: numbers and booleans as such, dates and everything else as the text shown in Sheets. Read values as: Displayed text returns every cell as shown.

Writes store values exactly as given (Google's RAW mode): a text that starts with = stays text and never becomes a formula, and 0012 keeps its zeros. Turn on Interpret values as if typed (USER_ENTERED) when you want formulas, dates and percentages parsed the way Sheets does when typing.

Service account

The alternative to the sign-in. Create a Google Cloud service account, add a JSON key, enable the Google Sheets API for its project, and paste the key under Advanced → Service account JSON key in the Environment's Google Sheets settings. Then share each spreadsheet with the service account's email (name@project.iam.gserviceaccount.com) as Editor. For a whole Google Workspace, enable domain-wide delegation on the service account, authorize its client id with the scope https://www.googleapis.com/auth/spreadsheets in the Workspace Admin console, and fill in Impersonate user. A spreadsheet created by a service account without impersonation belongs to the service account and is not visible in anyone's Drive.

The service account is used only when no Google account is connected to the Environment.

Configuration

Configuration is per Environment.

Field	Required	Description
Google account	One of the two	The Connect Google button and the connected account's status.
Default spreadsheet	No	A spreadsheet link or id used by Tasks whose Spreadsheet is empty.
Service account JSON key (Advanced)	One of the two	Fallback credential; see Service account.
Impersonate user (Advanced)	No	Service account only: the Workspace user to act as.

Platform settings (engine configuration / Key Vault), for administrators: GoogleSheetsModule:OAuth:ClientId, GoogleSheetsModule:OAuth:ClientSecret (Key Vault GoogleSheetsModule--OAuth--ClientId / --ClientSecret) and optionally GoogleSheetsModule:OAuth:RedirectUri. The redirect URI to register on the Google Cloud OAuth client is {Engine public URL}/api/oauth/google-sheets/callback. The same OAuth client as the Gmail Module can be reused by registering both redirect URIs on it. Without a client id the Connect button is hidden.

Limits

• Reads return at most 10,000 rows per step (Read at most, default 1,000); the Task fetches only that many rows from Google.
• Writes send at most 10,000 rows per step; Update rows and Delete rows touch at most 1,000 matching rows.
• CSV exports are capped at 10 MB.
• Google's own quota is 300 read and 300 write requests per minute per project; the Module retries on 429 with backoff.

Files. Export as CSV writes a file: set Store the file in (default {file}) and, for a saved file, Save as (ref-storage:name); see File fields.

Tasks

All Google Sheets Tasks are generic they run identically on every Channel.

GoogleSheetsReadTask (Generic)

Property	Type	Required	Example	Description
Operation	select	Yes	Read rows	Read rows, Find rows, Read range, Export as CSV.
Spreadsheet	string	No	https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0	Link or id. Empty: the Environment's default spreadsheet. Supports {variable}.
Tab	string	No	Customers	The tab name. Empty: the tab of the link's gid, else the first tab.
Range	string	Read range	A1:F200	A1 notation. For the other operations, the table area (empty: the whole tab, header row first).
First row holds the headers	bool	No	true	Rows become objects keyed by the header row.
Match column / Match value	string	Find rows	Email / {email}	Rows whose cell equals the value (case-insensitive, trimmed).
Only the first match	bool	No	false	Stop at the first matching row.
Read values as	select	No	typed	typed (numbers, booleans; dates as shown) or text (as shown).
Read at most (rows)	int	No	1000	1 to 10,000 data rows.
Store the rows in	variable	No	{rows}	Read rows (default {rows}) and Find rows (default {found}): count, headers, rows (objects with _row), first, range, sheet, spreadsheetUrl.
Store the cells in	variable	No	{values}	Read range: rowCount, columnCount, values (lists).
Save as / Store the file in	file	No	ref-session: / {file}	Export as CSV: where the file goes and the variable that receives it.

Failure modes: ModuleMissingConfiguration (not connected, or the connection needs reconnecting), TaskInvalidData (a bad link, an unknown tab or column; the message lists the existing ones), ModuleException (Google refused: the spreadsheet is not shared with the account, a bad range).

GoogleSheetsWriteTask (Generic)

Property	Type	Required	Example	Description
Operation	select	Yes	Add rows	Add rows, Update rows, Delete rows, Write range, Clear range.
Spreadsheet / Tab	string	No		As above.
Rows	JSON or variable	Add rows, Write range	{"Name": "{name}", "Email": "{email}"}	One object, a list of objects, a list of lists (cells from column A), or {variable} holding them. Add rows maps objects to the header row.
Values to set	JSON or variable	Update rows	{"Status": "done"}	The columns to change on every matching row; other columns keep their value.
Match column / Match value	string	Update, Delete	Order / {orderId}	Which rows.
Only the first match	bool	No	false	Change or delete one row only.
Add the row when nothing matches	bool	No	false	Update rows: append Values to set plus the match column as a new row (upsert).
First row holds the headers	bool	No	true	Off: rows are lists, columns are letters.
Range	string	Write range, Clear range	B2	Write range: where the rows start. Clear range: what to empty.
Interpret values as if typed	bool	No	false	USER_ENTERED instead of RAW.
Store the result in	variable	No	{written}	range, rowCount, cellCount, firstRow, lastRow, rowNumbers, spreadsheetUrl.

Update rows writes one range per matching row, from the first to the last changed column, leaving the columns between untouched. Delete rows removes rows bottom-up in one request, so row numbers never shift under it. When nothing matches, Update and Delete log an info row and set rowCount to 0 without failing.

GoogleSheetsSpreadsheetTask (Generic)

Property	Type	Required	Example	Description
Operation	select	Yes	Get info	Get info, Create spreadsheet, Add tab, Delete tab.
Spreadsheet	string	No		Get info, Add tab, Delete tab.
Title	string	Create	Leads {today}	The new spreadsheet's name.
Tab	string	Add, Delete	Archive	Create: the first tab's name (default Sheet1).
Headers	string	No	Name, Email, Phone	Column names written in row 1 (Create, Add tab).
I understand the tab and all its data are deleted	bool	Delete tab		Required to delete a tab; also checked at deploy.
Store the spreadsheet in	variable	No	{spreadsheet}	spreadsheetId, title, url, sheetCount, sheetNames, sheets (each with sheetId, title, url), and sheet (the tab just added).

A spreadsheet created through the sign-in lands in the connected account's My Drive. The last tab of a spreadsheet cannot be deleted.

Deploy checks

At deploy, a spreadsheet link that is not one, a missing Range or Match column, missing Rows, or a Delete tab without its confirm box refuse the deploy and name the step. Once deployed, one log line says which Google account the Environment is connected as (or that a service account is used), or an error that it is not connected yet.