Google Sheets Sync — Reliable Import & Export

Move data between Apify and Google Sheets without babysitting the run. This Actor exports an Apify dataset (or a plain JSON array of rows) into a Google Sheet with append, replace, upsert-by-key, or dedupe semantics, and imports a sheet range back out into a dataset. The reason to pick this one over the existing options: reliability. Writes are batched to stay inside Google's request limits, transient Google errors are retried with backoff instead of failing the run, an interrupted run can resume instead of restarting from zero, and when something can't be fixed automatically you get a plain-English reason instead of a raw HTTP error.

Who it's for

• Scraper/automation pipelines that need their Apify dataset to land in a sheet a non-technical teammate or client already has open, instead of a JSON/CSV export.
• Reporting and ops workflows that re-run on a schedule and need the same sheet updated in place (upsert by an ID column) rather than growing a new tab every time.
• Dashboards and spreadsheet-driven tools (Google Sheets add-ons, Data Studio/Looker Studio connectors, finance/ops trackers) that read their input from a tab this Actor keeps in sync.
• The reverse direction too: pulling a sheet that a human maintains (a config list, a target list, a list of URLs to scrape) into an Apify dataset so another Actor can consume it as structured input.

What makes this reliable (the actual point of this Actor)

• Limit-safe batching. Writes are chunked to stay under configurable cell-count and payload-size ceilings (defaults: 40,000 cells / 9 MB per request), comfortably inside Google's per-request limits, so large syncs don't get rejected outright.
• Exponential backoff with jitter on 429/5xx. A rate limit or a transient Google server error is retried automatically (configurable retry count); anything else (bad input, no access) fails immediately instead of retrying pointlessly.
• Resumable writes. Progress is checkpointed to the run's key-value store. If the run is killed and retried against the same spreadsheet/sheet/mode, it continues instead of re-writing everything (and re-running is always safe even without this: upsert and dedupe modes never double-write a row with the same key).
• Schema drift handling. If a later row has a field the sheet's header doesn't have yet, the column is appended at the end — existing columns and existing rows are never shifted. Rows missing a field just get a blank cell for it.
• Type coercion. Nested objects are flattened into parent.child columns (or JSON-stringified in one cell if you turn flattening off); arrays of plain values are joined with a delimiter you choose; arrays of objects are JSON-stringified; dates/numbers/ booleans come through as native Sheets values, not stringified Python reprs. Export writes use valueInputOption=RAW by default (see valueInputOption below), so a number like 54.07 is stored as that exact number even in a comma-decimal-locale sheet, not reinterpreted through the sheet's locale. Import reads with

  valueRenderOption= UNFORMATTED_VALUE
  and dateTimeRenderOption=FORMATTED_STRING, so numbers/booleans come back as their real typed value instead of a locale-formatted display string (e.g. never "54,07" when the underlying cell is the number 54.07); date/time cells still come back as readable strings rather than raw serial-number floats. TRUE/FALSE text cells become booleans, numeric-looking text cells become numbers, blank cells become null.
• A guard before you hit Google's 10,000,000-cell-per-spreadsheet ceiling, with an error that tells you what to do about it (split across sheets/tabs, drop columns) instead of a bare API rejection.
• Errors that tell you what to fix. "Sheet not shared with <service-account-email>" instead of a bare 403; "spreadsheet not found or not accessible" instead of a bare 404; "exportMode=upsert requires keyColumns" instead of a stack trace mid-run.

Modes

Export (dataset/rows -> sheet):

exportMode	Behavior
append	Add all rows after whatever is already in the sheet.
replace	Clear the sheet tab, then write the header and rows fresh.
upsert	Rows whose keyColumns value(s) match an existing row update that row in place; everything else is appended. Requires keyColumns. See upsertStrategy below for how a partial row (missing some fields) affects the columns it doesn't mention.
dedupe	Like append, but a row whose key already exists in the sheet, or already appeared earlier in the same run, is skipped. Requires keyColumns.

upsertStrategy (only used when exportMode is upsert):

upsertStrategy	Behavior
merge (default)	A matched row keeps its existing cell values for any column the new row doesn't include. Updating {id: 10, price: 12.5} against a row that also has geo.lat/tags/active columns leaves those columns exactly as they were — only price (and any other field the new row provides) changes.
replace	The old behavior: a matched row is overwritten entirely. Any column the new row omits is cleared to blank, even if the sheet had a value there before.

Import (sheet -> dataset): reads importRange from sheetName, treats headerRow as column names, and pushes one dataset item per row below it.

Input

Field	Type	Default	Notes
mode	"export" | "import"	"export"
serviceAccountJson	string (secret)	-	Full contents of a Google service account key JSON file. Not needed when dryRun is on.
spreadsheetId	string	-	From the sheet's URL. Required unless dryRun is on.
sheetName	string	"Sheet1"	The tab to read/write.
exportMode	string	"append"	append | replace | upsert | dedupe.
keyColumns	array of strings	[]	Required for upsert/dedupe.
upsertStrategy	string	"merge"	upsert only. merge: a matched row keeps existing values for columns the new row omits. replace: the matched row is overwritten entirely (old columns not in the new row are blanked).
sourceDatasetId	string	-	Export from this dataset instead of the current run's default dataset.
sourceRunId	string	-	Export from an earlier run's default dataset (used if sourceDatasetId is blank).
inputRows	array (JSON)	[]	Export exactly these objects (used if the two above are blank).
flattenNestedObjects	boolean	true	Off = JSON-stringify nested values into one cell instead of parent.child columns.
arrayJoinDelimiter	string	", "	Delimiter used to join arrays of plain values into one cell.
importRange	string	"A1:ZZ"	A1 notation; can include its own Tab! prefix.
headerRow	integer	1	Which row (within the range) holds column names, for import.
createSheetIfMissing	boolean	true	Export mode: create the tab if sheetName doesn't exist yet.
enableResume	boolean	true	Checkpoint export progress so a retried run continues instead of restarting.
valueInputOption	string	"RAW"	Export mode. RAW: values are written exactly as given — a number like 54.07 is always stored as that number, regardless of the sheet's locale (comma-decimal locales included). USER_ENTERED: parsed as if typed into the UI, which is what you need for a cell that should become a formula (e.g. "=A1+B1"), but risks locale-dependent reinterpretation of plain numeric/date-like strings. Only used for data rows — the header row is always written with RAW.
maxCellsPerBatch / maxBatchBytes / maxRetries	number	40000 / 9000000 / 5	Advanced batching/retry tuning; the defaults are safe, conservative choices.
dryRun	boolean	false	Run the whole pipeline against an in-memory fake sheet — no credentials, no network calls to Google. See "Testing without credentials" below.

Output

Import mode: one dataset item per sheet row, keyed by the header row's column names, with best-effort type coercion (numbers/booleans/null for blanks).

Export mode: one summary item per run:

{
  "summary_mode": "export",
  "summary_exportMode": "upsert",
  "summary_spreadsheetId": "1AbC...",
  "summary_sheetName": "Sheet1",
  "summary_rowsRead": 3,
  "summary_rowsWritten": 3,
  "summary_rowsSkippedDuplicate": 0,
  "summary_rowsResumedSkipped": 0,
  "summary_columnsWritten": ["id", "name", "tags", "address.city", "address.zip"],
  "summary_batches": 2,
  "summary_dryRun": false,
  "summary_durationSecs": 0.42
}

Google service account setup (one-time, step by step)

Google Sheets access for a headless Actor uses a service account, not your personal Google login. You do this once per Google Cloud project; after that, sharing a new sheet with the same service account email takes ten seconds.

1. Go to the Google Cloud Console and select or create a project.
2. Open APIs & Services -> Library, search for Google Sheets API, and click Enable.
3. Open APIs & Services -> Credentials -> Create Credentials -> Service account. Give it any name (e.g. "sheets-sync"), skip the optional role/access steps (this Actor doesn't need any Google Cloud IAM role — sheet access is granted separately, in step 6), and click Done.
4. Click into the service account you just created, open the Keys tab, click Add key -> Create new key, choose JSON, and confirm. A .json file downloads — this is the credential.
5. Open that downloaded file in a text editor, select all, copy it, and paste the whole thing into this Actor's serviceAccountJson input field.
6. Open the downloaded JSON file again and find the "client_email" field — it looks like something@your-project.iam.gserviceaccount.com. Open the target Google Sheet in your browser, click Share, paste that email address, set its permission to Editor, and share/send (uncheck "Notify people" if you don't want an email sent to a service account).
7. Copy the spreadsheet ID out of the sheet's URL — the long string between /d/ and /edit in https://docs.google.com/spreadsheets/d/<THIS_PART>/edit — into this Actor's spreadsheetId input field.

That's it — no OAuth consent screen, no refresh tokens to babysit, and the key doesn't expire on its own. If you ever see the error "sheet not shared with <email>", it means step 6 wasn't done (or was done for a different sheet/service account) — re-share the sheet with the exact client_email from your JSON key.

(An OAuth refresh-token flow was considered as an alternative, but a service account is simpler for a headless Actor — no browser consent screen, no token refresh to manage — so it's the only auth mode this Actor implements.)

Testing without credentials (dryRun)

Set dryRun: true and leave serviceAccountJson blank. The Actor runs its full pipeline — reading input, resolving source rows, flattening/coercing, computing schema drift, batching, checking resume state — against an in-memory fake spreadsheet instead of the real Google Sheets API. Nothing leaves the run; the dataset gets the same summary item shape a real export would produce, with summary_dryRun: true. This is how this Actor's own build was verified end to end without ever holding a real Google credential — see Tests below.

Limitations

• Resume is a prefix-skip, not a transaction log. It's exact for append (nothing is ever reprocessed unnecessarily) and safe-but-conservative for dedupe/upsert (a resumed run may harmlessly re-check a few already-handled rows, since both modes are naturally idempotent — re-upserting the same key or re-skipping an already-present key changes nothing).
• The max-cells guard is a post-write safety check, not a pre-flight reservation — it fails the run loudly if a write pushed (or would push) the spreadsheet over Google's 10,000,000-cell ceiling, but it does not reserve headroom against a concurrent writer.
• No deep validation of the target sheet's existing data. Only the header row and, for upsert/dedupe, the key column(s) are read and matched against — unrelated existing columns/rows are left exactly as they are, not diffed or validated.
• Import range parsing is A1 notation only (e.g. A1:F1000 or Tab!A1:F1000); it does not support named ranges.
• The OAuth refresh-token auth mode is not implemented — service account is the only supported credential type (see the setup guide above for why).
• Real-API coverage is a manual, owner-run step, not part of CI. Export/import/batching/ retry/resume/merge-upsert/type-coercion logic is covered by unit tests against a fake Sheets client plus a local dryRun pipeline run; tests/test_integration_real_sheets.py (a round-trip export+import) and a one-off 500-row merge/typing load test have both been run successfully by the Actor owner against a real, non-US-locale (ru_RU) throwaway spreadsheet, confirming upsertStrategy: "merge" preserves untouched columns and that numbers/booleans round-trip with their correct type instead of a locale-formatted string — but neither runs automatically, since no Google credential is available in CI.

FAQ

Why did my run fail with "sheet not shared with ...@...iam.gserviceaccount.com"? The spreadsheet isn't shared with your service account yet, or was shared with a different one than the serviceAccountJson you supplied. See step 6 of the setup guide above.

Can I export straight from a scraper Actor's results without downloading anything? Yes — set sourceRunId to that Actor run's ID (or sourceDatasetId to its dataset ID directly) and leave inputRows empty.

What happens if two rows in my export have the same key in upsert mode? The later one wins — the row is written once, with the last value seen for that key in the input. With the default upsertStrategy: "merge", "wins" means "wins for the fields it provides" — a field neither the winning nor an earlier row/existing sheet row ever set is just blank, but a field only an earlier occurrence set is preserved.

Will upsert blank out columns my updated row doesn't mention? Not by default. upsertStrategy: "merge" (the default) keeps the sheet's existing value for any column the new row doesn't include — e.g. upserting {id: 10, price: 12.5} never touches that row's geo.lat/tags/active cells. Set upsertStrategy: "replace" to get the old all-or-nothing behavior, where an omitted column is cleared.

Does replace mode support resuming? No, by design: a replace clears the sheet first, so a partial replace cannot be safely "continued" — it always reruns in full rather than risk mixing old and new data.

Why is rows-written based on rows actually written, not rows read? So that dedupe runs where most rows are already present don't cost the same as a run that had to do real work — see PRICING.md.

How is this priced? See PRICING.md for the proposed pay-per-event plan (not yet enabled on the platform).