Google Sheets Sync: Import, Export, Append & Upsert

Move any Apify dataset into Google Sheets, or bring a Google Sheet into a dataset. It retries automatically, handles big sheets in batches, and explains errors in plain English. You don't need an OAuth app, and there are no tokens that expire every 7 days.

Try it free: Apify's free plan gives $5/month of usage, no card needed. That's roughly 150,000 rows written a month at $0.03 per 1,000 rows (plus $0.002 per sync run).

• ✅ Export: dataset → sheet. Four write modes: Append, Append only new, Upsert (update rows by key) and Replace, which saves a backup of the old tab first.
• ✅ Import: sheet → dataset, as clean typed JSON.
• ✅ No OAuth. Use a Google service account, a Google API key (read-only), or no credentials at all for sheets shared as "Anyone with the link".
• ✅ Built for reliability. It retries Google's 429 quota, 5xx and dropped-connection errors with exponential backoff that respects Retry-After. It also throttles itself under Google's 60 requests/min quota.
• ✅ Big sheets. Writes go out in batches (5,000 rows per request, capped at about 4 MB). It checks Google's 10M-cell limit before writing, so you don't end up with half-written tabs.
• ✅ Safe by default. Values are written raw, so scraped text such as =IMPORTXML(...) can never turn into a live formula. Upsert never deletes columns or cells it doesn't own.
• ✅ Dry run. See exactly what would be appended, updated or skipped before anything is written.
• ✅ Fair pricing. You pay per row written or read. Failed runs cost nothing.

Try it in 10 seconds

Click Start with the default input. It imports Google's public sample sheet ("Class Data", 30 students) in public-link mode, so you can see the output format before you set anything up.

Switching from another Google Sheets actor? Keep your input

Tasks and integrations built for the OAuth-based Google Sheets Import & Export actor run here unchanged. Add one field, serviceAccountKey (see Quick start), and share the sheet with the service account's email as Editor. Its field names are read as they are:

Old field	What happens here
mode: append	Appends rows. With deduplicateByField, only rows whose value is not already in the sheet are added and existing rows are left alone.
mode: replace	Replaces the tab (deduplicated if you set a dedupe option). createBackup saves the old rows first.
mode: modify + transformFunction	Reads the tab, runs your ({ spreadsheetData, datasetData }) => rows function (30-second limit) and writes the result back.
mode: read	Rows go to the dataset and, as before, to the OUTPUT record. The run summary is in SUMMARY.
mode: load backup + backupStore	Restores a backup saved by either actor (record backup or BACKUP).
spreadsheetId, range, datasetId, rawData, limit, offset, columnsOrder, deduplicateByEquality, publicSpreadsheet (read only)	Used as is. Integration payloads (resource.defaultDatasetId) work too.
googleCredentials, tokensStore, Google account	Not needed. If the input has no service-account key, the run stops with step-by-step setup instructions and costs nothing.

A service account doesn't use browser sign-in, so there are no refresh tokens to expire or be revoked.

Quick start: export a dataset to a sheet (3-minute setup, done once)

Google only allows writes from an authenticated identity. A service account is a robot Google account that never expires and needs no browser login.

1. Open https://console.cloud.google.com/, then create or pick a project.
2. Go to APIs & Services → Library → "Google Sheets API" → Enable.
3. Go to IAM & Admin → Service accounts → Create service account. Give it any name and skip the optional roles.
4. Open the service account and go to Keys → Add key → Create new key → JSON. A .json file downloads.
5. In your Google Sheet, click Share, paste the service account's email (…@….iam.gserviceaccount.com) and set it to Editor.
6. In this actor, set Authentication = Service account and paste the whole JSON file into Service account key. The field is a secret input and is stored encrypted by Apify.

That's all. The key keeps working until you delete it, so there are no invalid_grant failures a week later.

Authentication modes

Mode	Read	Write	Private sheets	Setup
Service account	✅	✅	✅ (share with its email)	3 min, one time
API key	✅	❌	❌ (must be shared "Anyone with the link")	1 min
Public link	✅	❌	❌ (must be shared "Anyone with the link") or File → Share → Publish to web	none

Public-link mode reads Google's own CSV export of the sheet, the same file you get from File → Download → CSV. Use it for sheets you're allowed to view. Google's API rules say that writing always needs authentication, and this actor follows them.

Write modes (export)

Mode	What happens	Needs key columns
Append	Adds the rows at the bottom of the tab.	no
Append only new	Adds only rows whose key (e.g. url) isn't in the tab yet. This is ideal for scheduled scrapers.	yes
Upsert	Updates rows whose key exists and appends the rest. Only the cells for exported fields change, so your own notes columns stay untouched.	yes
Replace	Clears the tab and writes the data. The old contents are saved to the key-value store as BACKUP first.	no

Other export options:

• Remove duplicates in input: dedupes by the key columns.
• Fields / omit fields / column order: choose which fields are written and in what order.
• Add new columns: new fields get new columns on the right, and existing columns are never reordered.
• Arrays: written as tags/0, tags/1…, joined in one cell, or as JSON.
• Create the tab if missing.
• Parse values like typed input: off by default.

Use it after another actor (integrations)

1. On your scraper's page, go to Integrations → Add → Apify actor, pick Google Sheets Sync, and choose the Run succeeded trigger.
2. Leave Dataset ID empty. The actor detects the finished run's dataset (payload.resource.defaultDatasetId) automatically.
3. Use Append only new with keyColumns: ["url"]. Every scheduled scrape then adds only the new results.

You can also call it from code:

await Actor.call('USERNAME/google-sheets-sync', {
  mode: 'export', authMode: 'serviceAccount', serviceAccountKey: process.env.GOOGLE_SA_JSON,
  spreadsheet: 'https://docs.google.com/spreadsheets/d/…/edit#gid=0', sheetName: 'Leads',
  datasetId: run.defaultDatasetId, writeMode: 'upsert', keyColumns: ['url'],
});

Output

• Import: one dataset item per row, keyed by the header row. Duplicate or empty headers become Name, Name_2, column_3.
• Every run writes an OUTPUT record (key-value store):

{ "mode": "export", "writeMode": "upsert", "dryRun": false, "spreadsheet": "My book", "sheet": "Leads",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/…/edit#gid=0",
  "itemsRead": 1200, "duplicatesInInput": 3, "rowsAppended": 140, "rowsUpdated": 60, "rowsSkippedExisting": 997,
  "columnsAdded": 1, "batches": 2, "columns": 9, "header": ["url", "name", "…"], "warnings": [],
  "requests": 9, "retries": 1, "durationMs": 4120, "stoppedAtCostLimit": false }

Errors you can act on

Each error tells you what to do next. For example:

• "… → Open the sheet → Share → add the service account sync-bot@….iam.gserviceaccount.com as Editor → Send."
• "… → Enable it at https://console.cloud.google.com/apis/library/sheets.googleapis.com?project=… and run again."
• "Tab "Nope" not found. Tabs in this spreadsheet: "Class Data"."
• "Spreadsheet not found, or it is not shared publicly. → Share → General access → Anyone with the link → Viewer."
• "This write would take the spreadsheet past Google's 10,000,000-cell limit (≈… cells). Nothing from this batch was written."

Limits (Google's, not ours)

• 10,000,000 cells per spreadsheet, and 50,000 characters per cell. Longer values are truncated with a warning.
• 60 read and 60 write requests per minute per user. Each request carries up to 5,000 rows, so 100k rows take about 20 requests.
• Public-link mode reads whole tabs as CSV and returns formatted values. Use an API key or service account for raw numbers, formulas or A1 ranges.

Pricing (pay per event)

Event	Price
Successful run	$0.002
Row written to a sheet	$0.03 per 1,000 rows
Row read from a sheet	$0.01 per 1,000 rows

A daily scrape that appends 500 rows costs about $0.017. Importing a 10,000-row sheet costs about $0.10. Failed runs and dry runs aren't charged. Old-actor modes that rewrite the tab (modify, transforms, deduplicateByEquality in append mode) write every row, so every row counts as written; reading the tab for them is free. Set Maximum cost per run in the run options and the actor stops cleanly at that limit.

FAQ

Is my service-account key safe? It's an Apify secret input, encrypted at rest and never logged. The actor only sends it to Google's token endpoint. Give the service account access only to the sheets it needs.

Why not OAuth / "Sign in with Google"? OAuth refresh tokens for unverified apps expire after 7 days, which is the main reason other Sheets actors fail. Service accounts don't expire.

Can it write formulas? Yes. Turn on Parse values like typed input. It's off by default for safety.

Does Upsert delete rows that are missing from my data? No. Upsert only adds and updates. Use Replace for a full mirror; it saves a backup first.

My sheet has 200k rows. Will Append only new be slow? It reads only the key column(s), one request per key column, then writes in batches of up to 5,000 rows.

Can I import a tab by name in public mode? Yes. Set Tab name; the actor looks up its ID from the public page.