# Google Sheets Sync: Import, Export, Append & Upsert (`rationalistic_canoe/google-sheets-sync`) Actor

Send any Apify dataset to Google Sheets, or pull a sheet into a dataset. Append, append-only-new, upsert or replace, with retries, batching and plain-English errors. Service account, no OAuth. Drop-in for old Google Sheets actor inputs.

- **URL**: https://apify.com/rationalistic_canoe/google-sheets-sync.md
- **Developed by:** [VISHAL RAWAT](https://apify.com/rationalistic_canoe) (community)
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.03 / 1,000 row written to google sheets

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

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

## 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: Import, Export, Append & Upsert

**Move any Apify dataset into Google Sheets, or bring a Google Sheet into a dataset. It retries automatically, handles big sheets in batches, and explains errors in plain English. You don't need an OAuth app, and there are no tokens that expire every 7 days.**

**Try it free:** Apify's free plan gives $5/month of usage, no card needed. That's roughly 150,000 rows written a month at $0.03 per 1,000 rows (plus $0.002 per sync run).

- ✅ **Export:** dataset → sheet. Four write modes: **Append**, **Append only new**, **Upsert** (update rows by key) and **Replace**, which saves a backup of the old tab first.
- ✅ **Import:** sheet → dataset, as clean typed JSON.
- ✅ **No OAuth.** Use a Google **service account**, a Google **API key** (read-only), or **no credentials at all** for sheets shared as "Anyone with the link".
- ✅ **Built for reliability.** It retries Google's `429 quota`, `5xx` and dropped-connection errors with exponential backoff that respects `Retry-After`. It also throttles itself under Google's 60 requests/min quota.
- ✅ **Big sheets.** Writes go out in batches (5,000 rows per request, capped at about 4 MB). It checks Google's 10M-cell limit before writing, so you don't end up with half-written tabs.
- ✅ **Safe by default.** Values are written *raw*, so scraped text such as `=IMPORTXML(...)` can never turn into a live formula. Upsert never deletes columns or cells it doesn't own.
- ✅ **Dry run.** See exactly what would be appended, updated or skipped before anything is written.
- ✅ **Fair pricing.** You pay per row written or read. **Failed runs cost nothing.**

### Try it in 10 seconds

Click **Start** with the default input. It imports Google's public sample sheet ("Class Data", 30 students) in public-link mode, so you can see the output format before you set anything up.

### Switching from another Google Sheets actor? Keep your input

Tasks and integrations built for the OAuth-based [Google Sheets Import & Export](https://apify.com/lukaskrivka/google-sheets) actor run here unchanged. Add one field, `serviceAccountKey` (see Quick start), and share the sheet with the service account's email as Editor. Its field names are read as they are:

| Old field | What happens here |
|---|---|
| `mode`: `append` | Appends rows. With `deduplicateByField`, only rows whose value is not already in the sheet are added and existing rows are left alone. |
| `mode`: `replace` | Replaces the tab (deduplicated if you set a dedupe option). `createBackup` saves the old rows first. |
| `mode`: `modify` + `transformFunction` | Reads the tab, runs your `({ spreadsheetData, datasetData }) => rows` function (30-second limit) and writes the result back. |
| `mode`: `read` | Rows go to the dataset and, as before, to the `OUTPUT` record. The run summary is in `SUMMARY`. |
| `mode`: `load backup` + `backupStore` | Restores a backup saved by either actor (record `backup` or `BACKUP`). |
| `spreadsheetId`, `range`, `datasetId`, `rawData`, `limit`, `offset`, `columnsOrder`, `deduplicateByEquality`, `publicSpreadsheet` (read only) | Used as is. Integration payloads (`resource.defaultDatasetId`) work too. |
| `googleCredentials`, `tokensStore`, Google account | Not needed. If the input has no service-account key, the run stops with step-by-step setup instructions and costs nothing. |

A service account doesn't use browser sign-in, so there are no refresh tokens to expire or be revoked.

### Quick start: export a dataset to a sheet (3-minute setup, done once)

Google only allows writes from an authenticated identity. A **service account** is a robot Google account that never expires and needs no browser login.

1. Open <https://console.cloud.google.com/>, then create or pick a project.
2. Go to **APIs & Services → Library → "Google Sheets API" → Enable**.
3. Go to **IAM & Admin → Service accounts → Create service account**. Give it any name and skip the optional roles.
4. Open the service account and go to **Keys → Add key → Create new key → JSON**. A `.json` file downloads.
5. In your Google Sheet, click **Share**, paste the service account's email (`…@….iam.gserviceaccount.com`) and set it to **Editor**.
6. In this actor, set **Authentication = Service account** and paste the **whole JSON file** into *Service account key*. The field is a secret input and is stored encrypted by Apify.

That's all. The key keeps working until you delete it, so there are no `invalid_grant` failures a week later.

### Authentication modes

| Mode | Read | Write | Private sheets | Setup |
|---|---|---|---|---|
| **Service account** | ✅ | ✅ | ✅ (share with its email) | 3 min, one time |
| **API key** | ✅ | ❌ | ❌ (must be shared "Anyone with the link") | 1 min |
| **Public link** | ✅ | ❌ | ❌ (must be shared "Anyone with the link") or *File → Share → Publish to web* | none |

Public-link mode reads Google's own CSV export of the sheet, the same file you get from *File → Download → CSV*. Use it for sheets you're allowed to view. Google's API rules say that writing always needs authentication, and this actor follows them.

### Write modes (export)

| Mode | What happens | Needs key columns |
|---|---|---|
| **Append** | Adds the rows at the bottom of the tab. | no |
| **Append only new** | Adds only rows whose key (e.g. `url`) isn't in the tab yet. This is ideal for scheduled scrapers. | yes |
| **Upsert** | Updates rows whose key exists and appends the rest. Only the cells for exported fields change, so your own notes columns stay untouched. | yes |
| **Replace** | Clears the tab and writes the data. The old contents are saved to the key-value store as `BACKUP` first. | no |

Other export options:

- **Remove duplicates in input:** dedupes by the key columns.
- **Fields / omit fields / column order:** choose which fields are written and in what order.
- **Add new columns:** new fields get new columns on the right, and existing columns are never reordered.
- **Arrays:** written as `tags/0, tags/1…`, joined in one cell, or as JSON.
- **Create the tab if missing.**
- **Parse values like typed input:** off by default.

### Use it after another actor (integrations)

1. On your scraper's page, go to **Integrations → Add → Apify actor**, pick **Google Sheets Sync**, and choose the *Run succeeded* trigger.
2. Leave **Dataset ID** empty. The actor detects the finished run's dataset (`payload.resource.defaultDatasetId`) automatically.
3. Use **Append only new** with `keyColumns: ["url"]`. Every scheduled scrape then adds only the new results.

You can also call it from code:

```js
await Actor.call('USERNAME/google-sheets-sync', {
  mode: 'export', authMode: 'serviceAccount', serviceAccountKey: process.env.GOOGLE_SA_JSON,
  spreadsheet: 'https://docs.google.com/spreadsheets/d/…/edit#gid=0', sheetName: 'Leads',
  datasetId: run.defaultDatasetId, writeMode: 'upsert', keyColumns: ['url'],
});
```

### Output

- **Import:** one dataset item per row, keyed by the header row. Duplicate or empty headers become `Name`, `Name_2`, `column_3`.
- **Every run** writes an `OUTPUT` record (key-value store):

```json
{ "mode": "export", "writeMode": "upsert", "dryRun": false, "spreadsheet": "My book", "sheet": "Leads",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/…/edit#gid=0",
  "itemsRead": 1200, "duplicatesInInput": 3, "rowsAppended": 140, "rowsUpdated": 60, "rowsSkippedExisting": 997,
  "columnsAdded": 1, "batches": 2, "columns": 9, "header": ["url", "name", "…"], "warnings": [],
  "requests": 9, "retries": 1, "durationMs": 4120, "stoppedAtCostLimit": false }
```

### Errors you can act on

Each error tells you what to do next. For example:

- *"… → Open the sheet → Share → add the service account sync-bot@….iam.gserviceaccount.com as Editor → Send."*
- *"… → Enable it at https://console.cloud.google.com/apis/library/sheets.googleapis.com?project=… and run again."*
- *"Tab "Nope" not found. Tabs in this spreadsheet: "Class Data"."*
- *"Spreadsheet not found, or it is not shared publicly. → Share → General access → Anyone with the link → Viewer."*
- *"This write would take the spreadsheet past Google's 10,000,000-cell limit (≈… cells). Nothing from this batch was written."*

### Limits (Google's, not ours)

- 10,000,000 cells per spreadsheet, and 50,000 characters per cell. Longer values are truncated with a warning.
- 60 read and 60 write requests per minute per user. Each request carries up to 5,000 rows, so 100k rows take about 20 requests.
- Public-link mode reads whole tabs as CSV and returns formatted values. Use an API key or service account for raw numbers, formulas or A1 ranges.

### Pricing (pay per event)

| Event | Price |
|---|---|
| Successful run | $0.002 |
| Row written to a sheet | $0.03 per 1,000 rows |
| Row read from a sheet | $0.01 per 1,000 rows |

A daily scrape that appends 500 rows costs about **$0.017**. Importing a 10,000-row sheet costs about **$0.10**. Failed runs and dry runs aren't charged. Old-actor modes that rewrite the tab (`modify`, transforms, `deduplicateByEquality` in append mode) write every row, so every row counts as written; reading the tab for them is free. Set *Maximum cost per run* in the run options and the actor stops cleanly at that limit.

### FAQ

**Is my service-account key safe?** It's an Apify *secret* input, encrypted at rest and never logged. The actor only sends it to Google's token endpoint. Give the service account access only to the sheets it needs.

**Why not OAuth / "Sign in with Google"?** OAuth refresh tokens for unverified apps expire after 7 days, which is the main reason other Sheets actors fail. Service accounts don't expire.

**Can it write formulas?** Yes. Turn on *Parse values like typed input*. It's off by default for safety.

**Does Upsert delete rows that are missing from my data?** No. Upsert only adds and updates. Use Replace for a full mirror; it saves a backup first.

**My sheet has 200k rows. Will Append only new be slow?** It reads only the key column(s), one request per key column, then writes in batches of up to 5,000 rows.

**Can I import a tab by name in public mode?** Yes. Set *Tab name*; the actor looks up its ID from the public page.

# Actor input Schema

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

<b>Export</b>: write a dataset (or raw JSON) into a sheet. <b>Import</b>: read a sheet into this run's dataset. The default (import a public sample sheet) runs with no setup so you can see the output first. The "Old actor" modes accept the same fields as the OAuth-based Google Sheets actor, so existing tasks keep working once you add a service-account key.

## `spreadsheet` (type: `string`):

Paste the sheet's browser URL. If it contains <code>#gid=…</code> that tab is used.

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

Tab to read or write. Empty = the tab in the URL, or the first tab.

## `authMode` (type: `string`):

<b>Service account</b>: read and write, works with private sheets (share the sheet with the service account's email). <b>API key</b>: read-only, sheet must be shared as 'Anyone with the link'. <b>Public link</b>: read-only, no credentials at all.

## `serviceAccountKey` (type: `string`):

Paste the whole JSON key file. Stored encrypted. See the README for the 3-minute setup.

## `apiKey` (type: `string`):

Only for API-key mode. Enable the Google Sheets API for the key's project.

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

Dataset to export. When this actor runs as an integration after another actor, leave empty and the finished run's dataset is used.

## `rawData` (type: `array`):

Alternative to a dataset: a JSON array of objects, or an array of arrays with the header first.

## `writeMode` (type: `string`):

<b>Append</b>: add rows at the bottom. <b>Append only new</b>: skip rows whose key columns already exist in the sheet. <b>Upsert</b>: update rows with a matching key in place, add the rest. <b>Replace</b>: back up, clear the tab and write fresh.

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

Field(s) that identify a row, e.g. url or sku. Required for 'Append only new' and 'Upsert'; also used to remove duplicates from the input.

## `dedupeInput` (type: `boolean`):

Drop repeated items (by key columns, or identical rows if no key is set) before writing.

## `fields` (type: `array`):

Only export these fields (after flattening, e.g. address/city). Empty = all.

## `omitFields` (type: `array`):

Fields never written to the sheet.

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

Order for columns this actor creates. Existing sheet columns are never reordered.

## `addNewColumns` (type: `boolean`):

When the data has fields the sheet doesn't, add them at the right. Off = ignore them.

## `parseValues` (type: `boolean`):

Off (recommended for scraped data): every value is written exactly as-is, so text like '=IMPORTXML(…)' can never run as a formula. On: Google interprets values as if typed by a user.

## `flattenArrays` (type: `string`):

How to write lists: one column per item (tags/0, tags/1…), joined in one cell, or as JSON.

## `createSheet` (type: `boolean`):

When exporting to a tab name that does not exist yet, create it.

## `backupBeforeReplace` (type: `boolean`):

Saves the old tab contents to the key-value store record BACKUP before a Replace.

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

Shows what would be appended/updated/skipped without writing anything.

## `datasetOffset` (type: `integer`):

Start exporting from this item index.

## `datasetLimit` (type: `integer`):

0 = all.

## `range` (type: `string`):

Optional A1 range inside the tab, e.g. A1:F500. Empty = whole tab.

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

Row that holds the column names (usually 1).

## `valueRender` (type: `string`):

How cell values are returned when importing with a service account or API key.

## `inferTypes` (type: `boolean`):

For public-link mode (CSV): turn '42' into 42 and 'TRUE' into true. Codes with leading zeros stay text.

## `skipEmptyRows` (type: `boolean`):

Leave out rows where every cell is empty.

## `importLimit` (type: `integer`):

0 = all.

## `rowsPerRequest` (type: `integer`):

Batch size for writes (also capped at ~4 MB per request).

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

Google's default per-user quota is 60/min. Lower this if several runs write to the same sheet.

## `deduplicateByField` (type: `string`):

Rows with the same value in this field are kept once. In append mode only rows whose value is not already in the sheet are added.

## `deduplicateByEquality` (type: `boolean`):

Identical rows (same non-empty values) are kept once, across the sheet and the new data.

## `transformFunction` (type: `string`):

({ spreadsheetData, datasetData }) => rows. Its result replaces the tab. Runs with a 30-second limit; must return an array directly.

## `createBackup` (type: `boolean`):

Saves the current rows to the key-value store records "backup" and "BACKUP" before the tab is rewritten.

## `backupStore` (type: `string`):

Key-value store that holds the backup to restore.

## Actor input object example

```json
{
  "mode": "import",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "authMode": "public",
  "writeMode": "append",
  "dedupeInput": true,
  "addNewColumns": true,
  "parseValues": false,
  "flattenArrays": "expand",
  "createSheet": true,
  "backupBeforeReplace": true,
  "dryRun": false,
  "datasetOffset": 0,
  "datasetLimit": 0,
  "headerRow": 1,
  "valueRender": "UNFORMATTED_VALUE",
  "inferTypes": true,
  "skipEmptyRows": true,
  "importLimit": 0,
  "rowsPerRequest": 5000,
  "requestsPerMinute": 55
}
```

# Actor output Schema

## `summary` (type: `string`):

No description

## `rows` (type: `string`):

No description

## `backup` (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 = {
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
};

// Run the Actor and wait for it to finish
const run = await client.actor("rationalistic_canoe/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 = { "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0" }

# Run the Actor and wait for it to finish
run = client.actor("rationalistic_canoe/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 '{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
}' |
apify call rationalistic_canoe/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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