# Google Sheets Sync — Reliable Import & Export (`gravelly_caladium/google-sheets-sync`) Actor

Reliable Google Sheets import & export: send Apify datasets or JSON to a sheet (append, replace, upsert with merge, dedupe) or import a sheet to a dataset. Auto-grows the grid, handles schema drift, retries rate limits, clear error messages.

- **URL**: https://apify.com/gravelly\_caladium/google-sheets-sync.md
- **Developed by:** [Relay Data Tools](https://apify.com/gravelly_caladium) (community)
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

Pay per usage

This Actor is paid per platform usage. The Actor is free to use, and you only pay for the Apify platform usage, which gets cheaper the higher subscription plan you have.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-usage

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

## 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:

```json
{
  "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](https://console.cloud.google.com/) 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](PRICING.md).

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

# Actor input Schema

## `mode` (type: `string`):

"Export" writes rows into a Google Sheet. "Import" reads a sheet range and pushes each row as a dataset item.

## `serviceAccountJson` (type: `string`):

Paste the full JSON key file downloaded for a Google Cloud service account with the Sheets API enabled. Share the target spreadsheet with the service account's client\_email as Editor first — see the README setup guide. Leave blank when dryRun is enabled.

## `spreadsheetId` (type: `string`):

The ID from the sheet's URL: docs.google.com/spreadsheets/d/\<THIS\_PART>/edit. Not required when dryRun is enabled.

## `sheetName` (type: `string`):

Name of the worksheet tab to read from or write to.

## `exportMode` (type: `string`):

How to write rows in export mode. Append: add rows after existing data. Replace: clear the sheet and write fresh. Upsert: update rows matching Key column(s), append the rest. Dedupe: like append, but skip rows whose key already exists in the sheet or earlier in the same run.

## `keyColumns` (type: `array`):

Column/field name(s) that uniquely identify a row. Required for Upsert and Dedupe export modes.

## `upsertStrategy` (type: `string`):

Only used when exportMode is Upsert. Merge (default): a matched row keeps its existing cell values for any column the new row doesn't include, so a partial update never blanks out other columns. Replace: a matched row is overwritten entirely, exactly like the old behavior -- any column the new row omits is cleared.

## `sourceDatasetId` (type: `string`):

Export rows from this dataset ID instead of the current run's default dataset. Leave blank to use sourceRunId, inputRows, or the default dataset (checked in that order).

## `sourceRunId` (type: `string`):

Export rows from the default dataset of this earlier Actor run ID. Used only if sourceDatasetId is blank.

## `inputRows` (type: `array`):

Export exactly these JSON objects instead of reading a dataset. Handy for testing or for small ad-hoc pushes. Used only if sourceDatasetId and sourceRunId are both blank.

## `flattenNestedObjects` (type: `boolean`):

Turn nested objects into dot-separated columns, e.g. address.city. Turn off to JSON-stringify nested values into a single cell instead.

## `arrayJoinDelimiter` (type: `string`):

Arrays of plain values (strings/numbers/booleans) are joined into one cell with this delimiter. Arrays of objects are always JSON-stringified.

## `importRange` (type: `string`):

Range to read in import mode, e.g. A1:F1000. The sheetName above is used as the tab unless you include a tab name here yourself.

## `headerRow` (type: `integer`):

Row within the import range that holds column headers. Rows above it are ignored; rows below become dataset items keyed by these headers.

## `valueInputOption` (type: `string`):

How Google Sheets interprets values you write. RAW (default): stored exactly as given -- a number like 54.07 is always stored as that number, regardless of the sheet's locale (e.g. a comma-decimal locale). USER\_ENTERED: parsed as if a person typed it into the UI -- needed for a cell that should become a formula (e.g. "=A1+B1"), but risks locale-dependent reinterpretation of plain numeric/date-like strings.

## `createSheetIfMissing` (type: `boolean`):

If the named sheet tab does not exist yet in the spreadsheet, create it instead of failing (export mode only).

## `enableResume` (type: `boolean`):

Store write progress in the key-value store so that if this run is retried (same spreadsheet, sheet, and mode), it continues from where it left off instead of rewriting or re-appending already-written rows.

## `maxCellsPerBatch` (type: `integer`):

Self-imposed ceiling on cells written per Sheets API request, well under Google's per-request limits, to keep requests small and retry-friendly.

## `maxBatchBytes` (type: `integer`):

Self-imposed ceiling on the JSON payload size per Sheets API request, kept under Google's 10 MB request limit.

## `maxRetries` (type: `integer`):

How many times to retry a single Sheets API call after a 429 (rate limit) or 5xx (server error) response, with exponential backoff and jitter, before giving up.

## `dryRun` (type: `boolean`):

Run the full pipeline (read input, flatten/coerce rows, batch, resume-check) against an in-memory fake sheet instead of calling Google. Useful for testing without credentials. The dataset gets a summary item describing what would have been written/read.

## Actor input object example

```json
{
  "mode": "export",
  "sheetName": "Sheet1",
  "exportMode": "append",
  "keyColumns": [],
  "upsertStrategy": "merge",
  "inputRows": [],
  "flattenNestedObjects": true,
  "arrayJoinDelimiter": ", ",
  "importRange": "A1:ZZ",
  "headerRow": 1,
  "valueInputOption": "RAW",
  "createSheetIfMissing": true,
  "enableResume": true,
  "maxCellsPerBatch": 40000,
  "maxBatchBytes": 9000000,
  "maxRetries": 5,
  "dryRun": false
}
```

# Actor output Schema

## `results` (type: `string`):

No description

## `resultsAll` (type: `string`):

No description

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "sheetName": "Sheet1",
    "arrayJoinDelimiter": ", ",
    "importRange": "A1:ZZ"
};

// Run the Actor and wait for it to finish
const run = await client.actor("gravelly_caladium/google-sheets-sync").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = {
    "sheetName": "Sheet1",
    "arrayJoinDelimiter": ", ",
    "importRange": "A1:ZZ",
}

# Run the Actor and wait for it to finish
run = client.actor("gravelly_caladium/google-sheets-sync").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "sheetName": "Sheet1",
  "arrayJoinDelimiter": ", ",
  "importRange": "A1:ZZ"
}' |
apify call gravelly_caladium/google-sheets-sync --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,gravelly_caladium/google-sheets-sync"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/wdQjh6bsyhBODnyHc/builds/AqszE9yxoZLYUBUtB/openapi.json