# Google Sheets Sync (BYOK) (`assured_hippeastrum/google-sheets-sync`) Actor

Append, upsert or replace rows from an Actor dataset into Google Sheets using your own Google OAuth credentials.

- **URL**: https://apify.com/assured\_hippeastrum/google-sheets-sync.md
- **Developed by:** [Nevo Kani](https://apify.com/assured_hippeastrum) (community)
- **Categories:** Developer tools, Integrations
- **Stats:** 1 total users, 0 monthly users, 0.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 (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](https://console.cloud.google.com/apis/credentials)), then a refresh token from the [OAuth 2.0 Playground](https://developers.google.com/oauthplayground) 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:

```json
{
    "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](#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 `drop`ped. |
| `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:

```json
{ "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](https://mcp.apify.com?tools=actors): `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.

# Actor input Schema

## `clientId` (type: `string`):

OAuth client ID of a Desktop app from Google Cloud Console. Format: <number>-<hash>.apps.googleusercontent.com. See README for the 5-minute setup.

## `clientSecret` (type: `string`):

OAuth client secret of the same Desktop app. Starts with GOCSPX-. Marked as secret: encrypted by Apify and decrypted only inside the run.

## `refreshToken` (type: `string`):

Long-lived refresh token for scope https://www.googleapis.com/auth/spreadsheets, obtained via https://developers.google.com/oauthplayground. Marked as secret. Note: while your OAuth app is in Testing status the token expires in 7 days; publish the app to make it permanent.

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

append adds new rows at the bottom and never touches existing ones; upsert updates rows whose keyField matches and appends the rest; replace clears the target sheet and writes everything.

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

ID from https://docs.google.com/spreadsheets/d/<ID>/edit or the full URL. Leave empty to create a brand-new spreadsheet owned by your Google account; its URL is returned in the run report.

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

Tab name inside the spreadsheet, for example Sheet1. Case-sensitive.

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

When off (default) the run fails with a clear message listing existing tabs. When on, a missing tab is created automatically.

## `datasetId` (type: `string`):

Dataset with the rows to write. Pick it with the resource picker: under LIMITED\_PERMISSIONS the platform grants access only to the datasets you select this way. Leave the field empty to read the dataset of the current run instead (start the Actor with "Run actor" from a dataset page, or use an Actor Task).

## `maxItems` (type: `integer`):

Stop after this many dataset items. 0 means no limit.

## `columnsOrder` (type: `array`):

Explicit list of dataset fields to write, in this order. Fields not listed here are handled by unknownColumns. Leave empty to keep the natural order of keys as they appear in the first item.

## `unknownColumns` (type: `string`):

append keeps unlisted fields as extra columns at the end; drop writes only the listed ones.

## `writeHeader` (type: `string`):

once writes the header only when the target sheet is empty (recommended); always writes it before the first batch of this run; never never writes it.

## `keyField` (type: `string`):

Dataset field used as a unique key: required for upsert, optional for append (deduplication against rows already in the sheet). Items without this field are skipped and counted in the report.

## `keyNormalization` (type: `string`):

How keys are compared: exact, trim (default, ignores surrounding spaces) or lowercase (trim + case-insensitive).

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

USER\_ENTERED lets Google parse numbers, dates and formulas (dates stay dates); RAW writes everything as text.

## `escapeFormulas` (type: `boolean`):

Prefix cells that start with = + - @ with an apostrophe so scraped data can never be executed as a formula by Google Sheets. Recommended on.

## `addMissingColumns` (type: `boolean`):

In upsert mode, extend the sheet header with dataset fields that the sheet does not have yet. When off, such fields are dropped and reported.

## `chunkSize` (type: `string`):

Batch size — the main speed lever; rows/s measured on a 25k-row sheet. 4000 is the recommended balance; 6000-10000 pay off only on narrow rows and small sheets (the actor still shrinks a batch that would exceed maxPayloadMb); 1000-2000 is safest for wide rows and very large files. In API inputs pass the value as a string, e.g. "4000".

## `maxPayloadMb` (type: `number`):

Soft cap on the size of one Sheets API write request. 2 MB is the speed optimum (Google's own recommendation): a single 10 MB request is accepted (HTTP 200) but processed about 50x slower per cell, so a bigger cap slows big writes down.

## `requestsPerMinute` (type: `integer`):

Throttle applied independently to read requests and to write requests. 60 is Google's per-user limit (and the fastest setting); lower it if other tools share the same Google account.

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

Retries for retryable Google errors (429, 5xx, network) with exponential backoff and jitter.

## `autoSplit` (type: `string`):

off fails with the exact number of rows that still fit; sheets adds tabs to the same spreadsheet; spreadsheets adds tabs and then brand-new spreadsheets. Order of rows is always preserved.

## `maxCellsPerSpreadsheet` (type: `integer`):

Cell budget for the spreadsheet. Google's documented limit is 20,000,000 cells (or 100 MB per file); spreadsheets created before the limit increase may still be capped at 5-10 million. If the sheet refuses the write, the actor detects the cell-limit error and applies autoSplit.

## `maxCellsPerSheet` (type: `integer`):

Soft target used when auto-splitting: a new tab is started once a tab reaches this size. Keeps every tab fast to open and edit.

## `safetyMargin` (type: `integer`):

Reserved headroom below the spreadsheet cell limit, in case someone edits the file while the actor runs.

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

Validate credentials, read the dataset and compute the plan (rows, columns, splits, target ranges) without writing anything.

## `continueOnChunkError` (type: `boolean`):

When on, a failed batch is reported and the run continues with the next one; when off the run fails immediately.

## `resume` (type: `boolean`):

Continue from the last successfully written batch if the same dataset, spreadsheet, sheet and key field are used. Prevents duplicates on retries.

## Actor input object example

```json
{
  "mode": "append",
  "sheetName": "Sheet1",
  "createSheetIfMissing": false,
  "maxItems": 0,
  "unknownColumns": "append",
  "writeHeader": "once",
  "keyNormalization": "trim",
  "valueInputOption": "USER_ENTERED",
  "escapeFormulas": true,
  "addMissingColumns": true,
  "chunkSize": "4000",
  "maxPayloadMb": 2,
  "requestsPerMinute": 60,
  "maxRetries": 10,
  "autoSplit": "off",
  "maxCellsPerSpreadsheet": 20000000,
  "maxCellsPerSheet": 5000000,
  "safetyMargin": 100000,
  "dryRun": false,
  "continueOnChunkError": true,
  "resume": true
}
```

# Actor output Schema

## `syncReport` (type: `string`):

One JSON item per run: status, mode, totals (read, written, updated, skipped duplicates, errors), created sheets and spreadsheets, warnings and errors.

## `syncReportRecord` (type: `string`):

The same run report as a single key-value store record named OUTPUT: convenient for webhooks and for reading the report as one JSON document.

# 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 = {};

// Run the Actor and wait for it to finish
const run = await client.actor("assured_hippeastrum/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 = {}

# Run the Actor and wait for it to finish
run = client.actor("assured_hippeastrum/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 '{}' |
apify call assured_hippeastrum/google-sheets-sync --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,assured_hippeastrum/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/pAdus1c0S8zJx083Y/builds/wj2TS4Wbi2R70CqKh/openapi.json