Google Sheets Import & Export (Reliable, Service Account)

Read, append or replace Google Sheets rows from any Apify dataset or JSON — with service-account authentication that never expires and never needs a browser login. Built for scheduled pipelines, scrapers and AI agents that must not break at 3 a.m.

Why this Actor

• 🔐 Service account auth — no OAuth popups, no tokens expiring, no "app blocked by Google" errors.
• 🌐 Public sheets without any login — just paste the link.
• ➕ Append under the existing header; new fields become new columns automatically (existing column order is kept).
• ♻️ Replace a whole tab in one run (great for daily exports).
• 🧩 Nested JSON is flattened to dotted columns (user.name), lists are stored as JSON text.
• 📦 Batches for large data (1,000 rows per request) to stay within Google limits.
• 🛡️ Safe by default — values are written RAW, so text like =IMPORTXML(...) is never executed as a formula unless you choose USER_ENTERED.
• 💬 Clear error messages that tell you exactly what to fix (sharing, tab name, key format).

Quick start

Read a public sheet (no setup)

{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/<ID>/edit#gid=0",
  "operation": "read"
}

Each row becomes one dataset item, using the first row as the header.

Write rows (append or replace)

1. In Google Cloud Console create a service account and a JSON key (free). Enable the Google Sheets API for the project.
2. Open your spreadsheet → Share → add the service account email (...@...iam.gserviceaccount.com) as Editor.
3. Run:

{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/<ID>/edit",
  "operation": "append",
  "sheetName": "Leads",
  "datasetId": "<APIFY_DATASET_ID>",
  "serviceAccountJson": "{ ...paste the key file... }"
}

The key is stored encrypted by Apify (secret input).

Input

Field	Description	Default
spreadsheet	Sheet URL or ID (a tab URL with #gid= selects that tab)	—
operation	read, append, replace	read
serviceAccountJson	Service account key (needed to write or read private sheets)	—
sheetName	Tab name (created automatically on write if missing)	first tab
datasetId	Apify dataset to write	—
rows	Array of objects to write (alternative to datasetId)	—
columns	Only these columns, in this order	all
valueInputOption	RAW (safe) or USER_ENTERED	RAW
maxRows	Upper limit per run	100000

Output

• Read: one item per row, e.g. { "Name": "Ana", "Age": "30" }.
• Append / Replace: one summary item { "operation": "append", "rowsWritten": 250, "sheet": "Leads" }.

Pricing (pay per event)

You pay a small fee per row read or written plus a tiny start fee per run. Failed runs with a configuration error write nothing and charge no rows.

Integrations

Chain it after any scraper with Apify Integrations → Run Actor (pass the scraper's defaultDatasetId as datasetId), or call it from n8n, Make, Zapier, LangChain or an AI agent through the Apify MCP server.

Troubleshooting

• "Cannot open the spreadsheet" → share the sheet with the service account email.
• "This spreadsheet is not public" → set sharing to "Anyone with the link can view" or use a service account.
• "Reading a tab by name needs serviceAccountJson" → for public sheets paste the tab URL (contains #gid=).