Google Sheets Import & Export – Append, Upsert or Replace rows (Service Account, no OAuth popups)

Send any Apify dataset or JSON to Google Sheets – append new rows, upsert (update by key), or replace a whole tab – and export a Google Sheet to an Apify dataset. Built for scheduled scraper pipelines: it authenticates with a Google service account (no OAuth tokens that expire), maps your fields to the existing header row automatically, keeps numbers as numbers, retries Google's rate limits, and when something is wrong it tells you exactly what to fix.

Beta: This Actor is new. Its Google Sheets integration has been tested with mocked Google API responses and with dry runs. If something doesn't work, please report it on the Actor's Issues tab. You're only billed for rows in batches that were written or read successfully.

Free demo: click Start with empty input. The Actor runs a dry run on sample data against an in-memory sheet and shows you the exact rows it would write. No Google account needed, nothing is charged.

What does Google Sheets Import & Export do?

Mode	What happens
append	Adds rows under the existing data. With Key columns set, rows whose key already exists are skipped, so you can re-run a scraper every hour and only add the new items.
upsert	Updates rows whose Key columns match, adds the rest. Only the fields you send are overwritten; other columns, such as your own notes, are kept. Rows that didn't change aren't rewritten (or charged).
replace	Clears the tab and writes the new data. The old content is saved to the run's key-value store first (BACKUP-<tab>).
export	Reads a tab (or an A1 range) and saves every row to the run's dataset as JSON objects keyed by the header, with real numbers and booleans. Download as CSV, Excel or JSON, or feed another Actor.

Why this Actor?

The most-used Google Sheets Actor in the Apify Store fails on about half of its runs (Apify Store public run statistics, last 30 days). Its public issues are mostly about authorization, spreadsheet-ID validation, writing to a different tab, the cell limit, and column order. This Actor is designed around those problems:

• Service-account auth. Paste the JSON key and share the sheet with one e-mail address. There's no browser popup or token store, and nothing expires on a schedule. OAuth refresh tokens are supported too.
• Paste the URL. It accepts any Google Sheets URL or ID of any length, and picks the tab from #gid= in the URL.
• Any tab. Set Sheet (tab) name and the tab is created if it doesn't exist.
• Header auto-mapping. product_name matches the column Product Name. Existing columns are never reordered, and new fields become new columns on the right. You can turn this off.
• Types preserved. RAW mode by default: 12.5 stays a number, true a checkbox-style boolean, and 00123 or +1 555… stay text. =formulas from scraped text are not executed (no formula injection), and there are no locale surprises with 1,5.
• Large datasets. Data is chunked by rows and by about 2 MB of payload, and the grid grows automatically past 1,000 rows and 26 columns. The Actor checks the 10,000,000-cell spreadsheet limit before writing and truncates cells over 50,000 characters (it reports how many).
• Quota-aware. It paces requests for the 60 writes/min/user quota and retries 429, 5xx and network errors with exponential backoff, honoring Retry-After.
• Clear errors. For example, Permission denied … share it with "my-bot@project.iam.gserviceaccount.com" as Editor, or Sheets API is not enabled – open this link and click Enable.

Setup in 3 steps (about 5 minutes, once)

1. Create a service account key. In Google Cloud Console, open or create a project. Go to APIs & Services → Library → Google Sheets API → Enable. Then go to IAM & Admin → Service Accounts → Create service account (no roles needed), open it, and choose Keys → Add key → Create new key → JSON. A .json file downloads.
2. Share your sheet with the service account's e-mail (the client_email in the JSON, e.g. sheets-bot@my-project.iam.gserviceaccount.com). Click Share in the sheet, paste the address, choose Editor (Viewer is enough for export), untick Notify, and click Share.
3. Run the Actor. Paste the whole JSON file into Service account JSON key (stored encrypted as a secret input and never logged), paste the sheet URL, and pick the source: a Dataset ID, Data (JSON), or attach this Actor as an integration to your scraper so every finished run is pushed to the sheet.

Prefer OAuth? Fill in OAuth client ID, OAuth client secret and OAuth refresh token (scope https://www.googleapis.com/auth/spreadsheets, for example from the Google OAuth Playground with your own client). Note that Google expires refresh tokens after 7 days while your consent screen is in Testing mode.

Input examples

Append scraper results, skipping URLs already in the sheet

{
  "mode": "append",
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
  "sheetName": "Leads",
  "datasetId": "YOUR_DATASET_ID",
  "keyColumns": ["url"],
  "serviceAccountJson": "{ \"type\": \"service_account\", ... }"
}

Keep a price list up to date (upsert by SKU)

{
  "mode": "upsert",
  "spreadsheetId": "1AbC...",
  "keyColumns": ["sku"],
  "fields": ["sku", "title", "price", "inStock"],
  "datasetId": "YOUR_DATASET_ID",
  "serviceAccountJson": "..."
}

Replace a tab with fresh data, nested objects stored as JSON text

{ "mode": "replace", "spreadsheetId": "1AbC...", "sheetName": "Daily", "nestedHandling": "json", "data": [{ "name": "A", "meta": { "x": 1 } }], "serviceAccountJson": "..." }

Export a sheet to a dataset

{ "mode": "export", "spreadsheetId": "1AbC...", "sheetName": "Input URLs", "range": "A1:D500", "serviceAccountJson": "..." }

Try it without credentials (dry run)

{ "dryRun": true, "mode": "upsert", "keyColumns": ["sku"], "dryRunExistingValues": [["SKU", "Price"], ["a", 1]], "data": [{ "sku": "a", "price": 5 }, { "sku": "b", "price": 7 }] }

Use it from code or as an integration

• Integration: on your scraper's page, open Integrations → Connect Actor → Google Sheets Import & Export. The source run's default dataset is used automatically (payload.resource.defaultDatasetId).
• API: POST https://api.apify.com/v2/acts/<username>~google-sheets-sync/runs?token=… with the input JSON.

Output

• Write modes: one summary record in the dataset and in the OUTPUT key-value record. It contains the spreadsheet URL, tab, rows written, appended, updated, unchanged and skipped, new columns, truncated cells, and API retries.
• Export mode: one dataset item per sheet row, e.g. {"sku": "a", "price": 12.5, "inStock": true}. Turn on Unflatten to rebuild seller/name columns into nested objects.

Pricing

This Actor uses Apify's pay-per-event pricing. You pay for the rows the Actor actually moves:

Event	What it is	Price
rows-written	One row successfully written to your Google Sheet (appended, updated or replaced)	$0.0001 per row ($0.10 per 1,000 rows)
rows-read	One row successfully exported from your Google Sheet to the dataset	$0.00005 per row ($0.05 per 1,000 rows)

• You pay only for rows written or read successfully. Rows are charged after each batch is accepted by the Google Sheets API (or, for export, saved to the dataset). If a batch fails, its rows aren't charged.
• Free: rows skipped as duplicates, unchanged rows in upsert mode, and dry runs (including the empty-input demo) don't trigger rows-written or rows-read.
• Platform usage is included. With pay-per-event, the Actor's developer pays for the platform compute the run uses; you pay for the events above. Any other events Apify adds by default, such as its standard Actor start event, are listed on the Actor's Pricing tab. That tab always shows the current prices.
• Spending limit: the Actor stops cleanly at your run's Max cost per run limit and reports how many rows were left out.

FAQ

Is my service account key safe? It's a secret input, so Apify stores it encrypted, and the Actor never logs it. Use a dedicated service account that only has access to the sheets you share with it, and delete the key in Google Cloud to revoke access at any time.

Do I need a paid Google account? No. A free Google Cloud project with the Sheets API enabled is enough. Google doesn't charge for the Sheets API.

How many rows can I write? Up to Google's limit of 10,000,000 cells per spreadsheet (rows × columns, all tabs combined). The Actor checks this before writing.

Will it mess up my column order or formulas in other columns? No. Existing columns stay where they are. In upsert mode, only the fields you send are overwritten.

Why are my numbers text, or my dates not dates? In RAW mode, values keep their JSON type. If your scraper outputs "12.5" as a string, it stays text. Switch Value handling to USER_ENTERED to let Google parse dates and numbers in strings, and to run formulas.

Can it read public sheets without credentials? Not in this version. Export always uses your service account or OAuth credentials. Share the sheet with the service-account e-mail as Viewer.

Can it write to Excel/.xlsx files in Drive? No. Open the file and use File → Save as Google Sheets, then use the new URL.

Troubleshooting

Message	Fix
Permission denied … not shared with "…@….iam.gserviceaccount.com"	Share the sheet with that exact e-mail as Editor (step 2). In Google Workspace, your admin may block sharing outside the domain. Ask them, or use OAuth.
Google Sheets API is not enabled in your project	Open the link in the message, click Enable, wait a minute.
Spreadsheet not found	Copy the URL again from the address bar. For .xlsx files, use Save as Google Sheets.
invalid_grant (OAuth)	The refresh token expired or was revoked (7-day expiry in Testing mode). Create a new one or switch to a service account.
invalid_grant (service account)	The key was deleted or rotated. Create a new JSON key.
Tab "X" not found (export)	The message lists the available tabs. Names must match (case-insensitive).
10,000,000 cells	Use Fields to include or Limit, delete empty columns or tabs, or write to a new spreadsheet.
Quota (429) still failing after retries	Increase Delay between batches, run less often, or use one service account per workflow.

Limitations (honest list)

• Writes values only. It doesn't change formatting, charts, filters or data validation.
• Upsert reads the whole tab to find keys, so very large tabs (millions of cells) take longer and use more memory. Plain append on very large sheets uses Google's append API and reads only the header.
• One tab per run. Use several tasks for several tabs.

Local development

npm install
npm test                     # unit tests with a mocked googleapis client + end-to-end dry runs
node src/main.js --dry-run   # or: apify run (input in storage/key_value_stores/default/INPUT.json)