Google Sheets Sync (BYOK)

Write rows from any Apify Actor dataset into your own Google Sheets — your Google account, no shared credentials.

What it does

• Copies a dataset into a Google Sheet, into several tabs, or across several spreadsheets.
• Skips rows whose key is already there (append + keyField) or updates them in place (upsert).
• Handles the Google side itself: throttling, retries with backoff, adaptive batches, auto-split, and a machine-readable run report.

Works with any dataset — a Store Actor, your own scraper, or one picked with the resource picker. Credentials stay yours and are never stored.

Quick start

1. Get clientId, clientSecret and refreshToken from your own Google account: create an OAuth client of type Desktop app with the Sheets API enabled (Google Cloud Console), then a refresh token from the OAuth 2.0 Playground with scope https://www.googleapis.com/auth/spreadsheets.
2. Paste them into the actor input (they are secret fields, encrypted by Apify).
3. Set spreadsheetId (the ID or the full URL) and sheetName. Leave the ID empty to create a brand-new spreadsheet.
4. Choose a mode: append, upsert or replace.
5. Run the actor and open the report: URL, rows written, columns, warnings and error codes.

A typical first run:

{
    "clientId": "…apps.googleusercontent.com",
    "clientSecret": "GOCSPX-…",
    "refreshToken": "1//…",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Leads",
    "mode": "replace",
    "dryRun": true
}

dryRun: true validates credentials, reads the dataset and prints the plan (rows, columns, splits, ranges) without writing.

Important: while your OAuth app is in Testing status, Google issues a refresh token that lives only 7 days. Publish the app (OAuth consent screen → Publish app) to make the token permanent.

Input parameters

Parameter	Default	What it does
clientId, clientSecret, refreshToken	—	Your Google credentials (BYOK). The only required fields; both secrets are encrypted by Apify.
mode	append	append, upsert or replace — see Modes.
spreadsheetId, sheetName, createSheetIfMissing	Sheet1, false	Spreadsheet ID or URL (empty = create a new file) and the tab name; createSheetIfMissing creates a missing tab instead of failing.
datasetId, maxItems	current run, 0	Source dataset: pick one with the resource picker (under limited permissions only picked datasets are readable), or read the dataset of the run itself. maxItems caps rows (0 = all).
columnsOrder, unknownColumns	natural, append	Explicit column order; fields outside it are appended at the end or dropped.
writeHeader	once	once writes the header into an empty sheet only, always rewrites it, never skips it.
keyField, keyNormalization	—, trim	Row key for upsert or dedupe; compared with exact, trim or lowercase.
valueInputOption, escapeFormulas	USER_ENTERED, true	Let Google parse numbers/dates; escape cells starting with =, +, -, @ so data is never executed.
addMissingColumns	true	upsert: extend the sheet header when the dataset brings new fields.
chunkSize, maxPayloadMb	4000, 2	Rows per request — the main speed lever (1000 for wide rows, up to 10000 for small sheets); payload cap: 2 MB is the speed optimum, 10 MB the accepted maximum
requestsPerMinute, maxRetries	60, 10	Throttle per direction (60 = Google's per-user limit); retries 429/5xx/network with backoff.
autoSplit, maxCellsPerSpreadsheet, maxCellsPerSheet, safetyMargin	off, 20M, 5M, 100k	What to do when data does not fit, plus the cell budgets used to decide.
dryRun, continueOnChunkError, resume	false, true, true	Plan without writing; keep going after a failed batch; continue an interrupted run without duplicates.

Modes

• append — writes new rows at the bottom, never touches existing ones; keyField skips duplicates. Use it for logs and incremental syncs.
• upsert — requires keyField: matching rows are overwritten in place (the whole row), new keys are appended, existing row order is preserved. Use it to keep a sheet in sync with a changing source.
• replace — destructive: clears the tab and rewrites it from the dataset. Use it for snapshot-style reports.

Limits and auto-split

Google allows 20,000,000 cells per spreadsheet and 60 read plus 60 write requests per minute; the actor stays inside both (requestsPerMinute throttles reads and writes separately, batches shrink under maxPayloadMb, cells are counted first). autoSplit decides what happens when data does not fit: fail and list how many rows fit (off), continue in new tabs (sheets), or in new tabs and spreadsheets (spreadsheets, URLs in the report).

Run report

Every run publishes one compact report (the run dataset and the OUTPUT key-value record) that a person or an agent can read:

{ "status": "succeeded", "mode": "upsert", "spreadsheet": { "url": "https://docs.google.com/spreadsheets/d/1Bxi…", "sheet": "Leads" },
  "totals": { "datasetItems": 125000, "written": 4200, "updated": 120800, "errors": 0 }, "columns": ["email", "name", "company"], "errors": [] }

Errors carry a code (AUTH_FAILED, PERMISSION_DENIED, NOT_FOUND, QUOTA_EXCEEDED, VALIDATION_ERROR), a message and a hint with the parameter to fix; failed batches are listed individually.

Run from AI agents (MCP)

The actor can be called by an agent through the Apify MCP server: call-actor to start a run, get-actor-run for its status, get-dataset-items or get-key-value-store-record (key OUTPUT) for the report.

• Start with dryRun: true: the report contains the plan and nothing is written, so the agent can show it to you first.
• MCP calls an Actor, never an Actor Task, so the input carries your credentials. To keep them from the agent, store them in an Actor Task and run it from Console.
• Client setup: add https://mcp.apify.com?tools=actors in Claude Desktop, Cline, VS Code or any other MCP client.

Troubleshooting

Symptom	Fix
clientId looks invalid	Use the OAuth client ID of a Desktop app, not the Google Cloud project ID.
refresh_token is expired or revoked	Re-check the secrets, or publish the OAuth app (a Testing app token lives 7 days) and mint a new one.
Access denied	Add the Google account to Test users on the consent screen, or publish the app.
403 … Share the spreadsheet	Share it with the account that owns the refresh token (editor rights).
Sheet (tab) X was not found	Use a tab name from the error message, or set createSheetIfMissing: true.
Cannot write N rows … only M rows fit	Turn on autoSplit: sheets or spreadsheets, or reduce maxItems.
QUOTA_EXCEEDED / 429	Lower requestsPerMinute, wait a minute, re-run with resume: true — already written rows are not duplicated.
PERMISSION_DENIED on a dataset	Pick the dataset with the resource picker in the Run form, or leave datasetId empty.

FAQ

• Why BYOK? No shared service account: data goes straight to your spreadsheet with your own Google credentials, never stored by the actor; revoke access any time.
• Can I use a service account? No — the Sheets scope needs a user refresh token; service accounts cannot access a personal Google Drive.
• Why is = escaped? Scraped strings sometimes start with =, which Sheets would run as a formula. Such cells get an apostrophe; set escapeFormulas: false if your data legitimately contains formulas.
• How fast is it? ~2,400 rows/s at the defaults (25,000 rows in 10.6 s), up to ~3,200 rows/s with chunkSize: 10000; use 1000 for wide rows.
• What if the run is interrupted? With resume: true the next run continues from the last written batch; keyField prevents duplicates.
• Will it overwrite my formatting? No — only cell values are written; formatting, notes and other columns stay. Set valueInputOption: RAW to stop Google interpreting values as dates or numbers.
• Excel/xlsx? Google Sheets only; export from Sheets if you need .xlsx.

Cost

• The sync itself costs ~$0.49 per 1M rows. If your invoice is much higher, the cost is likely coming from the producer Actor writing rows to the dataset — not from this sync.
• Keep resume: true (default) to avoid paying twice for the same data on retries.
• Further memory/chunkSize tweaks give single-digit percent savings. The big lever is the number of runs.

Limitations

• Don't edit the sheet or start a second sync while the actor runs: row order can shift.
• upsert overwrites the whole row — fields missing in a dataset item become empty cells.
• A row of empty cells counts as “no data”; keep it with writeHeader: always.
• With duplicate keys already in the sheet only the first row per key is updated (the report lists them); rows are never deleted or reordered.
• replace clears the sheet first, so an interrupted run can leave the tab partially filled.

Feedback

Found a bug or a missing parameter? Open an issue on the actor's page in Apify Console (Issues tab) with the run ID and the report.