# Google Sheets Sync – Read, Append, Replace (service account) (`petipeti/google-sheets-sync`) Actor

Read, append or replace Google Sheets data from Apify datasets or JSON using your own Google service account. Retries Google quota errors instead of failing, restores the sheet if a replace fails.

- **URL**: https://apify.com/petipeti/google-sheets-sync.md
- **Developed by:** [peti peti](https://apify.com/petipeti) (community)
- **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

## Google Sheets Sync – Read, Append, Replace, Upsert with change log

Move data between **Apify datasets** and **Google Sheets** in one step:

- **Read** a tab into a dataset (first row = column names) – then export as JSON/CSV/Excel or feed another Actor
- **Append** scraper results below the rows already in your sheet, keeping your column order
- **Replace** a tab with fresh data – the old data is backed up and **restored automatically if the write fails**
- **Upsert** – match rows by a key column (id, SKU, URL), **update only what changed, add new rows**, and write *what* changed into a `changes` column (`price: 1200 → 980`). Changed rows are highlighted yellow, new rows green. Ideal for daily price / stock / ranking monitoring: open the sheet and see today's differences at a glance.

Built to be reliable for scheduled jobs:

- **Google quota errors (429) are retried with back-off instead of failing the run**, and requests are spaced to stay under the default 60-per-minute limit
- Large writes are split into chunks
- Nested JSON is flattened to columns (`address.city`, `tags.0`) locally – no extra API calls
- Clear error messages ("share the spreadsheet with …") instead of raw API errors

Uses **your own Google service account** and the official Google Sheets API. Nothing is scraped, and no Google account password or OAuth login is shared with the Actor developer.

### Setup (about 3 minutes, once)

1. Open [Google Cloud Console](https://console.cloud.google.com/) → create or pick a project.
2. **APIs & Services → Library →** enable **Google Sheets API**.
3. **IAM & Admin → Service Accounts → Create service account** (no roles needed) → open it → **Keys → Add key → JSON**. A `.json` file downloads.
4. Open your spreadsheet → **Share** → add the service account email (`…@….iam.gserviceaccount.com`) as **Editor** (or Viewer for read-only).
5. Paste the whole JSON file into the **Google service account key** field. Apify stores it encrypted (secret input).

### Input examples

Append the results of another Actor run:

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
  "mode": "append",
  "sheetName": "Leads",
  "datasetId": "<defaultDatasetId of the scraper run>",
  "serviceAccountKey": "{ ...json key... }"
}
```

Upsert today's prices by SKU (only changed rows are written and charged):

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
  "mode": "upsert",
  "sheetName": "Prices",
  "keyColumn": "sku",
  "datasetId": "<defaultDatasetId of the price scraper run>",
  "serviceAccountKey": "{ ...json key... }"
}
```

Read a tab:

```json
{ "spreadsheet": "1AbC...", "mode": "read", "sheetName": "Prices", "serviceAccountKey": "{ ... }" }
```

Chain it after any scraper with an Apify **integration / webhook** ("Run Actor" on success, pass `{{resource.defaultDatasetId}}` as `datasetId`) or from Make, Zapier and n8n.

### Output

- **read**: one dataset item per row, keys from the header row (`col1`, `col2` … for empty header cells)
- **upsert**: one dataset item per updated or new row: `{ "key": "A1", "status": "updated", "row": 2, "changes": "price: 1200 → 980" }`. The sheet itself gets the `changes` column and highlights.
- **append / replace / upsert**: `OUTPUT` record with the tab name, number of rows written and the header used; `BACKUP` record with the previous contents (replace mode)

### Modes in detail

| Mode | What happens |
|---|---|
| read | Reads the whole tab. Fails with a list of existing tabs if the name is wrong (it never creates tabs). |
| append | Keeps the existing header. New fields are added as new columns at the right. Rows go below the last filled row. Missing tab → created with a header. |
| upsert | Finds each item's row by `keyColumn`. Writes only the cells whose value differs, so formulas and formatting in other cells stay. Fields missing from an item are left as they are. New keys are added at the bottom. The `changes` column (name configurable) gets `price: 1200 → 980; stock: 3 → 0` or `new`. Rows changed by the previous run but not this one get their note and highlight cleared, so the sheet always shows the latest differences. Numbers and text are compared as text (`300` equals `"300"`). |
| replace | Saves the current values to `BACKUP`, clears the tab, writes header + rows. If any write fails, the previous values are written back and the run fails with the reason. |

`valueInputOption`: `RAW` (default) stores values exactly; `USER_ENTERED` lets Google parse formulas, dates and numbers like typing in the UI.

### Limits

- Google allows up to 10 million cells per spreadsheet.
- Very large runs take time because requests are spaced to respect Google's per-minute quota.

### Privacy

The service account key is used only during the run to obtain a short-lived Google access token and call the Google Sheets API on the spreadsheet you specify. The Actor does not store, log or send your key or sheet contents anywhere else. Revoke access at any time by deleting the key or un-sharing the sheet.

Not affiliated with or endorsed by Google. Google Sheets is a trademark of Google LLC.

### Pricing

Pay per event, platform usage included (you pay nothing for compute):

| Event | Price |
|---|---|
| Actor start | $0.01 per run |
| `row-written` – row appended or written (append/replace), or row updated/added (upsert – unchanged rows are free) | $0.50 per 1,000 rows |
| `row-read` – row read into the dataset (read) | $0.50 per 1,000 rows |

Example: appending 2,000 scraped rows costs $0.01 + $1.00 = $1.01.

A run without a spreadsheet and key only returns a setup reminder; no rows are charged.

### 日本語での説明

Apify のデータセットと Google スプレッドシートの間でデータを読み書きします（読み込み・追記・置き換え・差分更新）。差分更新（upsert）はキー列（ID・SKU・URL など）で行を探し、変わったセルだけを書き換え、新しい行は下に足し、「changes」列に「price: 1200 → 980」のように何が変わったかを書いて行に色を付けます（変更=黄、新規=緑）。変わらなかった行は課金しません。自分の Google サービスアカウントと公式 Google Sheets API を使います。Google の回数制限（429）に当たっても失敗せず待ってやり直し、置き換えに失敗したら元のデータを自動で戻します。

料金: 1回の実行ごとに $0.01、書き込み・読み込みとも 1,000行あたり $0.50（Apify の実行コスト込み）。

# Actor input Schema

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

Required. Paste the spreadsheet URL (https://docs.google.com/spreadsheets/d/.../edit) or just its ID. Share it with your service account email first.

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

read: sheet rows become dataset items (first row = column names). append: adds rows at the bottom and keeps the existing header order. replace: clears the tab and writes new data; the old data is saved to the BACKUP record and restored automatically if writing fails. upsert: finds each row by the key column, writes only the cells that changed, adds new keys at the bottom, writes what changed (e.g. "price: 1200 → 980") into the changes column and highlights those rows.

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

Paste the data here, NOT in the Key column box. An array of objects, one object per row, e.g. \[{"name": "A", "price": 1}]. Used when no dataset ID is given. Ignored in read mode.

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

Instead of pasting rows: the items of this dataset are written to the sheet. Typically the default dataset of another Actor run, so you can chain scrapers into Sheets.

## `keyColumn` (type: `string`):

Just the column name, e.g. name or sku – not JSON. The column that identifies a row; it must exist in the tab's header row (or the tab is empty). Required for upsert mode.

## `changesColumn` (type: `string`):

Just a column name, e.g. changes. Each run writes what changed in that row there ("price: 1200 → 980", or "new" for added rows). Created at the right if missing. Rows changed by the previous run but not this one are cleared.

## `highlightChanges` (type: `boolean`):

Changed rows get a light yellow background, new rows light green. The highlight of the previous run is removed from rows that did not change this time. Only the background color is touched.

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

Name of the tab (worksheet). Empty = first tab. For append/replace a missing tab is created.

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

Required. Paste the whole JSON key file of a Google Cloud service account with the Google Sheets API enabled. Stored encrypted by Apify and used only to call the Google Sheets API for this run. See the README for a 3-minute setup guide.

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

Columns to put first, in this order. Other fields follow in the order they appear. In append mode the sheet's existing header order always wins.

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

{"a": {"b": 1}} becomes a column named a.b. Arrays become a.0, a.1, …

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

RAW keeps "1,1" or "=SUM()" as text. USER_ENTERED lets Google turn them into numbers, dates and formulas.

## `maxItems` (type: `integer`):

Upper limit of rows read or written in one run.

## Actor input object example

```json
{
  "mode": "read",
  "rows": [
    {
      "name": "Example",
      "price": 1
    }
  ],
  "changesColumn": "changes",
  "highlightChanges": true,
  "flattenNested": true,
  "valueInputOption": "RAW",
  "maxItems": 100000
}
```

# Actor output Schema

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

read: one dataset item per sheet row, keys taken from the header row. upsert: one item per updated or new row with key, status, row number and what changed.

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

OUTPUT record: tab name, rows read or written, header used and number of API requests.

## `backup` (type: `string`):

BACKUP record with the values that were in the tab before a replace. Only present after a replace run.

# 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 = {
    "rows": [
        {
            "name": "Example",
            "price": 1
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("petipeti/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 = { "rows": [{
            "name": "Example",
            "price": 1,
        }] }

# Run the Actor and wait for it to finish
run = client.actor("petipeti/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 '{
  "rows": [
    {
      "name": "Example",
      "price": 1
    }
  ]
}' |
apify call petipeti/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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