Google Sheets Activities

Version: 1.0.5  ·  ID: zoan-packages-googlesheets  ·  Author: Zoan Software

The Google Sheets package reads and writes Google spreadsheets directly in the cloud (through its API). It is the Excel counterpart for Google documents: ideal when the data lives in a shared sheet that several people or systems update, with no files to download and upload again.

Concept 1: authentication

Unlike Excel (which opens a local file), Google Sheets reaches a sheet in the cloud, so it needs authorization. Google Sheets — Open supports two modes, chosen with authType:

Mode	What you need	When it fits
oauth (default)	A Google OAuth connection created and authorized in Zoan Cloud with the Sheets permission. Pass it in credential as credential("code-name"). Zoan Cloud refreshes the token for you.	The robot acts on a person’s account or a team mailbox.
service-account	A service account key (the full JSON) stored as a credential, in serviceAccount. The sheet must be shared with the service account’s email. Optional: impersonateUser to act on behalf of a Workspace user (domain-wide delegation).	Unattended runs with no person’s consent.

In both modes the activities use the read and write spreadsheets permission (https://www.googleapis.com/auth/spreadsheets).

What you need	Where to get it
Spreadsheet ID (spreadsheetId)	It is in the sheet URL: docs.google.com/spreadsheets/d/{ID}/edit — copy the {ID} part.

Concept 2: the session

Every operation works on a session (ZoanGoogleSheetsSession) that you open with Google Sheets — Open and share with the other activities:

• With body: the activities inside inherit the session without setting session; when the block ends the session closes by itself, even if something fails.
• Without body: save the session in a variable, pass it in session and close it with Google Sheets — Close.

Google Sheets — Open   credential = credential("google-operations")   spreadsheetId = sheetId
 └─ body:
      Google Sheets — Read sheet   sheet = "Orders"   has-headers = true   → output: orders
      ... process ...

Opening does not check access

Open only prepares the session: it does not call Google. If the spreadsheet ID is wrong or the account has no access, the error shows up on the first read or write.

A1 notation, sheets and values

• Cells and ranges use A1 notation: A1, B3, A1:E10, A:E (whole columns), 1:100 (whole rows).
• Sheets are referenced by name ("Orders"). If you leave it empty, the first sheet of the document is used. Type the name as is, without quotes: the activity wraps it in single quotes and doubles any apostrophe, so names like Sales O'Brien, Sheet with spaces or A1 work. Since version 1.0.5.
• What you read is text: reads return the value as displayed in the sheet (with its number and date format), always as a string. Convert with num() or the date functions before calculating. A formula is read as its result.
• has-headers: in Read sheet and Read range, true makes the first row the column names of the DataTable; false (default) names the columns Column1, Column2, … The result is always a DataTable.

Writes accept formulas

When writing, values are interpreted as USER_ENTERED: if you write =SUM(A1:A10), Google Sheets stores it as a formula, just as if you typed it; 2026-06-15 becomes a date and 1500 a number.

Activities

Session

Activity	Type	Output	What it does
Google Sheets — Open	googlesheets-open	ZoanGoogleSheetsSession	Opens an authenticated session with a spreadsheet
Google Sheets — Close	googlesheets-close	—	Closes the session

Read

Activity	Type	Output	What it does
Read sheet	googlesheets-read-sheet	DataTable	Reads a whole sheet
Read range	googlesheets-read-range	DataTable	Reads a range
Read cell	googlesheets-read-cell	object	Reads one cell
Used range	googlesheets-get-used-range	Dictionary<string,object>	Size of the area with data
Get sheet names	googlesheets-get-sheet-names	List<string>	Names of the sheets
Find cell	googlesheets-find-cell	Dictionary<string,object>	Finds a cell by value
Find row	googlesheets-find-row	Dictionary<string,object>	Finds a row by a column value

Write

Activity	Type	Output	What it does
Write sheet	googlesheets-write-sheet	—	Writes data from A1
Write range	googlesheets-write-range	—	Writes from a given cell
Write cell	googlesheets-write-cell	—	Writes one cell
Append rows	googlesheets-append	—	Appends rows at the end
Clear range	googlesheets-clear-range	—	Clears the content of a range

Google API errors

When Google rejects a call, the activity fails with a GoogleApiException whose message starts with The service sheets has thrown an exception. HttpStatusCode is … followed by Google’s text. The status code tells you the cause:

Code	Usual cause	What to do
NotFound	The spreadsheetId does not exist or was copied wrong.	Copy the ID from the sheet URL.
BadRequest (Unable to parse range: …)	The sheet or range does not exist (misspelled sheet name, invalid range).	Check the names with Get sheet names.
Forbidden	The account has no access to the sheet, or the connection lacks the Sheets permission.	Share the sheet with the account (or the service account’s email) or re-authorize the connection with the permission.
Unauthorized	The token is no longer valid (access revoked, connection expired).	Reconnect the connection in Zoan Cloud.
TooManyRequests	Google’s per-minute request quota was exceeded.	Group reads and writes (one DataTable instead of many cells) and wrap the activity in Retry with a delay.

Catch any of them with Try / Catch; error.Type is GoogleApiException.

Next steps

• Google Sheets — Open — open a spreadsheet.
• Read sheet — read data into a DataTable.
• Excel — the counterpart for local .xlsx files.