Google Sheets Import & Export: Dataset to Sheet and Back

What does Google Sheets Import & Export do?

Google Sheets Import & Export moves rows between Apify datasets and Google Sheets in both directions. Write a scraper's results to a sheet (append, append only new rows, update by key or replace), or read a sheet into a dataset. It runs after any scraper as an integration, and it signs in with your own Google service account, so there's no OAuth app for Google to block.

Input fields · API · Use it from Claude, ChatGPT or Cursor (MCP)

Jump to: Set up writing · After a scraper · Modes · Price · Input · Output · Switching · AI agents (MCP) · Limits · FAQ

Try it in one click: the input comes pre-filled to read Google's public sample sheet (30 students). That's 30 rows, about $0.003 plus $0.00005 for the run start. Then put in your own sheet's link.

Which mode do I need?

You want	Mode	What happens to the sheet
A sheet's rows as JSON, CSV or Excel, or as input for another actor	read	Nothing; each row becomes a dataset item keyed by the header row
Every scraper run added below the last one	append	New rows go under the existing ones
A growing list with no repeats (leads, listings, jobs)	appendNew	Only rows whose key (e.g. url) isn't in the sheet yet are added
A live table that stays current (prices, stock, statuses)	upsert	Rows whose key is already there are updated in place; the rest are added
The sheet to show exactly the latest run	replace	Old rows are overwritten, then what's left below is cleared

In every write mode the header row is kept: fields go into the column with the same name (any case), and fields the sheet doesn't have yet are added as new columns on the right. Nested data is flattened to columns such as address.city and tags.0.

Set up writing (3 minutes, once)

Reading a sheet shared as "Anyone with the link" needs nothing. Writing (and reading a private sheet) uses a Google service account: a robot Google account that belongs to you. It's free and needs no app review.

1. Open Google Cloud console and create a project (or pick one you have).
2. Open APIs & Services → Library, search Google Sheets API, and click Enable.
3. Open IAM & Admin → Service Accounts → Create service account. Any name works (for example sheets-writer); it needs no roles. Click Done.
4. Click the new account, open Keys → Add key → Create new key → JSON → Create. A .json file downloads.
5. Open that file in a text editor, copy all of it, and paste it into Google service account key (JSON) (serviceAccountKey). Apify stores it encrypted; the actor sends it only to Google.
6. In your Google Sheet click Share, add the key's client_email (it ends in .iam.gserviceaccount.com) as an Editor, untick "Notify people", and click Share.

That's all: every later run, scheduled or chained after a scraper, uses the same key. If your company's Google Workspace refuses step 4 ("key creation is disabled"), an admin has to allow it for the project, or use a project in a personal Google account.

Send every scraper run to a sheet automatically

1. Open your scraper (or its task) and go to the Integrations tab.
2. Add an integration with Google Sheets Import & Export (search for it under "Apify actors").
3. In its input, set What to do (for example appendNew), the sheet's link, your service account key and, for appendNew or upsert, the Key column (for example url). Set Rows to write: Apify dataset to {{resource.defaultDatasetId}}: Apify fills in the finished run's dataset, and gives this run access only to the dataset named there.
4. Save. From now on each successful scraper run lands in the sheet within seconds of finishing.

A webhook works the same way: start this actor with "datasetId": "{{resource.defaultDatasetId}}" in its payload.

How much does it cost to export a dataset to Google Sheets?

You pay per row: $0.10 per 1,000 rows written to the sheet (added or updated) or read from it, plus $0.00005 each time a run starts. A write run also adds one summary row to its dataset, charged as one row.

• The pre-filled example: 30 rows read = $0.003, plus the start fee.
• A daily scraper of 500 new rows into a sheet: 15,000 rows a month ≈ $1.50.
• Never charged: rows appendNew skips because they're already in the sheet, rows without a key, repeated keys, empty rows, and anything after a failure.
• Caps: Max rows per run in the input, and Maximum cost per run in the run options. The run stops cleanly at whichever comes first and writes only the rows it can charge for.

Example: read Google's sample sheet

The pre-filled input:

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

It returned 30 rows (local run, 2026-10-14). The first:

{
  "Student Name": "Alexandra",
  "Gender": "Female",
  "Class Level": "4. Senior",
  "Home State": "CA",
  "Major": "English",
  "Extracurricular Activity": "Drama Club"
}

Example: add only new leads from a scraper

{
  "mode": "appendNew",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/<your sheet id>/edit",
  "sheetName": "Leads",
  "keyColumn": "url",
  "columns": ["title", "url", "phone", "website", "totalScore"],
  "serviceAccountKey": "<the JSON key file>"
}

Run after a Google Maps scraper, this adds the places whose url isn't in the Leads tab yet, in those five columns, and creates the Leads tab if it doesn't exist. Run it every day and the tab only ever grows by new places.

Input

Field	What it does
What to do (mode)	read, append, appendNew, upsert or replace (see modes).
Google Sheets link (spreadsheetUrl)	The sheet's address from your browser, or its id. #gid= in the link picks the tab.
Tab name (sheetName)	The tab to use, any case. Writing to a tab that doesn't exist creates it.
Google service account key (serviceAccountKey)	Your key file, pasted whole (secret). Needed to write, and to read private sheets.
Rows to write: Apify dataset (datasetId)	The dataset to write. Empty in an integration: the finished run's dataset.
Or: paste rows (items)	A JSON array of objects to write.
Or: file URL (fileUrl)	A public CSV, JSON, JSONL or Excel file to write.
Key column (keyColumn)	For appendNew and upsert: the column that identifies a row, e.g. url.
Columns to write (columns)	Only these fields, in this order. Empty: all fields.
Add new columns (addNewColumns)	Add fields the header row lacks as new columns (default on).
Flatten nested fields (flatten)	address.city columns (default on), or JSON text in one cell.
How values are entered (valueInput)	raw (default, nothing runs as a formula) or userEntered (parsed like typing).
Header row (headerRow)	For read: the row with the column names, when a title sits above the table.
Values to read (readValues)	For read with a key: as shown (formatted) or underlying numbers (unformatted).
Max rows per run (maxRows)	Stop after this many rows.

Output

Read: one dataset item per sheet row, keyed by the header row. Empty cells are null; empty rows are skipped. Blank header cells become column_2 and so on, and repeated names get _2, so no value is lost.

Write: one summary item:

{
  "mode": "appendNew",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/<id>/edit",
  "spreadsheetTitle": "Scraped leads",
  "sheetName": "Leads",
  "rowsRead": 120,
  "rowsWritten": 37,
  "rowsAppended": 37,
  "rowsUpdated": 0,
  "rowsSkippedExisting": 83,
  "rowsSkippedWithoutKey": 0,
  "columnsAdded": [],
  "error": null,
  "finishedAt": "2026-10-14T09:12:44Z"
}

The run's status line says the same in words, and the RUN_STATS record adds the details (values cut to Google's 50,000-character cell limit, fields left out, API requests made).

Switching from another Google Sheets actor?

• No Google sign-in screen. Actors that connect through an OAuth app depend on that app staying approved by Google; when it isn't, every run stops at "This app is blocked". A service account is yours and never goes through that screen.
• Your sheet is never wiped by an empty run. replace with an empty dataset leaves the sheet as it was, and a replace overwrites rows in place before trimming, so the sheet is never empty mid-run.
• Appends add only the new rows instead of rewriting the whole sheet, so large sheets stay fast.
• Map your old settings: append → append; replace → replace; "deduplicate by field" → appendNew with keyColumn; read → read; column order → columns.
• A clear reason for every failure: an unshared sheet names the email to share it with, a disabled Sheets API gives the one-click link to turn it on, a missing key column lists the columns that exist.

Run it on a schedule, or from your own code

1. Save your input as a task (Save as a new task, top right of the actor page) and add it to a schedule (Console → Schedules → Create new). A scheduled read turns a sheet your team edits into a fresh dataset every day.
2. Or start it from the API, Make, Zapier or n8n through Apify's integrations.

Can I use Google Sheets Import & Export from an AI agent (MCP)?

Yes, through Apify's MCP server, from Claude, ChatGPT, Cursor or any other MCP client. Add this to your client's MCP configuration; your client signs you in to Apify:

{
  "mcpServers": {
    "apify": {
      "url": "https://mcp.apify.com?tools=humble-echidna/google-sheets"
    }
  }
}

To use an Apify API token instead of signing in, add "headers": {"Authorization": "Bearer <APIFY_TOKEN>"} next to url. An agent reads a shared sheet with {"mode": "read", "spreadsheetUrl": "<link>"}, and writes with a mode, the link, items and your key.

Who it's for

Anyone whose scraped data ends up in a spreadsheet: lead lists that grow every day without duplicates, price and stock tables kept current by key, job or listing feeds a team filters in Sheets, and the reverse, a sheet of URLs or keywords someone edits that feeds a scraper's next run.

Why this one?

• Writes that don't depend on an OAuth app. Your own service account; nothing for Google to block.
• Safe values by default. Values go in exactly as scraped, so text like =IMPORTXML(...) or +1 555 0100 from a web page is stored as text, never run as a formula. Switch to userEntered when you want dates and formulas parsed.
• Duplicate-free and update-in-place modes (appendNew, upsert) built on a key column, with keys compared as text (42 matches "42").
• Fits Google's limits instead of failing on them. Rows go in 2,000 at a time, paced under the default 60 requests a minute; the grid grows before wide rows are written; a write that would pass the 10-million-cell limit is refused before anything changes; long values are cut to the 50,000-character cell limit and counted.
• Honest failures. An append Google doesn't confirm is never silently retried (that could add rows twice); the run stops and says which rows to check.

Limits

• Writing needs a service account key (set up). Without one, only sheets shared as "Anyone with the link" (or published to the web) can be read.
• Up to 1,000,000 rows read per run. Google allows 10 million cells per spreadsheet (all tabs, empty grid cells included) and 50,000 characters per cell.
• appendNew and upsert read the key column first, and the header must be in row 1 for writing.
• Two runs writing to the same tab at the same moment can interleave their rows; run them one after the other.
• If a replace source breaks halfway, the new rows written so far stay above the old rows not yet overwritten; the run fails and says so.
• The Sheets API answers within its own quotas: 60 requests a minute per service account by default (the actor paces itself to 1 a second) and 300 a minute per project. Very frequent runs on one project can hit them; the run waits and retries, then says so.

FAQ

Is it legal to read and write Google Sheets with it?

It uses Google's official, documented Sheets API v4 with your own service account, as Google's Sheets API documentation describes for server-to-server use, on spreadsheets you choose to share with that account. Reading without a key uses the sheet's own CSV or Excel export, which works only for sheets their owner shared with anyone who has the link. Your use is under Google's API terms and your Google account's terms.

Why does it say the service account can't open the sheet?

The sheet isn't shared with the key's client_email. Click Share in the sheet and add that address as an Editor (Viewer is enough for read). The error message names the exact address.

Why does it say the Google Sheets API is turned off?

The Google Cloud project the key belongs to hasn't enabled the API. The message contains the link to its page; click Enable, wait a minute and run again.

What does appendNew count as "already in the sheet"?

A row whose key column value, compared as text with spaces trimmed, matches a value already in that column. 42 and 42.0 match; ABC and abc don't. Rows without a key are skipped and counted. If a key appears twice in the same data, the first one is used.

What happens to cells my data doesn't have?

In append, appendNew and upsert they're left as they are: upsert changes only the columns the row has, so notes your team adds in other columns survive. In replace the whole row is rewritten.

Can it write to a sheet in a shared drive or a Google Workspace domain?

Yes, as long as the sheet is shared with the service account's email. Some Workspace domains don't allow sharing outside the domain; then an admin has to allow it, or use a sheet outside that policy.

Why are my numbers stored as text?

The data has them as text (many scrapers return "19.99" as a string), and raw keeps values as they are. Set How values are entered to userEntered to have Sheets parse them like typed input.

Something that used to work now fails. Why?

The run's status line and RUN_STATS give Google's reason. The usual causes: the key was deleted or disabled in Google Cloud, the sheet was unshared, the tab was renamed, or the spreadsheet reached Google's cell limit. Please open an issue with the input (without your key) if it's none of these.

Related actors

Actor	Use it when
Dataset Transformer	You want to filter, dedupe, rename or sort rows before they go to the sheet
Join Datasets	You want to add fields from a second dataset (VLOOKUP) first
Dataset Diff	You want only the rows that changed since a scraper's last run

Feedback and support

Found a bug, or need a mode that isn't here? Open an issue on the Issues tab with the input you used (never your key).

Versions

Current version: 0.1. See the Changelog tab for what changed in each version.