Dataset to Google Sheets — Reliable Writer

Send your Apify datasets or JSON rows straight into Google Sheets — with validation that catches bad input before it costs you a failed run.

Most Sheets integrations fail after they start writing: they hit a quota mid-run, choke on an oversized cell, or die on an auth error — leaving you with a half-filled sheet and a generic FAILED. This actor is built the other way around. It checks everything first, tells you exactly what's wrong in plain language, and only then writes — in quota-safe batches. Broken input costs you nothing.

---

Why this actor

	This actor	Typical Sheets integration
Auth	Your own Google service account — nothing shared	Shared OAuth app (subject to "This app is blocked" and expiring tokens)
Bad input	Caught in a free preflight with row numbers	Fails mid-write with a generic error
Large syncs	Batched & paced under Google's quotas	Often dies partway through
Column safety	Matched by name; refuses ambiguous headers	Positional writes can silently shift data
Billing	Pay only for cells actually written	Often charged per run regardless of outcome

---

Features

• 🔐 Your own service account. You authenticate with a Google service account you control — no shared app that Google can block, no refresh tokens that expire on you. Two-minute setup below.
• 🧪 Free preflight validation. Bad spreadsheet URL, empty dataset, missing upsert keys, cells over Google's 50,000-character limit — all reported before any write and any charge, with the exact row numbers.
• 📦 Quota-aware batching. Writes are split under Google's request-size limits and paced under the per-minute write quota, so large jobs are built to finish instead of stopping at row 30,000.
• 🔁 Three write modes. append (add rows), replace (overwrite the sheet), and true upsert (update rows matching your key columns, append the rest).
• 🧭 Header safety. Columns are matched by name, never by position. If your data has columns the sheet doesn't — or the sheet's headers are blank/duplicated — the actor stops with a clear message instead of scrambling your columns.
• 💬 Actionable errors. A 403 becomes "share the spreadsheet with your-account@…iam.gserviceaccount.com (Editor) and retry" — not a stack trace.
• 💸 Fair pricing. One metered event: per 1,000 cells actually written. Stop at a budget limit and every charged cell was written — nothing beyond it. replace verifies your budget covers the whole write before it clears anything.
• 🔗 Pipeline-friendly. Reads the run's default dataset when chained after a scraper, and pushes a machine-readable report you can monitor or feed to the next actor.

---

Setup: Google service account (one time, ~2 minutes)

1. Open the Google Cloud Console and create a project (or use an existing one). Billing is not required.
2. Enable the Google Sheets API: APIs & Services → Library → Google Sheets API → Enable.
3. Create a service account: IAM & Admin → Service Accounts → Create. Skip the optional roles — none are needed.
4. Open the new account → Keys → Add Key → Create new key → JSON. A JSON file downloads. This is your googleServiceAccountKey.
5. Copy the account's email (looks like name@project-id.iam.gserviceaccount.com) and Share your spreadsheet with it as an Editor.

That's it — the same key works for every spreadsheet you share with that email.

---

Input

Field	Required	Description
spreadsheet	✅	Full Google Sheets URL or the bare spreadsheet ID.
googleServiceAccountKey	for real writes	Service account JSON key (stored encrypted). Not needed for a dry run.
mode		append (default), replace, or upsert.
keyColumns	for upsert	Column names that uniquely identify a row.
datasetId		Dataset to read. Omit to use the run's default dataset.
rows		Inline JSON rows — an alternative to datasetId.
sheetName		Target tab. Defaults to the first sheet; created if missing.
valueInputOption		RAW (default) stores values as-is; USER_ENTERED parses dates/formulas.
dryRun		true = validate and plan only, no writes, no charge.

Example — append a dataset

{
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "mode": "append",
    "datasetId": "YOUR_DATASET_ID",
    "googleServiceAccountKey": { "type": "service_account", "client_email": "…", "private_key": "…" }
}

Example — upsert by id

{
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "mode": "upsert",
    "keyColumns": ["id"],
    "rows": [
        { "id": 1, "status": "updated in place" },
        { "id": 999, "status": "new row, appended" }
    ],
    "googleServiceAccountKey": { "type": "service_account", "client_email": "…", "private_key": "…" }
}

Example — validate for free

{
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "datasetId": "YOUR_DATASET_ID",
    "dryRun": true
}

Output

Every run pushes one machine-readable report to the dataset. A successful sync:

{
    "type": "sync_report",
    "status": "SUCCEEDED",
    "sheetTitle": "Sheet1",
    "mode": "upsert",
    "rowsAppended": 1,
    "rowsUpdated": 1,
    "cellsWritten": 6,
    "writeRequests": 2,
    "durationMs": 1840
}

A validation failure instead returns a preflight_report (with the failing checks and row numbers) or an error_report (with a code, message, and a hint on how to fix it) — and charges nothing.

Error reference

Code	Meaning	What to do
INVALID_SPREADSHEET_REF	URL/ID not recognized	Pass the full docs.google.com/spreadsheets/d/… URL
EMPTY_DATA	Nothing to write	Check datasetId / rows
MISSING_KEY_COLUMNS · KEY_COLUMN_NOT_IN_DATA · EMPTY_UPSERT_KEY	Upsert misconfigured	List key columns that exist and are filled in your data
CELL_TOO_LARGE	Cell over 50,000 chars (Google's limit)	Trim the listed rows
SHEET_CELL_LIMIT	Would exceed the 10M-cell spreadsheet limit	Split across spreadsheets
HEADER_MISMATCH · SHEET_HEADER_BLANK · SHEET_HEADER_DUPLICATE	Sheet headers can't safely receive this data	Fix the header row or use mode=replace
PERMISSION_DENIED	Sheet not shared with the service account	Share with the email shown in the error
BUDGET_TOO_SMALL_FOR_REPLACE	Run budget doesn't cover a full replace	Raise the budget — nothing was cleared
SPREADSHEET_NOT_FOUND	Wrong or deleted spreadsheet	Check the URL

Pricing

One pay-per-event charge: $0.005 per 1,000 cells written (rounded up per batch). Dry runs, validation failures, and permission errors are free — you pay only when data lands in your sheet.

FAQ

Why a service account instead of "Sign in with Google"? A service account is a robot identity you own. It can't be blocked as part of a shared app, and its access never silently expires — the two most common reasons Sheets integrations break. The trade-off is one extra step: sharing the sheet with the account's email.

Is my key safe? The key is marked as a secret input and stored encrypted by Apify. It only grants access to spreadsheets you explicitly share with the account — nothing else in your Google account.

Which permissions does it need? Just the Google Sheets API and Editor access on the specific spreadsheets you share. No Drive-wide access, no IAM roles.

How much will a run cost? Cells × $0.005 / 1000. A 10,000-row sheet with 8 columns = 80,000 cells ≈ $0.40. Validate first with dryRun to see the exact cell count for free.

Does it work with any scraper? Yes. Chain it after any actor and omit datasetId to pick up that run's default dataset, or point datasetId at a specific dataset.

What if my data has nested objects? Nested objects and arrays are written as JSON strings, and the run reports how many cells that affected.