Google Sheets Import & Export: Append & Upsert

Send an Apify dataset or JSON to a Google Sheet, or pull a sheet back into a dataset, on a schedule with nobody around to click "Allow". Google Sheets Import & Export appends, upserts by key or replaces a tab (with an automatic backup), and exports any tab or A1 range to JSON, CSV or Excel. It's built for unattended runs, like a scraper that finishes at 3 a.m. and has to land its results in the team's sheet.

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

What can Google Sheets Import & Export do?

• 📥 Import (data → sheet): append below existing rows, upsert (update rows whose key matches, add the rest), or replace (clear the tab and write fresh, after saving a backup).
• 📤 Export (sheet → dataset): a whole tab or any A1 range becomes a dataset you can download as JSON, CSV, Excel or HTML. Public sheets ("Anyone with the link: Viewer") export with no credentials at all.
• 🔐 Service-account auth: no consent screen, and service account keys don't expire unless your Google Cloud organization sets a key-expiry policy, so scheduled runs don't wait on anyone to re-authorize. OAuth (client ID + secret + refresh token) also works.
• 🛡️ Safe by default: append only adds rows (and new columns on the right for new fields); it doesn't clear or reorder existing data. Upsert only writes the columns in your data, so your notes and formula columns are left as they are. Replace refuses to run on empty input unless you allow it. Values are written RAW by default, so scraped text like =IMPORTXML(...) is stored as text instead of being evaluated as a formula.
• 🔁 Built to keep running: paces requests under Google's 60-per-minute quota, retries 429/5xx/network errors with truncated exponential backoff and jitter (honouring Retry-After), chunks large writes, and checks the 10,000,000-cell spreadsheet limit before writing anything.
• 🧭 Errors that say what to fix, e.g. "Open the sheet, click Share, and add my-bot@project.iam.gserviceaccount.com as an Editor."
• 🧪 Dry run: see how many rows would be appended, updated or left unchanged, and which columns would be added, without writing or paying per row.

Who is it for?

• Scraper users who want every run's results in one de-duplicated Google Sheet.
• Ops, sales and marketing teams keeping a lead list, price list or report tab fresh without copy-paste.
• Developers and AI agents that need a reliable "write to Sheets" step in an Apify pipeline (it works through the Apify API, the Apify MCP server, webhooks and integrations).

How to export an Apify dataset to Google Sheets

Step 1: Create a Google service account (one time, about 5 minutes)

1. Open Google Cloud Console and create or pick a project.
2. APIs & Services → Library: search Google Sheets API and click Enable.
3. IAM & Admin → Service Accounts → Create service account. Any name is fine; it needs no roles.
4. Open the account → Keys → Add key → Create new key → JSON. A .json file downloads.
5. Open your spreadsheet, click Share, and add the client_email from that file (name@project.iam.gserviceaccount.com) as an Editor.
6. Paste the whole JSON file into Service account JSON key. Apify stores secret inputs encrypted.

The service account can reach only the sheets you share with it. Unshare a sheet to revoke access.

Step 2: Send a scraper's results to the sheet after every run

In your scraper's Integrations tab, add this Actor and use:

{
  "operation": "import",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
  "sheetName": "Leads",
  "datasetId": "{{resource.defaultDatasetId}}",
  "writeMode": "upsert",
  "keyColumns": ["url"],
  "serviceAccountJson": "<paste the JSON key>"
}

Schedule the scraper daily and you get one clean list. Existing rows are updated, new ones are added, and your own "Notes" column is left alone.

Step 3: Check before you write (optional)

{ "spreadsheet": "YOUR_ID", "datasetId": "abc123", "writeMode": "upsert", "keyColumns": ["id"], "dryRun": true }

More examples

Replace a report tab, choosing and renaming columns

{
  "spreadsheet": "YOUR_ID",
  "sheetName": "Daily report",
  "datasetId": "abc123",
  "writeMode": "replace",
  "columns": ["title", "price.value", "address.city"],
  "headerMap": { "title": "Product", "price.value": "Price", "address.city": "City" }
}

Paste JSON directly (array of objects, or array of arrays with a header row)

{ "spreadsheet": "YOUR_ID", "data": [["sku", "qty"], ["A-1", 4], ["B-2", 0]] }

Export a Google Sheet range to JSON or CSV

{ "operation": "export", "spreadsheet": "YOUR_ID", "range": "'Price list'!A1:F", "serviceAccountJson": "<paste the JSON key>" }

Export a public Google Sheet with no setup

If the sheet's General access is "Anyone with the link: Viewer", leave the credential fields empty. The prefilled example does exactly this with Google's public sample spreadsheet:

{ "operation": "export", "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit", "maxRows": 20 }

Without credentials, the Actor reads Google's CSV export, so values come back as displayed text (e.g. "$1,200.00", not 1200) and exportFormattedValues has no effect. Add a service account key to get raw numbers and dates.

Input

Field	What it does
operation	import (data → sheet, default) or export (sheet → dataset).
spreadsheet	Full URL or ID. #gid= in the URL selects the tab.
sheetName	Tab name. Created automatically on import if missing.
serviceAccountJson	Service account key (raw JSON or base64). Secret. Required for import; for export only when the sheet isn't public.
oauthClientId / oauthClientSecret / oauthRefreshToken	Use instead of a service account if you must.
datasetId / data / dataUrl	Exactly one data source when importing.
writeMode	append, upsert or replace.
keyColumns	Upsert key field(s); several fields form a composite key.
columns, excludeColumns, headerMap	Pick, order and rename columns. Nested fields use dots (seller.name).
flattenArrays	json (one cell) or index (tags.0, tags.1 …).
parseValuesAsTyped	Let Google parse dates, currency and formulas (USER_ENTERED). Off by default.
range, hasHeaderRow, skipEmptyRows, exportFormattedValues	Export options.
maxRows, offset, dryRun	Limits and safe testing.
requestsPerMinute, maxRetries, batchRows, maxCellsPerRequest	Advanced reliability settings. The defaults suit almost everyone.

Output

• Import: the Google Sheet itself, plus an OUTPUT record in the run's key-value store with rowsAppended, rowsUpdated, rowsUnchanged, columnsAdded, apiRequests and retries. Replace mode also saves a BACKUP record of the old tab.
• Export: one dataset item per sheet row, keyed by the header row (blank headers become column_N, duplicates name_2).
• On failure: OUTPUT holds { "error": { "code", "message", "hint" } }, and the run's status message says what to fix.

Example OUTPUT after an upsert:

{ "mode": "upsert", "sheetName": "Leads", "rowsAppended": 12, "rowsUpdated": 3, "rowsUnchanged": 185, "columnsAdded": ["phone"], "apiRequests": 6, "retries": 0 }

How much does it cost to send data to Google Sheets?

Pay per event, no subscription:

Event	Price
Sheet sync (once per successful run)	$0.002
Row written (appended or changed)	$0.01 per 1,000 rows
Row exported	$0.01 per 1,000 rows

Rows are charged only after Google confirms the write. Failed runs don't pay the sync fee; dry runs and unchanged upsert rows cost nothing. A daily sync of 2,000 changed rows costs about $0.022 a day. Set Maximum cost per run and the Actor stops cleanly at the limit.

Use it from the API, Python, JavaScript or an AI agent

from apify_client import ApifyClient

client = ApifyClient("<YOUR_APIFY_TOKEN>")
run = client.actor("drop_data_solutions/google-sheets-io").call(run_input={
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "sheetName": "Leads",
    "datasetId": "YOUR_DATASET_ID",
    "writeMode": "upsert",
    "keyColumns": ["url"],
    "serviceAccountJson": open("service-account.json").read(),
})
print(client.key_value_store(run["defaultKeyValueStoreId"]).get_record("OUTPUT")["value"])

It also works from Apify schedules, webhooks/integrations (run it after any Actor finishes), the Apify MCP server for AI agents, and Apify's Make, Zapier and n8n integrations.

Limits to know

• Google allows 10,000,000 cells per spreadsheet (all tabs combined) and 50,000 characters per cell. Longer values are truncated with a warning.
• Google allows 60 requests per minute per user per project. A service account counts as one user, so other tools sharing the same Google Cloud project share that budget.
• Imports and upserts read the target tab once at the start, so very large tabs take longer.
• The header is row 1.
• Datasets up to 200,000 items are read once and kept in memory. Larger datasets are read twice, so give those runs 1 GB+ of memory.
• Writing always needs credentials. Exporting needs either a public sheet ("Anyone with the link: Viewer") or the sheet shared with the service account (Viewer is enough).

FAQ

Does my Google Sheet need to be public?

No. Share it only with the service account's email. Public sheets are a convenience for export: they can be read without any credentials.

Can it write formulas?

Yes. Turn on parseValuesAsTyped and send values that start with =.

Why use a service account instead of OAuth?

Google expires OAuth refresh tokens after 7 days for apps left in "Testing" mode, which breaks scheduled runs. A service account key doesn't expire.

My headers have different names than my data fields.

Use headerMap, e.g. {"price.value": "Price"}. Key columns can use either name.

Will re-running create duplicate rows?

Re-running an append run adds its rows again, so use upsert with a key column for repeated syncs. Within a single run, a retried write request targets the same explicit cell range, so a network retry doesn't add the rows twice.

Support

Open the Issues tab with your run ID. The OUTPUT record and the log include an error code that pinpoints the problem. Built and maintained by drop_data_solutions.