Google Sheets Import & Export

Google Sheets Import & Export moves tabular data between Google Sheets and your Apify datasets or raw JSON — read a sheet into structured data, or write scraped data back into a spreadsheet with append, replace, and upsert. Reading a link-shared sheet needs no login and no API key. Writing uses a Google service account, so a scheduled run never stalls waiting for someone to click an OAuth consent screen.

Why this Google Sheets actor

Most Sheets integrations authenticate with interactive OAuth: the first time a run needs to write, it waits for a human to open a link and approve access. On a schedule, or inside an automation, that silently hangs or fails. This actor authenticates non-interactively with a service-account key, retries Google's rate limits (HTTP 429 and 5xx) with exponential backoff, and returns a report of exactly how many rows and columns it wrote — so an import that half-succeeds tells you, instead of failing quietly.

What can the Google Sheets actor do?

• Import a Google Sheet to JSON, CSV, or Excel — pull a whole tab into an Apify dataset you can download or feed to another actor.
• Export scraped data to Google Sheets — send any dataset (or inline JSON) straight into a spreadsheet for reporting or manual review.
• Append rows to a Google Sheet — add new records to the bottom of a tab without touching what is already there.
• Replace a sheet with fresh data — clear a tab and write a new header plus rows in one run.
• Upsert by a key column — write only the rows whose key is not already in the sheet, so re-runs don't create duplicates.
• Read a public sheet with no credentials — if a sheet is shared "Anyone with the link → Viewer," just paste the URL.
• Flatten nested JSON into columns — nested objects and arrays become columns like address/city, so scraped data lands in a clean grid.

How to read a Google Sheet (no key needed)

1. Set Mode to read.
2. Paste the sheet's URL (or its ID) into Spreadsheet URL or ID.
3. If the sheet is private, either share it "Anyone with the link → Viewer," or add a service-account key (below).
4. Run it. Each row becomes one dataset item, using the first row as column names.

How to write data into a Google Sheet

Writing changes your spreadsheet, so it needs a Google service account — a robot Google account you control:

1. In the Google Cloud Console, create a service account and a JSON key, and enable the Google Sheets API.
2. Open the JSON key and copy the client_email (it looks like name@project.iam.gserviceaccount.com).
3. In Google Sheets, Share your spreadsheet with that email as an Editor.
4. Paste the entire key JSON into the Service-account key JSON field.
5. Choose a mode — append, replace, or upsert — set the Tab name, and supply the rows (inline data, a source dataset ID, or this run's default dataset).

The service-account key is stored as a secret input.

Modes

Mode	What it does	Needs a key
read	Sheet → dataset (JSON/CSV/Excel)	Only for private sheets
append	Add rows to the bottom of a tab	Yes
replace	Clear the tab, write header + rows	Yes
upsert	Append only rows whose key column is new	Yes

Input example

Read a public sheet:

{
  "mode": "read",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "headerRow": true
}

Append a scraper's results to a spreadsheet:

{
  "mode": "append",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/your-sheet-id/edit",
  "tab": "Leads",
  "datasetId": "your-dataset-id",
  "serviceAccount": { "type": "service_account", "client_email": "...", "private_key": "..." }
}

Output

In read mode, every row is pushed to the dataset as a JSON object keyed by the header, ready to export as JSON, CSV, or Excel, or to pass to another actor. In write modes, the run's log and result report the mode, the number of rows written, the column count, and — for upsert — how many existing rows were skipped.

Use cases

• Push scraped leads, products, or listings into a shared spreadsheet your team can read and edit.
• Build a live report that appends fresh rows on a schedule and never duplicates them (upsert).
• Turn a spreadsheet into an API — read a sheet your team maintains and feed it into other actors or your own code.
• Back up or migrate rows between datasets and sheets.
• Chain after any scraper — run a scraper, then this actor with "use this run's default dataset" to land the data in Sheets automatically.

Notes and limits

• Cells over Google's 50,000-character limit are truncated (with a trailing …) and the run continues.
• Large writes are automatically split into chunks to stay within Google's request limits.
• Reading a private sheet without a key returns a clear message telling you how to share it or add a key — it never returns empty rows silently.
• The actor reads and writes only the spreadsheets you point it at and share with your service account. It collects no personal data on its own.