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 → 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:

{
  "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):

{
  "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:

{ "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 の実行コスト込み）。