Reliable Google Sheets Bridge

Move rows safely in either direction between an Apify dataset and Google Sheets.

• Apify dataset → Google Sheets: append, replace, or upsert.
• Google Sheets → Apify dataset: one dataset item per non-empty row.
• Limited Permissions: the source dataset is chosen with Apify's resource picker and requests READ only.
• Composite upsert keys: match on one column or a combination such as account_id + order_id.
• Failure-safe validation: missing keys, duplicate keys, duplicate headers, oversized cells, malformed IDs, and row-limit overflows are rejected with actionable errors.
• No interactive OAuth: authenticate with your own Google service account.

Why this Actor

Service-account authentication is now available in several Google Sheets Actors. The specific focus here is predictable synchronization under least privilege:

1. The Actor cannot browse your Apify account. Under Limited Permissions it can read only the dataset you select.
2. Upsert supports multiple key columns and rejects missing or ambiguous keys before changing sheet data.
3. Nested objects and arrays are stored as deterministic compact JSON instead of becoming [object Object].
4. Append and upsert expand headers when new fields appear.

Upsert matching rules

• Header names and configured keyColumns are normalized with Unicode NFKC and surrounding whitespace is removed.
• Header matching remains case-sensitive: Name and name are different columns.
• The same normalization is applied to source row dictionaries and destination sheet headers before values are looked up.
• String key values are Unicode-normalized and trimmed. Numeric 101, numeric 101.0, and string "101" match; string "00101" remains distinct.
• Every key part must be non-blank. Duplicate normalized keys in either source or destination stop the run before any sheet mutation.
• Matching rows are replaced in place across the final header width. Nonmatching source rows append; unrelated destination rows are untouched.

5. Row limits fail safely by default instead of silently dropping data.
6. Google quota and temporary-server errors receive automatic SDK retries.

One-time Google setup

1. Create or choose a Google Cloud project.
2. Enable the Google Sheets API.
3. Create a service account and download a JSON key.
4. Share the spreadsheet with the key's client_email.
   • Use Viewer for Sheet → dataset.
   • Use Editor for dataset → Sheet.
5. Paste the complete JSON key into Google service-account JSON. The input field is marked secret and is stored encrypted by Apify.

The Actor never logs the credential or raw Google error bodies. Revoke access at any time by unsharing the spreadsheet or disabling the service-account key.

Operations

Dataset → Google Sheets

Select the source with Source Apify dataset. The picker grants the run read-only access to that one dataset.

Append

• Creates the destination tab when autoCreateSheet is enabled.
• Creates a header for an empty tab.
• Adds new columns to the end of an existing header.
• Aligns incoming values to the final header order.

{
  "operation": "dataset_to_sheet",
  "datasetId": "SELECT_WITH_RESOURCE_PICKER",
  "spreadsheetIdOrUrl": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit",
  "sheetName": "Leads",
  "serviceAccountJson": "{ ... }",
  "writeMode": "append"
}

Replace

Validates and serializes the complete incoming transfer before clearing the tab. An empty source dataset intentionally clears the destination tab.

{
  "operation": "dataset_to_sheet",
  "datasetId": "SELECT_WITH_RESOURCE_PICKER",
  "spreadsheetIdOrUrl": "SPREADSHEET_ID",
  "sheetName": "Current inventory",
  "serviceAccountJson": "{ ... }",
  "writeMode": "replace"
}

Upsert with a composite key

Matching rows are replaced in place; new keys are appended. Every key component must be present and non-empty. Duplicate incoming keys or duplicate destination keys stop the run before sheet writes.

{
  "operation": "dataset_to_sheet",
  "datasetId": "SELECT_WITH_RESOURCE_PICKER",
  "spreadsheetIdOrUrl": "SPREADSHEET_ID",
  "sheetName": "Orders",
  "serviceAccountJson": "{ ... }",
  "writeMode": "upsert",
  "keyColumns": ["account_id", "order_id"]
}

Google Sheets → Apify dataset

The first configured header row becomes the object keys. Empty rows are skipped. If readRange is supplied, headerRow is relative to the returned range; otherwise it is the absolute row within the tab.

{
  "operation": "sheet_to_dataset",
  "spreadsheetIdOrUrl": "SPREADSHEET_ID",
  "sheetName": "Customers",
  "serviceAccountJson": "{ ... }",
  "headerRow": 1,
  "maxRows": 50000
}

Data handling

Input value	Google Sheets value
string, integer, finite number, boolean	Native cell value
null or missing field	Empty cell
object or array	Compact JSON string with stable key order
NaN or Infinity	Rejected
value over 50,000 characters	Rejected

RAW is the recommended value mode because text beginning with = is not executed as a formula. Choose USER_ENTERED only when Google formula/date parsing is intentional.

Limits and batching

• maxRows defaults to 50,000 and can be set as high as 250,000.
• rowLimitAction: "fail" is the default. Set "truncate" only when deliberate truncation is acceptable.
• batchSize defaults to 2,000 and supports 1–10,000 rows.
• Upsert update requests are capped at 500 row ranges per Google batch request.
• Google Sheets API quotas and spreadsheet cell limits still apply.

Output

Every run writes SUMMARY to the default key-value store.

• Sheet → dataset: transferred rows are in the default dataset.
• Dataset → Sheet: the default dataset contains a transfer receipt.
• Health check: the default dataset contains one credential-free status item.

Example summary:

{
  "status": "ok",
  "operation": "dataset_to_sheet",
  "writeMode": "upsert",
  "sheetName": "Orders",
  "rowsRead": 1250,
  "rowsTransferred": 1250,
  "rowsUpdated": 900,
  "rowsAppended": 350,
  "columns": 12,
  "columnsAdded": ["last_seen"],
  "rowLimitTruncated": false
}

Errors use stable codes such as CREDENTIAL_INVALID, SPREADSHEET_INACCESSIBLE, DATASET_INACCESSIBLE, UPSERT_KEY_VALUE_MISSING, and ROW_LIMIT_EXCEEDED.

Health-check mode

The prefilled default input is:

{"operation": "health_check"}

It uses no Google credential or private resource, succeeds quickly, and writes a non-empty default dataset. This is designed for Apify Store's automatic daily test.

Pricing behavior

The v0.3.1 source contains verified Apify Python SDK 4.0.2 charging calls, but charging is disabled unless the Actor environment variable RSB_ENABLE_CHARGING=true is set. Do not enable it until the matching Store events are configured. See PRICING_AND_KILL_RULES.md and DEPLOY.md.

Important replace limitation

Google Sheets does not provide a transaction spanning clear and multiple value writes. The Actor validates all local data and spreadsheet access before clearing, but a network or Google outage during a large replace can still leave a partially written tab. Use a copied spreadsheet or scheduled backup for business-critical replacements.

Support information

When reporting a problem, provide the Apify run ID, operation, write mode, approximate row count, and error code. Never send a service-account private key in an issue or message.