# Google Sheets Sync (Reliable) (`loris-barce/google-sheets-sync`) Actor

Sync Apify datasets with Google Sheets: append, upsert, replace or export. Batched writes, automatic retries, deduplication and clear error messages.

- **URL**: https://apify.com/loris-barce/google-sheets-sync.md
- **Developed by:** [Loris Barce](https://apify.com/loris-barce) (community)
- **Security:** Limited permissions
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $1.00 / 1,000 row writtens

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 (Reliable)

Send any Apify dataset to a Google Sheet, or pull a Google Sheet into a dataset. Pick **append**, **upsert**, **replace** or **export**.

It's built to finish: writes go in batches, transient Google errors are retried automatically, duplicates are skipped, and when something is wrong the error tells you how to fix it.

- **Append** new rows at the bottom of a tab
- **Upsert**: update rows whose key matches (e.g. `url` or `id`) and append the rest
- **Replace** the whole tab with fresh data
- **Export** a tab to an Apify dataset (header row → JSON keys)
- **Deduplication** on by default: running the same sync twice writes nothing the second time
- **Nested JSON flattened** into columns (`address.city`), arrays joined with `; `
- **Dry run** to check access, columns and limits without writing anything
- **Pay only for rows actually written**, never for retries or failed rows

### Quick start

1. Set up a Google service account (5 steps, below). You only do this once.
2. Share your sheet with the service account email as **Editor**.
3. Paste the sheet URL and the service account JSON into the input, select your dataset, and click **Start**.

> **No credentials yet?** Run the Actor with the example input anyway. It starts in **demo mode**: it shows you, in the `OUTPUT` record, exactly which header and rows it would write. Nothing is written and no rows are charged (only the tiny $0.00005 start fee, see Pricing).

### Set up a Google service account (5 steps)

A service account is a "robot" Google user that the Actor signs in as. It can only open sheets you explicitly share with it.

![Set up in 5 steps: enable the API, create a service account, download a JSON key, share your sheet, paste and run](https://api.apify.com/v2/key-value-stores/Bf4U6oOS4ZajGP76V/records/setup-steps.png)

1. **Create a project and enable the API.** In [Google Cloud Console](https://console.cloud.google.com/), create a project (or pick one), then open [Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com) and click **Enable**.
2. **Create the service account.** Go to **IAM & Admin → Service Accounts → Create service account**. Any name works. You can skip the optional roles.
3. **Download a JSON key.** Open the service account, then **Keys → Add key → Create new key → JSON**. A `.json` file downloads. Keep it private.
4. **Share your sheet.** Open the Google Sheet, click **Share**, paste the service account email (the `client_email` value in the JSON, like `name@project.iam.gserviceaccount.com`), choose **Editor**, and click **Send**.
5. **Paste the key into the Actor.** Copy the whole content of the JSON file into **Google service account JSON**. The field is stored encrypted and never appears in logs.

For **export** only, the sheet can be shared as **Viewer**.

### Input examples

#### Append a dataset to a sheet

```json
{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
  "sheetName": "Leads",
  "mode": "append",
  "datasetId": "YOUR_DATASET_ID",
  "serviceAccountJson": "{ ...paste the JSON key... }"
}
```

#### Upsert by key (update existing rows, add new ones)

```json
{
  "spreadsheetId": "1AbC...",
  "mode": "upsert",
  "datasetId": "YOUR_DATASET_ID",
  "keyColumns": ["url"],
  "serviceAccountJson": "{ ... }"
}
```

Rows whose `url` is already in the sheet are updated in place. Only the columns present in your data are changed, so formulas and notes in other columns stay as they are. New URLs are appended.

#### Replace the content of a tab

```json
{
  "spreadsheetId": "1AbC...",
  "sheetName": "Daily snapshot",
  "mode": "replace",
  "datasetId": "YOUR_DATASET_ID",
  "columns": ["title", "price", "url"],
  "serviceAccountJson": "{ ... }"
}
```

`columns` is optional. It keeps only these columns, in this order.

#### Export a sheet to a dataset

```json
{
  "spreadsheetId": "1AbC...",
  "sheetName": "Products",
  "mode": "export",
  "serviceAccountJson": "{ ... }"
}
```

Each row becomes one dataset item, with the header row as keys.

#### Write rows pasted as JSON (no dataset)

```json
{
  "spreadsheetId": "1AbC...",
  "rawRows": [
    { "id": 1, "name": "Alice", "address": { "city": "Rome" }, "tags": ["vip", "newsletter"] }
  ],
  "serviceAccountJson": "{ ... }"
}
```

This writes the columns `id | name | address.city | tags` and the row `1 | Alice | Rome | vip; newsletter`.

#### Input reference

| Field | Default | Description |
|---|---|---|
| `spreadsheetId` | required | Sheet ID or full URL |
| `sheetName` | first tab | Tab to use. Created if missing (except for export) |
| `mode` | `append` | `append`, `upsert`, `replace`, `export` |
| `datasetId` | | Dataset to write. Takes precedence over `rawRows` |
| `rawRows` | | JSON array of objects, used when no dataset is selected |
| `keyColumns` | | Required for upsert. With dedup, a row is a duplicate when its key already exists |
| `deduplicate` | `true` | Skip rows already present in the sheet (and repeated rows in the input) |
| `columns` | all | Whitelist and order of columns |
| `flattenNested` | `true` | `a.b.c` columns and `; `-joined arrays. When off, nested values are written as JSON text |
| `serviceAccountJson` | | Service account key (secret). Empty = demo mode |
| `dryRun` | `false` | Validate and report, without writing |

### Output

Each run stores a summary in the `OUTPUT` record of its key-value store:

```json
{
  "rowsWritten": 800,
  "rowsUpdated": 200,
  "rowsSkippedDuplicates": 0,
  "rowsFailed": 0,
  "sheetUrl": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
  "durationSec": 6.4,
  "mode": "upsert",
  "dryRun": false,
  "spendLimitReached": false
}
```

- In **export** mode, `rowsWritten` is the number of rows exported.
- If some rows could not be written, `failedRowsDatasetId` points to a **`failed-rows`** dataset. Each item there holds the original row and the reason (see FAQ).
- If the run stops on a configuration error, `error` contains the message.

### Pricing

This Actor uses **pay per event**. You pay only for rows that Google confirmed as written:

| Event | When it is charged | Price |
|---|---|---|
| `row-written` | A row appended or updated in the sheet | $0.001 per row ($1 per 1,000 rows) |
| `row-exported` | A row exported from the sheet to the dataset | $0.0005 per row ($0.50 per 1,000 rows) |
| `apify-actor-start` | Once per run | $0.00005 per run (5 cents per 1,000 runs) |

Platform usage (compute) is included: you pay nothing else. Besides the start fee, you don't pay for skipped duplicates, failed rows, retries, dry runs or demo mode. If you set a **maximum cost per run**, the Actor writes as many rows as fit in it, then stops cleanly with status SUCCEEDED and `spendLimitReached: true`. The run is never cut off in the middle of a batch.

### FAQ: common errors

**"The service account cannot access this spreadsheet. Share the spreadsheet with … as Editor"**
The sheet is not shared with the service account. Click **Share** in the sheet and add the email shown in the message as **Editor**.

**"The service account can open the spreadsheet but is not allowed to edit it"**
The sheet is shared as Viewer. Change the service account's access to **Editor**. Nothing was written.

**"The Google Sheets API is not enabled for the Google Cloud project…"**
Open the link in the message, click **Enable**, wait a minute or two and run again.

**"Invalid service account JSON: …"**
Paste the entire downloaded `.json` file, including the curly braces. OAuth client IDs and API keys won't work: it has to be a *service account* key (`"type": "service_account"`). If Google rejects the key, it may have been deleted. Create a new one.

**"Spreadsheet … was not found"**
Check the URL or ID. Copy it straight from the browser's address bar.

**"This file is not a native Google Sheet"**
The file is an uploaded Excel or CSV. In Google Sheets choose **File → Save as Google Sheets** and use the new URL.

**"This write would grow the spreadsheet to about … cells"**
Google allows at most 10,000,000 cells per spreadsheet, counting all tabs. Limit the columns with `columns`, write fewer rows, or use a new spreadsheet. This check runs before anything is written.

**"Key column(s) … were not found in the data"**
`keyColumns` must match column names in your data. Nested fields use dot notation, e.g. `address.city`.

**Some rows are in `failed-rows`. What happened?**
Google rejected that batch even after retries; the most common cause is a cell over Google's 50,000 character limit. The rest of the data was still written. Each item in `failed-rows` shows the original row and the reason. Fix the data and run again: with deduplication on, rows already in the sheet are skipped. Rows marked `"possiblyWritten": true` hit a network error where Google's answer was lost. Check the sheet for them. They were not charged.

**I ran it without credentials and nothing was written**
That's demo mode. Add the service account JSON to write for real.

### Why this instead of other Sheets actors

- **Built to finish the job.** Writes go in batches of up to 500 rows, requests are paced to stay under Google's per-minute quota, and rate-limit or server errors are retried with exponential backoff (up to 6 attempts). Retries of an append can never duplicate rows.
- **One bad batch doesn't sink the run.** Rows Google rejects go to a `failed-rows` dataset with the reason. Everything else is still written, and the run ends with a clear summary.
- **Real upsert.** Matching rows are updated in place by key and new ones appended, without touching your formulas or other columns.
- **Dedup by default.** Scheduled runs don't pile up duplicate rows.
- **Errors you can act on.** Each error says what is wrong and how to fix it, with the exact email to share the sheet with. Everything is checked before the first write.
- **Fair billing.** You're charged only for rows Google confirmed, and your spending limit is respected to the row.
- **Minimal access.** The Actor only gets read access to the dataset you select, and it reaches only the sheets you share with your service account.

### In italiano (breve)

**Google Sheets Sync (Reliable)** copia un dataset Apify in un Google Sheet (modalità `append`, `upsert`, `replace`) oppure esporta un foglio in un dataset (`export`). Scrive a blocchi, ritenta da solo gli errori temporanei di Google e salta i duplicati. Se qualcosa non va, l'errore dice cosa fare.

**Configurazione in 5 passi:** (1) in Google Cloud Console abilita la *Google Sheets API*; (2) crea un *service account*; (3) scarica una chiave JSON; (4) condividi il foglio con l'email del service account (`client_email`) come **Editor**; (5) incolla il JSON nel campo *Google service account JSON*.

**Prezzo:** paghi solo le righe scritte davvero (`row-written`, $0,001 a riga) o esportate (`row-exported`, $0,0005 a riga), più $0,00005 per ogni avvio. L'uso della piattaforma è incluso. Duplicati saltati, righe fallite, dry run e modalità demo non costano nulla oltre all'avvio. Se imposti un limite di spesa, l'Actor si ferma in modo pulito quando lo raggiunge.

**Senza credenziali** l'Actor gira in modalità demo: nel record `OUTPUT` mostra un'anteprima di cosa scriverebbe, senza scrivere nulla e senza addebitare righe.

# Actor input Schema

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

The ID of the Google Sheet, or simply paste its full URL (e.g. https://docs.google.com/spreadsheets/d/<ID>/edit). The sheet must be shared with your service account email as Editor.

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

Name of the tab to read from or write to. Defaults to the first tab. If the tab does not exist, it is created.

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

<b>append</b>: add rows at the bottom.<br><b>upsert</b>: update rows whose key columns match, append the rest (requires Key columns).<br><b>replace</b>: clear the tab and write the data.<br><b>export</b>: read the tab and save each row to the default dataset.

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

The Apify dataset to write to the sheet (append / upsert / replace). Takes precedence over Raw rows. The Actor only gets read access to this dataset.

## `rawRows` (type: `array`):

Alternative to a dataset: a JSON array of objects. Used only when no dataset is selected. The example rows show what a run does: without a service account the Actor runs in demo mode and only previews the result.

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

Columns that uniquely identify a row (e.g. "url" or "id"). Required for upsert. Also used by deduplication: when set, a row is a duplicate if its key already exists.

## `deduplicate` (type: `boolean`):

Skip rows that are already present in the sheet (identical rows, or same key if Key columns are set).

## `columns` (type: `array`):

Optional. Only these columns are written/exported, in this order. Use flattened names for nested fields, e.g. "address.city".

## `flattenNested` (type: `boolean`):

Turn nested objects into columns like "a.b.c" and join arrays with "; ". When off, nested values are written as JSON text.

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

Paste the full JSON key of a Google Cloud service account with the Google Sheets API enabled. Stored encrypted and never logged. Leave empty to run in demo mode (preview only, nothing written, no rows charged). See the README for a 5-step setup.

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

Validate everything (access, data, columns, limits) and report what would happen, without writing anything.

## Actor input object example

```json
{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/PASTE_YOUR_SHEET_ID_HERE/edit",
  "mode": "append",
  "rawRows": [
    {
      "id": 1,
      "name": "Alice",
      "address": {
        "city": "Rome",
        "zip": "00100"
      },
      "tags": [
        "vip",
        "newsletter"
      ]
    },
    {
      "id": 2,
      "name": "Bob",
      "address": {
        "city": "Milan",
        "zip": "20100"
      },
      "tags": []
    },
    {
      "id": 2,
      "name": "Bob",
      "address": {
        "city": "Milan",
        "zip": "20100"
      },
      "tags": []
    }
  ],
  "deduplicate": true,
  "flattenNested": true,
  "dryRun": false
}
```

# Actor output Schema

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

Rows written, updated, skipped as duplicates and failed, plus the sheet URL (the OUTPUT record). In demo mode it also holds the preview and setup steps.

## `exportedRows` (type: `string`):

Export mode only: one item per sheet row, header row as keys.

# 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 = {
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/PASTE_YOUR_SHEET_ID_HERE/edit",
    "rawRows": [
        {
            "id": 1,
            "name": "Alice",
            "address": {
                "city": "Rome",
                "zip": "00100"
            },
            "tags": [
                "vip",
                "newsletter"
            ]
        },
        {
            "id": 2,
            "name": "Bob",
            "address": {
                "city": "Milan",
                "zip": "20100"
            },
            "tags": []
        },
        {
            "id": 2,
            "name": "Bob",
            "address": {
                "city": "Milan",
                "zip": "20100"
            },
            "tags": []
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("loris-barce/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 = {
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/PASTE_YOUR_SHEET_ID_HERE/edit",
    "rawRows": [
        {
            "id": 1,
            "name": "Alice",
            "address": {
                "city": "Rome",
                "zip": "00100",
            },
            "tags": [
                "vip",
                "newsletter",
            ],
        },
        {
            "id": 2,
            "name": "Bob",
            "address": {
                "city": "Milan",
                "zip": "20100",
            },
            "tags": [],
        },
        {
            "id": 2,
            "name": "Bob",
            "address": {
                "city": "Milan",
                "zip": "20100",
            },
            "tags": [],
        },
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("loris-barce/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 '{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/PASTE_YOUR_SHEET_ID_HERE/edit",
  "rawRows": [
    {
      "id": 1,
      "name": "Alice",
      "address": {
        "city": "Rome",
        "zip": "00100"
      },
      "tags": [
        "vip",
        "newsletter"
      ]
    },
    {
      "id": 2,
      "name": "Bob",
      "address": {
        "city": "Milan",
        "zip": "20100"
      },
      "tags": []
    },
    {
      "id": 2,
      "name": "Bob",
      "address": {
        "city": "Milan",
        "zip": "20100"
      },
      "tags": []
    }
  ]
}' |
apify call loris-barce/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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