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

Send Apify datasets, JSON or CSV to Google Sheets without failed runs: true append (no full-sheet rewrite), upsert by key, auto-flatten nested data, quota-aware retries. Also reads sheets into a dataset.

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

## Pricing

from $0.08 / 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 – Reliable Export, Append & Upsert

Send any Apify dataset, JSON or CSV to Google Sheets – **without failed runs**. Built for scheduled scrapers and integrations that push data after every run.

- **True append** – new rows go below your data. The sheet is not rewritten on every run, so existing rows, columns, formulas and formatting stay put and big sheets stay fast.
- **Upsert by key** – update rows whose `id`/`url` already exists, append the rest. Perfect for price trackers and monitoring.
- **Quota-aware** – Google allows ~60 write requests per minute per user. When it pushes back (HTTP 429) the Actor waits and retries instead of failing and importing nothing.
- **Nested data handled** – objects become `address.city` columns, simple lists become comma-separated text, complex lists become JSON. No "arrays cannot contain nested structures" errors.
- **Large datasets** – rows are sent in batches sized to Google's request limits; a clear error appears *before* writing if a spreadsheet would pass the 10 million-cell limit.
- **Read mode** – load any tab into a dataset (header row → field names) to feed other Actors.

### How to use it

1. **Create a Google service account** (one-time, ~5 minutes – see below) and copy its JSON key.
2. **Share your spreadsheet** with the service account's email (`...@...iam.gserviceaccount.com`) as **Editor**.
3. Run the Actor with:
   - **Spreadsheet URL or ID**
   - **Mode**: `append`, `replace`, `upsert` or `read`
   - **Service account key** (stored encrypted as a secret input)
   - **Data source**: an Apify dataset ID, inline JSON, or a JSON/CSV URL

**Try it first for free:** run it without a spreadsheet and key. Nothing is written and nothing is charged; the dataset
shows the exact columns and rows your data would get (nested fields flattened), so you can check the layout before setup.

#### After another Actor (integration)

In the other Actor's **Integrations** tab → *Connect Actor or Task* → choose Google Sheets Sync and set:

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/<id>/edit",
  "sheetName": "Results",
  "mode": "append",
  "datasetId": "{{resource.defaultDatasetId}}"
}
```

Every finished run then lands in your sheet automatically.

#### Upsert example (price tracker)

```json
{
  "spreadsheet": "<id>",
  "sheetName": "Prices",
  "mode": "upsert",
  "matchColumn": "url",
  "datasetId": "{{resource.defaultDatasetId}}"
}
```

#### From n8n, Make or any app (one HTTP call)

Save the spreadsheet and the service-account key once in a **Task** (this Actor → *Create task*), so your workflows never carry the key. Then send rows from anywhere:

- `POST https://api.apify.com/v2/actor-tasks/<your-task-id>/run-sync-get-dataset-items`
- Header `Authorization: Bearer <your Apify API token>`
- JSON body – only what changes per call; everything you leave out comes from the task:

```json
{ "mode": "upsert", "matchColumn": "email", "jsonData": [{ "email": "ana@example.com", "status": "won", "value": 1200 }] }
```

Typical uses: the last step of an n8n or Make workflow that collects leads or prices, or a place where an AI agent's results land so people can review them in a sheet.

### Create a service account (one-time)

1. Open [Google Cloud Console](https://console.cloud.google.com/) and create a project (any name).
2. **APIs & Services → Library →** enable **Google Sheets API**.
3. **APIs & Services → Credentials → Create credentials → Service account.** Name it, skip optional steps.
4. Open the service account → **Keys → Add key → Create new key → JSON**. A `.json` file downloads.
5. Paste the whole file content into the **Google service account key** field.
6. Share each spreadsheet you want to use with the service account email (shown in the JSON as `client_email`) as **Editor**.

Your key never leaves your Apify account: it is a secret input, encrypted at rest and hidden in logs. You can revoke it any time in Google Cloud.

### Input options

| Field | What it does |
|---|---|
| `spreadsheet` | URL or ID of the spreadsheet |
| `sheetName` | Tab name; created if missing (default `Sheet1`) |
| `mode` | `append` (default), `replace`, `upsert`, `read` |
| `datasetId` / `jsonData` / `dataUrl` | Where the rows come from (write modes) |
| `matchColumn` | Field used to match rows in `upsert` (`keyColumn` still works) |
| `columns` | Columns to place first, in this order |
| `onlySelectedColumns` | Write only the listed columns |
| `flattenMode` | `dot` (default) or `json` for nested data |
| `parseValues` | On: Sheets interprets numbers/dates/formulas (USER_ENTERED). Off: values stored as-is (RAW) |

### Output

- **Write modes:** a summary in the `OUTPUT` record and the dataset – rows written, final column list, spreadsheet link.
- **Read mode:** every row of the tab in the dataset, keyed by the header row.
- **Preview (no spreadsheet or key):** one `demo` summary item plus up to 100 `preview` rows exactly as they would be written; free.

### Pricing

Pay per event: you pay per row written or read. Higher Apify plans get a Store discount automatically.

| Event | Free plan | Bronze | Silver | Gold and up |
|---|---|---|---|---|
| Row written | $0.00010 | $0.000095 | $0.000088 | $0.00008 |
| Row read | $0.00010 | $0.000095 | $0.000088 | $0.00008 |

1,000 rows ≈ $0.10 · 100,000 rows ≈ $10 on the Free plan ($8 on Gold). Header rows and preview runs are free, failed runs that write nothing cost nothing, and **Max cost per run** caps any run.

### Works great with

- [Google Trends Scraper – Bulk Keywords & Comparable Scores](https://apify.com/donrulezz/google-trends-bulk): add this Actor as its integration to get every Trends run in a sheet automatically.

### FAQ

**Why a service account instead of "Sign in with Google"?** It's more reliable for automation: no expiring sign-in tokens, its own API quota, and it only touches spreadsheets you explicitly share with it.

**Will append move my existing columns?** No. Existing columns keep their position; new fields are added as new columns on the right.

**What happens on Google's rate limit?** The Actor waits (exponential backoff, up to ~1 minute) and retries the same batch. You get your data instead of a failed run.

**Can I write to a sheet with formulas or formatting?** Yes. Append and upsert only write the cells they need.

### Support

Open an issue on the **Issues** tab – fixes usually ship within a few days.

# Actor input Schema

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

Paste the Google Sheets URL (or just its ID). Share the spreadsheet with your service account email as Editor. Without a spreadsheet and key the run is a free preview: it shows the rows it would write and writes nothing.

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

Tab to write to or read from. Created automatically if it doesn't exist.

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

append: add rows below existing data (existing rows and columns never move). replace: clear the tab and write fresh. upsert: update rows whose key matches, append the rest. read: load the tab into this run's dataset.

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

Paste the full JSON key of a Google Cloud service account with the Sheets API enabled. Stored encrypted. 5-minute setup in the README.

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

Rows to send. In an integration after another Actor, use {{resource.defaultDatasetId}}.

## `jsonData` (type: `array`):

Alternatively, paste an array of objects.

## `dataUrl` (type: `string`):

Alternatively, a public URL returning a JSON array or a CSV file.

## `matchColumn` (type: `string`):

Column that identifies a row, e.g. 'url' or 'id'. Rows with a value already in the sheet are updated in place.

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

Optional: columns to place first, in this order. Other fields follow unless 'Only these columns' is on.

## `onlySelectedColumns` (type: `boolean`):

Write only the columns listed above and drop other fields.

## `flattenMode` (type: `string`):

How nested objects and arrays become cells.

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

On: numbers, dates and formulas are interpreted (USER_ENTERED). Off: everything is stored exactly as text/values (RAW).

## Actor input object example

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789/edit",
  "sheetName": "Sheet1",
  "mode": "append",
  "datasetId": "{{resource.defaultDatasetId}}",
  "jsonData": [
    {
      "sku": "A-100",
      "price": 19.9,
      "inStock": true
    },
    {
      "sku": "B-200",
      "price": 4.5,
      "inStock": false
    }
  ],
  "dataUrl": "https://example.com/export.csv",
  "matchColumn": "sku",
  "onlySelectedColumns": false,
  "flattenMode": "dot",
  "parseValues": true
}
```

# Actor output Schema

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

Rows written/read, sheet, final column list and spreadsheet URL.

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

Write modes: one summary item. Read mode: every row of the sheet as an object keyed by the header row.

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

```

## MCP server setup

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