# Google Sheets Sync: Export Datasets & JSON to Sheets (`genesis.labs.co/google-sheets-sync`) Actor

Export any Apify dataset or JSON to Google Sheets reliably. Append, replace or dedupe/upsert rows by key, batch 100k+ rows, auto-retry Google errors, clear fixes in every error. Official Sheets API, your own service account.

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

## Pricing

from $0.07 / 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.
Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have.

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: Export Apify Datasets & JSON to Google Sheets

**Get scraper results into Google Sheets on every run, without losing rows or babysitting the export.** Append new rows, replace a tab, or dedupe and upsert by a key such as `url`, `id` or `email`. Tested live with 100,000 rows in one run, retries Google errors on its own, and every error message tells you how to fix it.

- ⚡ **Quick start:** paste a sheet URL, a dataset and a Google service account key, then run
- 🔁 **Append, replace, dedupe or upsert**, with new fields added as new columns
- 🧱 **Large datasets:** streamed in batches, rate-limited under Google's quota, checked against the 10M-cell limit before writing
- 🛡️ **Safe by default:** never rewrites existing rows on append, never wipes a tab on an empty scrape, no formula injection
- 🔌 **Works as an integration:** results go to Sheets each time your scraper finishes
- 💸 **$0.10 per 1,000 rows.** Skipped duplicates are free, and there's no separate compute charge

***

Running with empty input (no sheet, credentials or data) exits successfully with a short demo message so Store health checks pass; provide a spreadsheet + credentials + datasetId for a real sync.

### Why teams switch to Google Sheets Sync

Many dataset-to-Sheets exports download the whole sheet, merge it in memory, and write all of it back on every run. That gets slower as the sheet grows, hits cell limits, and lets two runs finishing at once overwrite each other. Google Sheets Sync is built differently:

| | Google Sheets Sync |
|---|---|
| **Append** | Uses Google's `append` API. Existing rows, formatting and formulas are never rewritten |
| **Parallel runs** | Two runs writing to one tab at the same time keep all rows (tested live) |
| **Dedupe / upsert** | Reads only the key column(s) from the sheet, not the whole tab |
| **Big datasets** | Streams the dataset in pages; 100,000 rows in about 2 minutes (tested live) |
| **Login** | Your own service account key, so no shared app that Google can block and no expiring browser login |
| **Errors** | Plain-English fixes, e.g. "Share the spreadsheet with sync@project.iam.gserviceaccount.com as an Editor" |
| **Retries** | Exponential backoff on Google 429/5xx errors and network drops |
| **Permissions** | Apify *limited permissions*: it can read only the dataset you give it, with no approval prompt |

***

### Quick start (about 5 minutes the first time)

1. **Create a Google service account key.** See [Setup: service account](#setup-service-account-recommended) below.
2. **Share your Google Sheet** with the service account email (`…@….iam.gserviceaccount.com`) as **Editor**.
3. **Fill in the input:**
   - **Google Sheet URL**: paste the browser URL. `#gid=` picks a tab, or type a **Tab name** and it's created if missing.
   - **Mode**: `append`, `replace` or `dedupe`.
   - **Apify dataset**: pick the dataset from your scraper run.
   - **Google service account key**: paste the whole JSON file. It's stored encrypted as a secret.
4. **Run.** The log ends with a link to your sheet.

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
  "sheetName": "Leads",
  "mode": "dedupe",
  "dedupeOn": ["url"],
  "updateExisting": true,
  "datasetId": "aBcD1234efGH",
  "serviceAccountJson": "{ \"type\": \"service_account\", ... }"
}
```

***

### Setup: service account (recommended)

A service account is a robot Google account that never expires and never asks you to log in again.

1. Open [Google Cloud Console](https://console.cloud.google.com/) and create a project (any name, no billing needed).
2. Enable the **Google Sheets API**: [console.cloud.google.com/apis/library/sheets.googleapis.com](https://console.cloud.google.com/apis/library/sheets.googleapis.com).
3. Go to **IAM & Admin → Service accounts → Create service account**. A name is enough; skip the roles.
4. Open the account and go to **Keys → Add key → Create new key → JSON**. A `.json` file downloads.
5. In Google Sheets, click **Share** and add the account's `client_email` as **Editor**.
6. Paste the full contents of the JSON file into **Google service account key**.

> Workspace admins: if key creation is blocked by the `iam.disableServiceAccountKeyCreation` policy, ask your admin for an exception or use OAuth below.

### Setup: OAuth (alternative)

Use this if you can't create service account keys.

1. In Google Cloud Console, create **APIs & Services → Credentials → OAuth client ID** (type *Desktop app*) and enable the Google Sheets API.
2. 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/) using your own client ID and secret ("Use your own OAuth credentials").
3. Fill in **OAuth client ID**, **OAuth client secret** and **OAuth refresh token**.

The sheet must belong to, or be shared with, the Google account that authorized the token.

***

### Run it automatically after every scraper run (integration)

1. Open your scraper or task in Apify Console and go to **Integrations → Connect Actor or task → Google Sheets Sync**.
2. The **Apify dataset** field is prefilled with `{{resource.defaultDatasetId}}`, the dataset of the run that just finished. Keep it.
3. Add your sheet URL, mode and key, then save.

Every time the scraper succeeds, its new results go into your sheet. Use `dedupe` mode with `dedupeOn` so that re-scraped items update or skip instead of piling up.

**Custom webhooks:** send `{"datasetId": "{{resource.defaultDatasetId}}", ...}` as the payload and turn on *Interpolate variables in string fields*. A dataset ID that appears only inside the raw webhook `payload` isn't shared with limited-permission Actors, so always map it into the dataset field.

***

### Modes

| Mode | What it does | Good for |
|---|---|---|
| `append` (default) | Adds rows at the bottom. New fields become new columns on the right | Logs, daily exports |
| `replace` | Clears the tab and writes fresh data, keeping your header order. An empty source leaves the tab untouched | Dashboards, "latest snapshot" |
| `dedupe` | Appends only rows whose `dedupeOn` key isn't in the sheet yet | Lead lists, monitoring |
| `dedupe` + `updateExisting` | **Upsert**: updates matching rows in place and appends new ones | Price trackers, CRM syncs, job boards |

Other options:

- `data`: a JSON array instead of a dataset
- `columns`: which fields to write, and in what order
- `omitColumns`
- `flatten`: nested values become `a.b` and `list/0` columns, like Apify's CSV export
- `limit` / `offset`
- `maxRows`: a cap on rows written per run
- `batchSize`
- `requestsPerMinute`
- `clearIfEmpty`
- `parseValues`: lets Google turn text into dates, numbers and formulas. It's off by default, so values are written as-is

***

### Pricing

Pay only for rows actually written to your sheet. **Skipped duplicates are free.**

| Rows written | Free plan ($0.10 / 1K) | Bronze ($0.09 / 1K) | Silver ($0.08 / 1K) | Gold+ ($0.07 / 1K) |
|---|---|---|---|---|
| 1,000 | $0.10 | $0.09 | $0.08 | $0.07 |
| 10,000 | $1.00 | $0.90 | $0.80 | $0.70 |
| 100,000 | $10.00 | $9.00 | $8.00 | $7.00 |

Plus Apify's standard start fee of $0.00005 per run. Example: a daily scraper adding about 300 new rows in dedupe mode costs about **$0.90 a month**. Set **Max cost per run** in Apify and the Actor stops cleanly before going over it.

***

### Output

Each run saves a summary to the default dataset and to the `OUTPUT` record in the key-value store:

```json
{ "mode": "dedupe", "sheetTitle": "Leads", "itemsRead": 5000, "rowsAppended": 412, "rowsUpdated": 4588, "duplicatesSkipped": 0, "columns": 14, "url": "https://docs.google.com/spreadsheets/d/.../edit#gid=0" }
```

***

### Troubleshooting

| Message | Fix |
|---|---|
| *Permission denied. Share the spreadsheet with …* | Share the sheet with the service account email shown, as **Editor** |
| *Google Sheets API has not been used in project … / is disabled* | Enable the Sheets API for the project your key belongs to |
| *Google says this service account does not exist* | The key or account was deleted. Create a new JSON key |
| *Your Google key could not be used* | Paste the whole JSON file, including the `-----BEGIN PRIVATE KEY-----` block |
| *Spreadsheet not found* | Check the URL, and that the sheet is shared with the service account |
| *This run has no access to dataset …* | Pick the dataset in **Apify dataset**, or map `{{resource.defaultDatasetId}}` into it in the integration |
| *…was not filled in ({{…}})* | Your webhook didn't interpolate variables. Turn on *Interpolate variables in string fields* |
| *…over Google's 10,000,000-cell limit* | Use a new spreadsheet, delete unused tabs, restrict `columns`, or use `replace` |
| *Google Sheets quota exceeded* | Lower `requestsPerMinute`, raise `batchSize`, or avoid many parallel runs on one key |

***

### FAQ

**Does it work with Google Maps, Instagram, Amazon or any other Apify scraper?** Yes. It reads any Apify dataset.

**Will it overwrite my formatting or formulas?** Not in `append` or `dedupe` mode. Rows are added below, and upserts change only the matched rows. `replace` clears cell values in the target tab.

**Can several scrapers write to the same sheet?** Yes. Use one tab per scraper, or the same tab. Parallel appends were tested without losing rows.

**What about very large datasets?** Rows are streamed and batched; 100,000 rows took about 2 minutes in testing. A Google spreadsheet holds at most 10 million cells, and the Actor checks that limit before writing.

**Is my Google key safe?** It's a secret input: Apify encrypts it, and it never appears in logs. The Actor runs with limited permissions and can't see the rest of your Apify account.

**Can I import from Google Sheets into Apify?** This Actor exports datasets and JSON to Sheets.

*Keywords: export Apify dataset to Google Sheets, scraper to Google Sheets, JSON to Google Sheets, Google Sheets integration, append rows to Google Sheets, dedupe, upsert, Google Sheets API automation, sync data to spreadsheet.*

# Actor input Schema

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

Paste the full URL of your Google Sheet (or just its ID). A URL with #gid=… selects that tab.

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

Name of the tab to write to. Created automatically if it doesn't exist. Leave empty to use the tab from the URL or the first tab.

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

append: add rows to the bottom (never rewrites existing data). replace: clear the tab and write fresh data. dedupe: append only rows whose key (see 'Dedupe on') is not already in the sheet; with 'Update existing rows' it becomes an upsert.

## `dedupeOn` (type: `array`):

Field name(s) that identify a unique row, e.g. url, placeId or email. Required for mode 'dedupe'. Nested fields use dots: author.id

## `updateExisting` (type: `boolean`):

In dedupe mode, overwrite the existing row when the key already exists instead of skipping it.

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

Dataset to export (pick one or paste its ID). For automatic export after each scraper run, add this Actor as an integration and set this field to {{resource.defaultDatasetId}}.

## `data` (type: `array`):

Array of objects to write instead of a dataset.

## `limit` (type: `integer`):

Only read this many dataset items.

## `offset` (type: `integer`):

Skip this many dataset items.

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

Recommended. Paste the JSON key of a Google Cloud service account with the Google Sheets API enabled, then share your sheet with the service account's client_email as Editor. Stored encrypted.

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

Your own Google OAuth client ID (alternative to service account).

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

Your own Google OAuth client secret.

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

Refresh token with the https://www.googleapis.com/auth/spreadsheets scope.

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

Only write these fields, in this order. Leave empty to write all fields.

## `omitColumns` (type: `array`):

Fields to leave out.

## `flatten` (type: `boolean`):

Turn {a:{b:1}} into a column 'a.b' and arrays into 'list/0', 'list/1' (same as Apify CSV export).

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

Off (default): values are written exactly as-is, so text like '=SUM()' or '00123' can't turn into formulas or numbers. On: Google parses dates, numbers and formulas like typing them by hand.

## `clearIfEmpty` (type: `boolean`):

By default an empty dataset leaves the sheet untouched so a failed scrape never wipes your data.

## `batchSize` (type: `integer`):

Rows sent to Google per API call.

## `maxRows` (type: `integer`):

Safety cap on rows written (and charged) in one run.

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

Stays under Google's default 60 requests/min per user quota. Raise it only if your project has a higher quota.

## Actor input object example

```json
{
  "mode": "append",
  "updateExisting": false,
  "offset": 0,
  "flatten": true,
  "parseValues": false,
  "clearIfEmpty": false,
  "batchSize": 1000,
  "requestsPerMinute": 55
}
```

# Actor output Schema

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

One dataset item per successful run with mode, sheetTitle, itemsRead, rowsAppended, rowsUpdated, rowsReplaced, duplicatesSkipped, columns, url (link to the Google Sheet tab) and a human-readable summary.

## `result` (type: `string`):

JSON record in the default key-value store. On success it holds the same counts and the sheet url as the summary; on failure it holds { error, message } with a plain-English fix.

# 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("genesis.labs.co/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("genesis.labs.co/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 genesis.labs.co/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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