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

Writes any Apify dataset, Actor run output or JSON rows to a Google Sheet and returns a sync summary (rows appended, updated, unchanged). Modes: append, replace, or upsert by key column. Auto headers, nested-field flattening, quota retries, service-account auth. $0.50 per 1,000 rows written.

- **URL**: https://apify.com/brasspage/google-sheets-sync.md
- **Developed by:** [Brasspage](https://apify.com/brasspage) (community)
- **Categories:** Integrations, Automation, Developer tools
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.50 / 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

### Export Apify datasets to Google Sheets: append, replace or upsert

Send the results of any Apify Actor to a Google Sheet. Use it after a scraper run, on a schedule, or as an **integration** that runs by itself each time another Actor finishes.

- **Three modes:** `append` new rows, `replace` the whole tab, or `upsert` (update the rows whose key matches, add the new ones).
- **Automatic header row** from your data. New fields become new columns at the end. Your column order stays the same.
- **Nested fields flattened:** `{"price": {"value": 5}}` becomes a column `price/value`, the same style as the Apify CSV export.
- **Reliable writes:** large exports go in chunks, and Google quota errors (429) and server errors retry with exponential backoff.
- **Service-account login:** no OAuth pop-up, no tokens that expire after 7 days, and it works from the API, schedules, and AI agents.
- **Clear error messages:** each auth or sharing problem tells you exactly what to fix.
- **You pay only for rows written.** Rows that did not change in upsert mode are free.

### What you get

- **Your rows in the Google Sheet tab you name**, with a header row made from your data. If the tab does not exist, the Actor adds it.
- **One summary row per run:** the sheet URL, rows read, appended, updated, unchanged and skipped, and the columns written.
- **Errors that say what to fix** for login and sharing problems. Google quota errors retry by themselves.
- **$0.50 per 1,000 rows written.** Unchanged rows in upsert mode are free.

A run needs your own Google key and sheet, so a one-click run with no input only returns a setup note (it costs only the $0.005 start fee). Setup takes about 5 minutes, see below.

### Quick start

1. Make a free Google service-account key and share your sheet with its email ([5 steps below](#setup-in-5-minutes)).
2. Paste the key, the sheet URL and a dataset ID, then click **Start**.
3. To export every run of a scraper, add this Actor on the scraper's **Integrations** tab with Dataset ID `{{resource.defaultDatasetId}}`.

### What makes it reliable

Exports to Google Sheets usually fail for the same few reasons. This Actor handles each one:

| Problem                                                               | What this Actor does                                                                  |
| --------------------------------------------------------------------- | ------------------------------------------------------------------------------------- |
| The scraper returned 0 items                                          | The run **succeeds** and the sheet is not touched. Your integration does not break.   |
| Google quota error `429` or `500/503`, `ECONNRESET`                   | Retries up to 8 times with exponential backoff (up to about 4 minutes).               |
| OAuth token expired (`invalid_grant`)                                 | Service-account keys do not expire.                                                   |
| The sheet is too small for the new rows or columns                    | The tab grows first, then the data is written.                                        |
| Large datasets time out                                               | Writes go in chunks of about 2 MB. Each chunk has an exact range, so a retry is safe. |
| Text longer than 50,000 characters (the Google cell limit)            | The text is cut to 50,000 characters and the run tells you.                           |
| The 10,000,000-cell limit                                             | Checked **before** writing, with a clear message.                                     |
| `"00123"` loses its zeros, `"1,5"` becomes a date, `=` runs a formula | Default `RAW` mode writes values exactly as they are.                                 |
| Append mode rewrites the whole sheet                                  | Append writes only the new rows. Upsert writes only the cells that changed.           |
| Tab name `Sheet1` matches `Sheet10`                                   | Exact tab-name match. If the tab does not exist, it is created.                       |

### Setup in 5 minutes

You need a free Google Cloud service account. You make it once and use it for all your sheets.

#### 1. Create a Google Cloud project

1. Go to [console.cloud.google.com/projectcreate](https://console.cloud.google.com/projectcreate).
2. Type a name, for example `apify-sheets`, and click **Create**. You do not need billing.

#### 2. Enable the Google Sheets API

1. Go to [console.cloud.google.com/apis/library/sheets.googleapis.com](https://console.cloud.google.com/apis/library/sheets.googleapis.com).
2. Make sure your new project is selected at the top, then click **Enable**.

#### 3. Create a service account and a JSON key

1. Go to **IAM & Admin > Service Accounts** ([direct link](https://console.cloud.google.com/iam-admin/serviceaccounts)).
2. Click **Create service account**. Type a name, for example `sheets-writer`. Click **Done**. It needs no roles.
3. Click the new service account, open the **Keys** tab, and click **Add key > Create new key > JSON > Create**.
4. Your browser downloads a `.json` file. Keep it safe: it is a password.

If your Google Workspace blocks key creation (the policy `iam.disableServiceAccountKeyCreation`), ask your admin to allow it for this project, or use a personal Google account.

#### 4. Share your spreadsheet with the service account

1. Open the `.json` file and copy the `client_email` value. It looks like `sheets-writer@apify-sheets.iam.gserviceaccount.com`.
2. Open your Google Sheet, click **Share**, paste that email, select **Editor**, and click **Send**. (Untick "Notify people".)

#### 5. Run the Actor

1. Paste the **whole** content of the `.json` file into **Google service-account key (JSON)**. Apify stores it encrypted.
2. Paste your spreadsheet URL into **Spreadsheet ID or URL**.
3. Give a data source (a dataset ID or raw JSON rows), choose a mode, and click **Start**.

### Run it automatically after another Actor (integration)

1. Open the Actor that makes your data (for example, a scraper) and go to its **Integrations** tab.
2. Click **Add integration**, select **Apify Actor**, and choose **Google Sheets Sync**.
3. Fill in the spreadsheet, the key JSON, and the mode.
4. In **Dataset ID**, type `{{resource.defaultDatasetId}}`. Apify replaces it with the dataset of the run that just finished.
5. Save. Each time the scraper run succeeds, this Actor writes that run's data to your sheet.

To export the data of one run by hand, open the run, go to its **Storage** tab, and copy the dataset ID into **Dataset ID**.

### Input example

```json
{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbCdEf.../edit",
  "sheetName": "Products",
  "mode": "upsert",
  "keyFields": ["url"],
  "datasetId": "aBcD1234eFgH5678i",
  "columns": ["url", "title", "price/value", "price/currency", "inStock"],
  "flattenNested": true,
  "serviceAccountJson": "{ \"type\": \"service_account\", ... }"
}
```

Run it from the API:

```bash
curl -X POST "https://api.apify.com/v2/acts/brasspage~google-sheets-sync/runs?token=$APIFY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"spreadsheetId":"1AbCdEf...","mode":"append","rows":[{"name":"A","price":10}],"serviceAccountJson":"..."}'
```

### Input fields

| Field                                                     | What it does                                                                                              |
| --------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- |
| `spreadsheetId`                                           | The spreadsheet URL or ID.                                                                                |
| `sheetName`                                               | The tab name. Empty = the first tab. A missing tab is created.                                            |
| `mode`                                                    | `append` (default), `replace`, or `upsert`.                                                               |
| `keyFields`                                               | Field names that identify a row, for example `["url"]` or `["store", "sku"]`. Upsert needs them.          |
| `serviceAccountJson`                                      | The service-account key file (secret).                                                                    |
| `datasetId`                                               | The dataset to export.                                                                                    |
| `rows`                                                    | Raw JSON: an array of objects.                                                                            |
| `columns`                                                 | Pick and order the fields to write. Empty = all fields.                                                   |
| `flattenNested`                                           | `true` (default): nested fields become `a/b` columns. `false`: nested values are written as JSON text.    |
| `valueInputOption`                                        | `RAW` (default) writes values as they are. `USER_ENTERED` lets Sheets parse dates, numbers, and formulas. |
| `oauthClientId`, `oauthClientSecret`, `oauthRefreshToken` | Optional OAuth login instead of a service account (see the FAQ).                                          |

If you give both, `rows` wins over `datasetId`.

### Modes

**append** adds the rows after the last row of the tab. The first run writes the header row. If you also set `keyFields`, rows whose key is already in the sheet are skipped (dedupe).

**replace** writes the header and all rows from the top of the tab, then clears the old rows and columns below and to the right. The tab is never empty during the run. If the source is empty, the tab is **not** cleared.

**upsert** finds rows by `keyFields`. If a key is already in the sheet, only the cells that changed are written. If a key is new, the row is added at the end. Columns you added by hand (for example "Notes" or "Status") and formulas in them stay as they are. If the same key appears more than once in the input, the items are merged and the last value wins.

In all modes, new fields become new columns at the end of the header. Existing columns keep their order.

### Output

The Actor writes one summary item to its dataset (and to the `OUTPUT` record):

```json
{
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
  "spreadsheetTitle": "Price tracker",
  "sheetName": "Products",
  "mode": "upsert",
  "source": "dataset aBcD1234eFgH5678i",
  "rowsRead": 1200,
  "rowsAppended": 35,
  "rowsUpdated": 112,
  "rowsUnchanged": 1053,
  "rowsSkippedDuplicate": 0,
  "rowsSkippedNoKey": 0,
  "rowsWritten": 147,
  "columns": ["url", "title", "price/value", "price/currency", "inStock"],
  "durationMs": 4210,
  "errors": []
}
```

`errors` lists warnings, for example long text that was cut. A real error fails the run, and the run's status message says what to fix.

### Pricing

Pay per event. You pay only for what is written:

| Event       | Price           |
| ----------- | --------------- |
| Actor start | $0.005 per run  |
| Row written | $0.0005 per row |

Examples: 100 new rows = $0.055. A daily upsert of 2,000 products where 150 changed = $0.08. Rows that did not change, duplicates that were skipped, and the header row are free. Set a maximum cost per run in the run options to cap the cost: the Actor writes only as many rows as that limit allows and tells you.

### Limits

- Google Sheets allows 10,000,000 cells per spreadsheet, 18,278 columns per tab, and 50,000 characters per cell.
- Google allows about 60 write requests per minute per service account. The Actor writes up to about 2 MB per request, so this is rarely reached. When it is, the Actor waits and retries.
- The Actor reads the dataset and the current tab into memory. The default is 512 MB. If a run on a very large sheet runs out of memory, set the run memory to 1–2 GB.
- In `append` and `upsert` modes, row 1 of the tab is the header.

### FAQ

**Why a service account instead of "Sign in with Google"?**
A service account is a robot Google user that belongs to you. Its key does not expire, so schedules and integrations keep working. OAuth tokens for apps in "Testing" status expire after 7 days, which is the most common reason Google Sheets exports stop working.

**Is my key safe?**
The key field is a secret input: Apify stores it encrypted and does not show it in logs. The key can only open the spreadsheets that you share with its email. You can delete the key in Google Cloud at any time.

**Can I use OAuth instead?**
Yes. Create an OAuth client (type "Desktop app") in your own Google Cloud project, get a refresh token with the scope `https://www.googleapis.com/auth/spreadsheets` (for example with the [OAuth 2.0 Playground](https://developers.google.com/oauthplayground) and "Use your own OAuth credentials"), and fill in `oauthClientId`, `oauthClientSecret`, and `oauthRefreshToken`. Publish the OAuth app to "In production", or the token expires after 7 days.

**Can I write to an Excel file in Google Drive?**
No. Open the file and use **File > Save as Google Sheets**, then use the new spreadsheet.

**How do I write nested data?**
Leave `flattenNested` on. Use `columns` to pick only the fields you need, for example `["title", "price/value", "images/0"]`.

**Can an AI agent use it?**
Yes. To give an AI agent this one tool, connect it to the Apify MCP server at `https://mcp.apify.com?tools=brasspage/google-sheets-sync`. The agent passes `rows` and the sheet, and gets the sync summary back.

**Does it read data from Google Sheets?**
No. This Actor only writes to Google Sheets.

### Troubleshooting

| Message starts with                                   | Fix                                                                                               |
| ----------------------------------------------------- | ------------------------------------------------------------------------------------------------- |
| `AUTH: No access to the spreadsheet`                  | Share the sheet with the `client_email` of your key as **Editor**.                                |
| `AUTH: The Google Sheets API is turned off`           | Open the link in the message and click **Enable**. Wait 1–2 minutes.                              |
| `AUTH: "serviceAccountJson" is not valid JSON`        | Paste the whole key file, from the first `{` to the last `}`.                                     |
| `AUTH: This JSON is an OAuth client file`             | You pasted the wrong file. Create a key under **Service Accounts > Keys**.                        |
| `AUTH: Google rejected the service-account key`       | The key was deleted. Create a new key and paste it.                                               |
| `AUTH: The OAuth refresh token is expired or revoked` | Make a new refresh token, and publish your OAuth app to "In production".                          |
| `Spreadsheet "..." was not found`                     | Check the ID in the URL, and share the sheet with the service account.                            |
| `This file is an Excel file`                          | Use **File > Save as Google Sheets**.                                                             |
| `Key field "x" is not in the data`                    | Check the spelling in `keyFields`. Nested fields use a slash: `product/id`.                       |
| `This run cannot read dataset`                        | Put the dataset ID into **Dataset ID**. In an integration, use `{{resource.defaultDatasetId}}`.   |
| `No data source`                                      | Set `datasetId` or `rows`. In an integration, set `datasetId` to `{{resource.defaultDatasetId}}`. |

Found a bug or need a feature? Open an issue on the **Issues** tab.

### Related Actors

Other tools from the same developer, with the same pay-per-result pricing:

- [ATS Jobs API](https://apify.com/brasspage/careers-site-jobs-api): every open job from Greenhouse, Lever, Ashby, SmartRecruiters, Workable, Recruitee and Personio boards, with new-job alerts.
- [Podcast & Audio Transcriber](https://apify.com/brasspage/media-transcriber): podcast episodes and audio or video files to text, SRT and VTT for $0.004 per minute.
- [Website Contacts & Tech Stack](https://apify.com/brasspage/website-contact-tech-audit): public emails, phones, social links, CMS and pixels for a list of websites.
- [PDF OCR to Markdown](https://apify.com/brasspage/pdf-ocr-to-markdown): scanned PDFs and images to clean Markdown with tables, for $0.006 per page.

# Actor input Schema

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

The Google Sheet to write to. Paste the full URL or only the ID (the part between /d/ and /edit). Share this sheet with your service-account email as Editor.

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

The tab to write to. If the tab does not exist, the Actor creates it. Empty = the first tab.

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

append adds new rows. replace overwrites the tab. upsert updates the rows whose key columns match and adds the others (needs Key columns).

## `keyFields` (type: `array`):

One or more field names that identify a row, for example "url" or "sku". Upsert needs them. In append mode they skip rows whose key is already in the sheet. Nested fields use a slash, for example "product/id".

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

Paste the whole JSON key file of a Google Cloud service account. Then share the spreadsheet with the "client_email" from this file as Editor. The value is stored encrypted.

## `oauthClientId` (type: `string`):

Only if you do not use a service account. Client ID of your own Google OAuth client.

## `oauthClientSecret` (type: `string`):

Client secret of the same OAuth client.

## `oauthRefreshToken` (type: `string`):

Refresh token with the scope https://www.googleapis.com/auth/spreadsheets. Tokens of OAuth apps in "Testing" status expire after 7 days.

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

The Apify dataset to export, for example the default dataset of a scraper run (Storage > Datasets, or the run's Storage tab). In an integration, set it to {{resource.defaultDatasetId}}.

## `rows` (type: `array`):

An array of objects to write, for example \[{"name": "A", "price": 10}]. Use this when the data does not come from a dataset.

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

Pick and order the fields to write. Empty = all fields. Nested fields use a slash, for example "price/value" or "images/0".

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

On: {"price": {"value": 5}} becomes a column "price/value". Off: nested objects are written as JSON text in one cell.

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

RAW keeps text as text, so "00123" keeps its zeros and a value that starts with "=" is not run as a formula. USER_ENTERED makes Sheets parse values as if you typed them.

## Actor input object example

```json
{
  "mode": "append",
  "flattenNested": true,
  "valueInputOption": "RAW"
}
```

# Actor output Schema

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

One item per run: spreadsheet URL, sheet name, mode, rows read, appended, updated, unchanged, duplicates skipped, rows written (billed), duration and warnings.

# 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("brasspage/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("brasspage/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 brasspage/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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