Google Sheets Import Export

Returns: one item per read or written row with selected column fields, id, sourceUrl, _spreadsheetId, _sheetName, _sheetGid, _rowNumber. Write previews add _dryRun and _writeError. Input: {"mode": "read", "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0", "maxItems": 20} Best for: Google Sheets API reads into datasets, syncing scraper output to a sheet, paging through large tabs.

{
    "Student Name": "Alexandra",
    "Major": "English",
    "_sheetName": "Class Data",
    "_rowNumber": 2,
    "id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms:0:2",
    "sourceUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
}

For agents: set mode explicitly. Check _dryRun, _writeError, and OUTPUT.rowsWritten before confirming writes. Empty reads include corrective guidance.

What does Google Sheets Import Export do?

Google Sheets Import Export moves rows between Google Sheets and Apify in both directions.

• Import (read mode): it reads one tab of a spreadsheet, or an A1 range of it, and saves one dataset item per sheet row. The header cells become the field names. Numbers and true/false cells come out as numbers and booleans, not text.
• Export (write modes): it takes the items of an Apify dataset, or a JSON array you paste in, and appends them to a tab, replaces the tab's contents, or appends only rows that aren't there yet (dedupe on key columns you choose).

Sheets shared as "Anyone with the link" can be read with no credentials. Private sheets and all writes go through the official Google Sheets API with a service-account key that you supply. There is no OAuth popup and no browser login, so the Actor runs well on a schedule, from the API, or as the last step of another Actor's workflow.

To try it, open the Input tab and click Start. The form is prefilled with Google's public example spreadsheet (a list of students), so you get 20 rows back in a few seconds.

This Actor is an independent tool. It is not made, sponsored or endorsed by Google.

What data can you get from Google Sheets?

In read mode every dataset item holds all the columns of one sheet row plus a few fields that describe where the row came from. In write modes every item holds the cell values exactly as they were sent to the sheet (after flattening and formula escaping) plus the same location fields.

Field	Type	Description
(one field per header cell)	text, number or boolean	The row's cell values, keyed by the header text, e.g. Student Name, Major. A blank header cell becomes column_C style names.
id	text	Stable record ID: spreadsheetId:tabGid:rowNumber, e.g. 1BxiMVs0…upms:0:2. If public tab discovery is unavailable, a supplied tab name replaces the gid.
sourceUrl	link	Link to the tab, e.g. https://docs.google.com/spreadsheets/d/<id>/edit#gid=0. Without a discovered gid, it is a query link to the named tab.
_spreadsheetId	text	Google spreadsheet ID.
_sheetName	text	Tab name from public tab discovery or your input; gid:N when neither provides a name.
_sheetGid	number	Numeric tab ID (gid), when known.
_rowNumber	number	1-based row number in the tab. In write modes, the row the item was written to.
_dryRun	boolean	Write modes only: true when the row was planned but not written.
_writeError	text	Write modes only: why nothing was written (the row is then a dry-run preview). null otherwise.

Write modes also save a run summary as the OUTPUT record in the key-value store (mode, rowsRead, rowsWritten, rowsSkippedAsDuplicate, chargedEvents, updatedRange, dryRun, writeError).

Download results as JSON or CSV from Storage or the API.

How to use Google Sheets Import Export

Read a public Google Sheet

1. In Google Sheets, click Share and set General access to Anyone with the link – Viewer.
2. Copy the sheet's URL from the browser. Keep the #gid=… part if you want a specific tab.
3. Paste it into Spreadsheet URL or ID, keep Mode on read and click Start.
4. Open the Output tab to see one row per item, or download the dataset.

Read a private Google Sheet or write to any sheet

1. In the Google Cloud console, create a project (or use an existing one) and enable the Google Sheets API.
2. Create a service account and add a JSON key. Google downloads a .json file.
3. Open the file and copy its client_email value (it looks like name@project.iam.gserviceaccount.com).
4. In Google Sheets, click Share and add that email: Viewer to read, Editor to write.
5. Paste the whole JSON file into the Service-account JSON key field. The field is stored as an Apify secret and is never logged or written to the output.
6. Choose a mode, fill in datasetId or rawData for write modes, and click Start.

Write access is checked first. Default failures return a preview; see onWriteError.

Export another Actor's results automatically

Add this Actor as an integration on the other Actor (Integrations tab → "Run Actor") with an input like this:

{
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/<your sheet id>/edit",
    "serviceAccountKey": "<your service-account JSON key>",
    "mode": "append-dedupe",
    "dedupeFields": ["url"],
    "datasetId": "{{resource.defaultDatasetId}}"
}

Omitted datasetId uses the triggering dataset from resource.defaultDatasetId or payload.resource.defaultDatasetId. Omitted mode infers append from source rows, or append-dedupe with dedupeFields.

A source with 0 items is not an error: the run ends successfully with the message "Source dataset had 0 items; nothing written. Choose a populated datasetId or provide a non-empty rawData array.", rowsWritten: 0 in OUTPUT, and no charge.

Input

All fields are on the Input tab. Only Spreadsheet URL or ID is required.

Field	Type	Default	What it does
spreadsheetUrl	text	– (required)	Sheet URL or bare spreadsheet ID. Account-specific /u/0/d/ links, Google open?id= links and links without https:// are accepted. A #gid= or ?gid= selects the tab.
mode	read, append, replace, append-dedupe	see note	Read rows into the dataset, or write rows to the sheet. When omitted: append (or append-dedupe with dedupeFields) if datasetId or rawData is given, otherwise read.
excludeColumns	list	–	Drop these columns, in all modes. Applied after columns.
sheetName	text	tab from URL, else first tab	Tab name. Overrides the gid. Write modes create the tab when it doesn't exist.
range	text	whole tab	A1 range to read, e.g. A1:C10 or Class Data!A1:C10.
maxItems	integer	1000 (read), 0 (write)	Maximum rows to read or to take from the source. 0 means no limit.
offset	integer	0	Read mode: data rows to skip after the header, before maxItems applies.
columns	array	all	Keep only these columns, in this order (read: header names; write modes: item keys).
headerRow	integer	1	Row that holds the column names when reading.
serviceAccountKey	secret text	–	Full service-account JSON key. Needed for private sheets and every write.
datasetId	text	–	Apify dataset to export (write modes).
rawData	JSON array	–	Objects to write instead of a dataset.
dedupeFields	list	–	Key columns for append-dedupe.
columnsOrder	list	first-seen order	Header order for a tab that has no header yet.
flattenNested	boolean	true	Nested objects become a/b columns and arrays a/0, a/1.
escapeFormulas	boolean	true	Text starting with =, +, - or @ gets a leading apostrophe.
valueInputOption	USER_ENTERED, RAW	USER_ENTERED	Let Google parse values as if typed, or store them exactly as sent.
dryRun	boolean	false	Plan the write and output the rows without changing the sheet.
onWriteError	preview, fail	preview	When the sheet can't be written: output a dry-run preview, or fail the run.

Read example:

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

Write example (append only new URLs):

{
    "mode": "append-dedupe",
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit",
    "sheetName": "leads",
    "serviceAccountKey": "<contents of your key file>",
    "rawData": [
        { "url": "https://a.example", "title": "A" },
        { "url": "https://b.example", "title": "B" }
    ],
    "dedupeFields": ["url"]
}

Invalid input stops the run right away with a clear message, for example a negative maxItems, an unknown mode, a URL that isn't a Google Sheets link in read mode, or a write mode with neither datasetId nor rawData.

Output

A row from the public example sheet, read with the prefilled input (real output from a local run):

{
    "Student Name": "Alexandra",
    "Gender": "Female",
    "Class Level": "4. Senior",
    "Home State": "CA",
    "Major": "English",
    "Extracurricular Activity": "Drama Club",
    "_spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "_sheetName": "Class Data",
    "_sheetGid": 0,
    "_rowNumber": 2,
    "id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms:0:2",
    "sourceUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
}

A replace run with rawData [{"name": "x", "nested": {"a": 1}, "tags": ["t1", "t2"], "f": "=1+1"}] and no usable key (real output from a local run). It shows flattening (nested/a, tags/0), formula escaping ('=1+1) and the dry-run preview:

{
    "name": "x",
    "nested/a": 1,
    "tags/0": "t1",
    "tags/1": "t2",
    "f": "'=1+1",
    "_spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "_sheetName": "golden-replace",
    "_sheetGid": null,
    "_rowNumber": 2,
    "_dryRun": true,
    "_writeError": "serviceAccountKey is not valid JSON. Paste the full contents of the service-account JSON key file.",
    "id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms:golden-replace:2",
    "sourceUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit"
}

When the write succeeds, _dryRun is false, _sheetGid holds the tab's gid, _writeError is null, rowsWritten in the OUTPUT record counts the rows written and updatedRange shows the last range Google reported, e.g. 'golden-replace'!A2:E2.

The key-value store also holds OUTPUT (the run summary).

Limited reads set OUTPUT.truncated: true. Raise maxItems or use 0 for all rows.

How much does it cost to use Google Sheets Import Export?

The Actor uses pay-per-event pricing. You pay one result event per row read into the dataset in read mode, or written to the sheet in write modes. Dry runs, preview runs and rows skipped as duplicates are free. See the Pricing tab for the current price. Runs use three HTTP requests per public tab, no browser. The JSON request returns only column types; CSV retains the full cell values. Explicit tab reads overlap optional tab discovery, with public requests paced at one per second. Network retries have a bounded deadline. Google's own Sheets API quota is free for normal use.

Use with the API

You can start the Actor from any language through the Apify API, and connect it to Make, Zapier, n8n, Slack, webhooks and other Actors from the Integrations tab.

Python

import os
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])
run = client.actor("axiomworks/google-sheets-import-export").call(run_input={
    "mode": "read",
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
    "maxItems": 20,
})
for row in client.dataset(run["defaultDatasetId"]).iterate_items():
    print(row["_rowNumber"], row["Student Name"])

JavaScript

import { ApifyClient } from 'apify-client';

const client = new ApifyClient({ token: process.env.APIFY_TOKEN });
const run = await client.actor('axiomworks/google-sheets-import-export').call({
    mode: 'append',
    spreadsheetUrl: 'https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit',
    sheetName: 'results',
    serviceAccountKey: process.env.GOOGLE_SA_KEY,
    datasetId: '<dataset id to export>',
});
const summary = await client.keyValueStore(run.defaultKeyValueStoreId).getRecord('OUTPUT');
process.stdout.write(JSON.stringify(summary.value));

cURL

curl -X POST "https://api.apify.com/v2/acts/axiomworks~google-sheets-import-export/run-sync-get-dataset-items" \
  -H "Authorization: Bearer $APIFY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"mode": "read", "spreadsheetUrl": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "sheetName": "Class Data", "range": "A1:C10"}'

Use with AI agents (MCP)

AI agents can call this Actor through the Apify MCP server. Add it to Claude Desktop, Cursor, VS Code or any MCP client:

{
    "mcpServers": {
        "google-sheets": {
            "url": "https://mcp.apify.com?tools=axiomworks/google-sheets-import-export"
        }
    }
}

The server asks you to sign in with your Apify account the first time. Example prompts:

• "Read the first 50 rows of https://docs.google.com/spreadsheets/d/…/edit#gid=0 and tell me which majors appear most often."
• "Read the tab 'Q3 leads' of my sheet and list the rows where Status is empty."
• "Append these five companies to the 'prospects' tab, skipping any whose website is already in the sheet."
• "Do a dry run of replacing the 'export' tab with dataset aBcD1234 and show me the first three planned rows."

For write prompts, the agent needs your service-account key. Store it once in an Apify task and have the agent run the task, rather than pasting the key into a chat.

Use it from an AI agent

Use Apify's hosted MCP server at https://mcp.apify.com with OAuth and tool axiomworks/google-sheets-import-export. Set mode explicitly. Inspect uncharged RUN_SUMMARY: one entry per spreadsheet input with inputIndex, status (ok, 0-items, blocked, invalid) and a short reason. Write previews retain dataset rows but have no actual writes; check OUTPUT.rowsWritten before confirming export. Explicit onWriteError: fail still fails the run.

Cap API spend with the run option maxTotalChargeUsd, separate from the maxItems row budget:

const run = await client.actor('axiomworks/google-sheets-import-export').call({
    mode: 'read',
    spreadsheetUrl: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    maxItems: 20,
}, { maxTotalChargeUsd: 1 });
const outcome = await client.keyValueStore(run.defaultKeyValueStoreId).getRecord('RUN_SUMMARY');

FAQ

Do I need a Google account or OAuth app?

Not for reading sheets shared as "Anyone with the link". For private sheets and for writing you need a Google Cloud service account, which is free to create. There is no OAuth consent screen and no refresh token that expires.

Why does my run say "Sheet is private or does not exist"?

Google answers a request for a private sheet as if the sheet didn't exist. Either share the sheet as "Anyone with the link – Viewer", or provide serviceAccountKey and share the sheet with the key's client_email.

My write run succeeded but nothing changed in the sheet. Why?

Look at _writeError on the items, writeError in the OUTPUT record, and at the run's status message. With onWriteError set to preview (the default), a missing or rejected key, or a sheet not shared with the service account, turns the run into a dry-run preview instead of failing. Set onWriteError to fail if you prefer the run to fail.

How does append-dedupe decide what is a duplicate?

It joins the values of the dedupeFields columns into a key, compares it with every row already in the tab and with rows earlier in the same batch, and skips matches. The number of skipped rows is rowsSkippedAsDuplicate in the summary.

Are there size limits?

Google allows 10 million cells per spreadsheet and 50,000 characters per cell. The Actor cuts longer cell text down to 50,000 characters, and refuses a write that would push the spreadsheet past the cell limit, before writing anything. Large exports are sent in chunks of up to 2,000 rows.

Is it legal to scrape Google Sheets?

This Actor doesn't scrape anyone else's content. It reads sheets that are already shared publicly by their owner, or sheets you have explicitly shared with your own service account, and it writes only to sheets you have given it Editor access to. Spreadsheets can contain personal data. If yours do, you are responsible for handling that data lawfully, for example under GDPR. This Actor is not affiliated with, endorsed by or sponsored by Google. Use the Actor only with spreadsheets you are allowed to access and change.

Feedback

Found a bug, or need another mode or option? Open an issue on the Issues tab of this Actor with the run ID and a short description, and we'll look into it.