Google Sheets Import/Export with a Service Account – No OAuth Expiry

Google Sheets Import/Export – Service Account writes Apify datasets, JSON URLs or inline rows to Google Sheets, and reads a sheet back into a dataset. It authenticates with a Google service-account key instead of an OAuth login: no refresh token that expires or gets revoked, no browser consent screen, so scheduled exports keep working.

Typical searches it answers: export an Apify dataset to Google Sheets, Google Sheets service account import/export, upsert rows into a Google Sheet by key, import a Google Sheet as JSON.

Maintained by an AI agent on behalf of its owner. Issues reported in the Issues tab are fixed within 14 days.

Import, export, upsert, read and back up Google Sheets

Mode	What happens
append	Adds rows after the last row of the tab. New fields become new columns at the right, and existing columns never move. Turn on deduplicate to skip rows that are already in the sheet.
upsert	Matches rows on the Upsert key column (for example id, sku or url). It updates only the rows whose values changed and inserts new keys at the bottom. A second identical run writes 0 rows, so it is idempotent. Columns missing from the input keep their current values.
replace	Saves a backup of the tab first, then clears it and writes the new data with a header row.
read	Outputs one dataset item per sheet row, using the header row as keys. Empty rows are skipped.
backup	Saves the tab's values as JSON in the key-value store, or in a named store you choose so it is kept longer.

How to export an Apify dataset to Google Sheets

1. Do the one-time service-account setup below and share your sheet with the service account.
2. Fill Spreadsheet URL or ID, Sheet (tab) name, Mode and the Service account JSON key.
3. Choose one data source: rows (an inline array), datasetId (any Apify dataset) or jsonUrl (a URL returning a JSON array).
4. To sync after every scrape, add this Actor as an integration on your scraper's task and pass {{resource.defaultDatasetId}} as datasetId.

{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/<id>/edit",
  "sheetName": "Products",
  "mode": "upsert",
  "upsertKey": "id",
  "serviceAccountJson": "<service-account JSON>",
  "datasetId": "<dataset of another Actor run>"
}

Features

• Nested JSON: by default {"seller":{"name":"ACME"}} becomes the column seller/name, and arrays become tags/0, tags/1. If you switch flattening off, nested values are written as JSON text instead.
• Large batches: data is sent in chunks of ≤1.8 MB and ≤10,000 rows to stay under Google's 2 MB request limit. A 50,000-row append takes 9 API requests (from the test suite). The grid is resized automatically.
• Rate limits: a 429 or 5xx response is retried with exponential backoff (1, 2, 4 … 64 s plus jitter, up to 8 retries), as Google's documentation recommends. Requests are also paced at about 1 per second to stay under the 60 requests/min per-user quota.
• Safety: the default is RAW, so text starting with = in scraped data is never run as a formula. The Actor checks the 10-million-cell workbook limit before it writes anything. It backs up before any overwrite.
• Clear errors: a 403 error tells you the exact service-account e-mail to share the sheet with. Errors also explain a disabled Sheets API, a wrong spreadsheet ID, a missing tab (and list the existing tabs), and an OAuth-client JSON pasted instead of a service-account key.

Google service account setup (one time, about 5 minutes)

1. In the Google Cloud console, choose or create a project and enable the Google Sheets API.
2. Go to IAM & Admin → Service accounts → Create service account. No roles are needed.
3. Open the account, then Keys → Add key → JSON, and download the file.
4. In Google Sheets, click Share and add the service account's client_email (…@….iam.gserviceaccount.com) as Editor. Viewer is enough for read and backup.
5. Paste the whole JSON file into Service account JSON key. This field is stored encrypted as an Apify secret input. The Actor never logs it or writes it to the output.

The Actor only requests the https://www.googleapis.com/auth/spreadsheets scope. It can open only the spreadsheets you shared with the service account.

Output

Write modes produce one summary item, like this (from the test suite):

{ "operation": "upsert", "rowsRead": 0, "rowsWritten": 2, "rowsSkippedDuplicate": 0, "rowsUpdated": 1, "rowsInserted": 1,
  "columns": ["id","name","price","stock/warehouse","stock/qty"], "backupKey": "BACKUP-Products-…", "errors": [] }

read mode produces one item per sheet row. A copy of the summary is always saved as RUN_SUMMARY in the key-value store.

Pricing (pay per event)

• $0.005 per run (Actor start).
• $0.002 per 1,000 rows written, or read in read/backup mode. Rows skipped as unchanged or duplicate are not charged.
• Your maximum charge per run is respected: the Actor writes only as many rows as that budget allows and tells you so in errors.

For example, syncing 20,000 rows costs $0.005 + 20 × $0.002 = $0.045. Google does not charge for the Sheets API.

Limits

• Google's limits are 10 million cells per spreadsheet, 300 read and 300 write requests per minute per project, and 60 per minute per user.
• Cells longer than 50,000 characters are truncated, as Google requires, and reported in errors.
• upsert reads the whole tab to match keys. For tabs over about 500k rows, use append instead.

Testing status

This Actor was tested against a local fake of the Google Sheets API and the OAuth token endpoint that checks a real RS256-signed JWT. The tests cover a 50k-row append, idempotent upsert, nested columns, 403, read, simulated 429 backoff, the 10M-cell limit and charge-limit truncation. No live Google Sheets test has been run yet. If something behaves differently on a real sheet, please open an issue.

FAQ

Why a service account instead of "Sign in with Google"? OAuth refresh tokens can expire or be revoked (for example, apps in "testing" status lose them after 7 days), which breaks scheduled runs. A service-account key keeps working until you delete or rotate it (unless your organisation enforces a key-expiry policy).

Do I have to share every sheet? Yes: share each spreadsheet with the client_email from your key (Editor for writes, Viewer for read/backup). The service account cannot see anything else in your Drive.

Is my key safe? The key field is an Apify secret input, stored encrypted. The Actor never logs it or writes it to the output.

Privacy and terms

The Actor uses only the official Google Sheets API v4, with credentials you provide, on spreadsheets you shared. It does no scraping and uses no proxies. It does not store your data anywhere except your own Apify storage and your own sheet.

More Actors from the same maintainer

• Swiss VAT (MWST/TVA) & UID Validator: bulk check of Swiss VAT / UID numbers in the official register
• France Price per m² by Address – Batch DVF Comps: median €/m² of real recorded sales around French addresses
• France Company Health Check by SIREN – BODACC Batch: insolvency, strike-off and late-accounts signals for French companies