# Google Sheets Import & Export: Dataset to Sheet and Back (`humble-echidna/google-sheets`) Actor

Write any Apify dataset, pasted JSON or CSV/Excel file to a Google Sheet (append, append only new rows, update by key, replace), or read a sheet into a dataset. Runs after your scraper as an integration. Your own Google service account: no OAuth app to get blocked.

- **URL**: https://apify.com/humble-echidna/google-sheets.md
- **Developed by:** [Michael Costa](https://apify.com/humble-echidna) (community)
- **Categories:** Integrations, Automation, Developer tools
- **Security:** Limited permissions
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.10 / 1,000 row reads

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.

Learn more: https://docs.apify.com/actors/running/actors-in-store.md#pay-per-event

## What's an Apify Actor?

An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes.
In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours,
and optionally produces a well-defined JSON output, datasets with results, or files in key-value store.
In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server.

Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md.

## How to integrate an Actor?

If asked about integration, you help developers integrate Actors into their projects.
You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready.

Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run.

For examples already wired to this Actor's own input schema, see the [API](#api) section below.

Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`).

# README

### 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](https://apify.com/humble-echidna/google-sheets/input-schema) ·
[API](https://apify.com/humble-echidna/google-sheets/api) ·
[Use it from Claude, ChatGPT or Cursor (MCP)](#can-i-use-google-sheets-import-and-export-from-an-ai-agent-mcp)

**Jump to:** [Set up writing](#set-up-writing-3-minutes-once) ·
[After a scraper](#send-every-scraper-run-to-a-sheet-automatically) ·
[Modes](#which-mode-do-i-need) ·
[Price](#how-much-does-it-cost-to-export-a-dataset-to-google-sheets) ·
[Input](#input) · [Output](#output) ·
[Switching](#switching-from-another-google-sheets-actor) ·
[AI agents (MCP)](#can-i-use-google-sheets-import-and-export-from-an-ai-agent-mcp) · [Limits](#limits) · [FAQ](#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](https://console.cloud.google.com/) 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:

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

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

```json
{
  "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

```json
{
  "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](#which-mode-do-i-need)). |
| **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:

```json
{
  "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](https://docs.apify.com/platform/actors/running/tasks) (**Save as a new task**, top right
   of the actor page) and add it to a [schedule](https://docs.apify.com/platform/schedules) (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](https://apify.com/humble-echidna/google-sheets/api), Make, Zapier or n8n through
   [Apify's integrations](https://docs.apify.com/platform/integrations).

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

Yes, through [Apify's MCP server](https://docs.apify.com/platform/integrations/mcp), from Claude, ChatGPT, Cursor or
any other MCP client. Add this to your client's MCP configuration; your client signs you in to Apify:

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

To use an [Apify API token](https://console.apify.com/settings/integrations) 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](#set-up-writing-3-minutes-once)). 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](https://developers.google.com/sheets/api) 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](https://apify.com/humble-echidna/dataset-transform) | You want to filter, dedupe, rename or sort rows before they go to the sheet |
| [Join Datasets](https://apify.com/humble-echidna/dataset-join) | You want to add fields from a second dataset (VLOOKUP) first |
| [Dataset Diff](https://apify.com/humble-echidna/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.

# Changelog

This Actor's version history is a separate document: https://apify.com/humble-echidna/google-sheets/changelog.md

# Actor input Schema

## `mode` (type: `string`):

`read`: the sheet's rows become this run's dataset (one item per row, keyed by the header row). `append`: add the rows below the sheet's last row. `appendNew`: add only rows whose keyColumn value isn't in the sheet yet (no duplicates across runs). `upsert`: update rows whose key is already in the sheet, add the rest. `replace`: the sheet holds exactly these rows afterwards (old rows below are cleared; an empty source leaves the sheet untouched). Every mode except read needs serviceAccountKey.

## `spreadsheetUrl` (type: `string`):

The spreadsheet's address from your browser, e.g. https://docs.google.com/spreadsheets/d/<id>/edit#gid=0 (the gid picks the tab), or just the id. A "Publish to web" link works for reading. The default is Google's public sample sheet (30 students), for a first try.

## `sheetName` (type: `string`):

The tab (sheet) to read or write, e.g. Leads; any case. Empty: the tab in the link's #gid=, else the first tab. When writing to a tab that doesn't exist yet, it's created.

## `serviceAccountKey` (type: `string`):

Your own Google Cloud service account's JSON key file, pasted whole (it contains "type": "service_account"). Needed for writing and for private sheets; share the sheet with the key's client_email as an Editor. Stored encrypted by Apify, sent only to Google. Empty: read a sheet shared as "Anyone with the link". Setup steps: README, "Set up writing".

## `datasetId` (type: `string`):

The rows to write: the id or name of one of your Apify datasets, e.g. a scraper's results (or pick one in the form). To run this actor after a scraper in an integration, `{{resource.defaultDatasetId}}`: the finished run's dataset is used, and this field is what gives the run access to it. Read-only access. Ignored in read mode.

## `items` (type: `array`):

Rows to write, as a JSON array of objects, e.g. \[{"name": "Ada", "city": "London"}]. Used when datasetId is empty.

## `fileUrl` (type: `string`):

Rows to write from a file: a direct public link (http/https, ports 80 and 443) to a CSV, JSON, JSONL or Excel (.xlsx) file, up to 200 MB. Used when no dataset is picked and no rows are pasted.

## `keyColumn` (type: `string`):

For appendNew and upsert: the column that identifies a row, by its header name, e.g. url, id or email (any case; dots reach nested fields like address.postalCode). Rows without a value in it are skipped. Values compare as text, so 42 and "42" match.

## `columns` (type: `array`):

Optional: the fields to write, in this order (e.g. title, url, price; dots reach nested fields). Empty: every field. Fields already in the sheet's header row keep their column; others are added on the right.

## `addNewColumns` (type: `boolean`):

When the rows have fields that the sheet's header row doesn't, add them as new columns on the right (default). Off: those fields are left out and counted in the run's status.

## `flatten` (type: `boolean`):

Nested objects and lists become their own columns: address.city, tags.0 (default). Off: they go into one cell as JSON text.

## `valueInput` (type: `string`):

`raw` (default): values go in exactly as they are; text stays text, so a scraped "=SUM(...)" or "+1 555" is never run as a formula. `userEntered`: values are parsed as if typed into Sheets (dates, percentages, formulas).

## `headerRow` (type: `integer`):

For read: the row number that holds the column names, e.g. 3 when a title sits above the table. Rows above it are skipped. Default 1.

## `readValues` (type: `string`):

For read with a service account key: `formatted` (default) gives values as the sheet shows them (text such as "$1,200.00"); `unformatted` gives the underlying numbers and booleans.

## `maxRows` (type: `integer`):

Stop after this many rows read or written, e.g. 1000. Empty (the default): no limit. The run also stops cleanly at the maximum cost per run you set in the run options.

## Actor input object example

```json
{
  "mode": "read",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "addNewColumns": true,
  "flatten": true,
  "valueInput": "raw",
  "readValues": "formatted"
}
```

# Actor output Schema

## `results` (type: `string`):

No description

## `runStats` (type: `string`):

No description

# API

You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup.

## JavaScript example

```javascript
import { ApifyClient } from 'apify-client';

// Initialize the ApifyClient with your Apify API token
// Replace the '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

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

// Run the Actor and wait for it to finish
const run = await client.actor("humble-echidna/google-sheets").call(input);

// Fetch and print Actor results from the run's dataset (if any)
console.log('Results from dataset');
console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`);
const { items } = await client.dataset(run.defaultDatasetId).listItems();
items.forEach((item) => {
    console.dir(item);
});

// 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs

```

## Python example

```python
from apify_client import ApifyClient

# Initialize the ApifyClient with your Apify API token
# Replace '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

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

# Run the Actor and wait for it to finish
run = client.actor("humble-echidna/google-sheets").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print(f"💾 Check your data here: https://console.apify.com/storage/datasets/{run.default_dataset_id}")
for item in client.dataset(run.default_dataset_id).iterate_items():
    print(item)

# 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start

```

## CLI example

```bash
echo '{
  "mode": "read",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
}' |
apify call humble-echidna/google-sheets --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,humble-echidna/google-sheets"
        }
    }
}
```

The hosted server signs you in with OAuth on first connect, so no API token belongs in this config. Clients without OAuth support can send an `Authorization: Bearer <APIFY_API_TOKEN>` header instead, using a token from API & Integrations in Apify Console (https://console.apify.com/settings/integrations).

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/w2GDi57YRzcfzi7JC/builds/LSRd4oyO5WpXNAmUr/openapi.json