# Google Sheets Import & Export: Append & Upsert (`drop_data_solutions/google-sheets-io`) Actor

Send an Apify dataset or JSON to a Google Sheet, or pull a tab back into a dataset. Append, upsert by key without touching your own columns, or replace with an automatic backup. Service-account auth for unattended schedules, Google-recommended retries and a 10M-cell check before writing.

- **URL**: https://apify.com/drop_data_solutions/google-sheets-io.md
- **Developed by:** [drop_data_solutions](https://apify.com/drop_data_solutions) (community)
- **Categories:** Integrations, Automation, Developer tools
- **Stats:** 2 total users, 1 monthly users, 66.7% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.01 / 1,000 written rows

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

Send an Apify dataset or JSON to a Google Sheet, or pull a sheet back into a dataset, on a schedule with nobody around to click "Allow". **Google Sheets Import & Export** appends, upserts by key or replaces a tab (with an automatic backup), and exports any tab or A1 range to JSON, CSV or Excel. It's built for unattended runs, like a scraper that finishes at 3 a.m. and has to land its results in the team's sheet.

> Not affiliated with or endorsed by Google. Google Sheets is a trademark of Google LLC.

### What can Google Sheets Import & Export do?

- 📥 **Import (data → sheet):** **append** below existing rows, **upsert** (update rows whose key matches, add the rest), or **replace** (clear the tab and write fresh, after saving a backup).
- 📤 **Export (sheet → dataset):** a whole tab or any A1 range becomes a dataset you can download as JSON, CSV, Excel or HTML. **Public sheets** ("Anyone with the link: Viewer") export with no credentials at all.
- 🔐 **Service-account auth:** no consent screen, and service account keys don't expire unless your Google Cloud organization sets a key-expiry policy, so scheduled runs don't wait on anyone to re-authorize. OAuth (client ID + secret + refresh token) also works.
- 🛡️ **Safe by default:** append only adds rows (and new columns on the right for new fields); it doesn't clear or reorder existing data. Upsert only writes the columns in your data, so your notes and formula columns are left as they are. Replace refuses to run on empty input unless you allow it. Values are written RAW by default, so scraped text like `=IMPORTXML(...)` is stored as text instead of being evaluated as a formula.
- 🔁 **Built to keep running:** paces requests under Google's 60-per-minute quota, retries `429`/`5xx`/network errors with truncated exponential backoff and jitter (honouring `Retry-After`), chunks large writes, and checks the 10,000,000-cell spreadsheet limit **before** writing anything.
- 🧭 **Errors that say what to fix**, e.g. *"Open the sheet, click Share, and add `my-bot@project.iam.gserviceaccount.com` as an Editor."*
- 🧪 **Dry run:** see how many rows would be appended, updated or left unchanged, and which columns would be added, without writing or paying per row.

### Who is it for?

- **Scraper users** who want every run's results in one de-duplicated Google Sheet.
- **Ops, sales and marketing teams** keeping a lead list, price list or report tab fresh without copy-paste.
- **Developers and AI agents** that need a reliable "write to Sheets" step in an Apify pipeline (it works through the Apify API, the Apify MCP server, webhooks and integrations).

### How to export an Apify dataset to Google Sheets

#### Step 1: Create a Google service account (one time, about 5 minutes)

1. Open Google Cloud Console and create or pick a project.
2. **APIs & Services → Library**: search **Google Sheets API** and click **Enable**.
3. **IAM & Admin → Service Accounts → Create service account.** Any name is fine; it needs no roles.
4. Open the account → **Keys → Add key → Create new key → JSON**. A `.json` file downloads.
5. Open your spreadsheet, click **Share**, and add the `client_email` from that file (`name@project.iam.gserviceaccount.com`) as an **Editor**.
6. Paste the whole JSON file into **Service account JSON key**. Apify stores secret inputs encrypted.

The service account can reach only the sheets you share with it. Unshare a sheet to revoke access.

#### Step 2: Send a scraper's results to the sheet after every run

In your scraper's **Integrations** tab, add this Actor and use:

```json
{
  "operation": "import",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
  "sheetName": "Leads",
  "datasetId": "{{resource.defaultDatasetId}}",
  "writeMode": "upsert",
  "keyColumns": ["url"],
  "serviceAccountJson": "<paste the JSON key>"
}
```

Schedule the scraper daily and you get one clean list. Existing rows are updated, new ones are added, and your own "Notes" column is left alone.

#### Step 3: Check before you write (optional)

```json
{ "spreadsheet": "YOUR_ID", "datasetId": "abc123", "writeMode": "upsert", "keyColumns": ["id"], "dryRun": true }
```

### More examples

#### Replace a report tab, choosing and renaming columns

```json
{
  "spreadsheet": "YOUR_ID",
  "sheetName": "Daily report",
  "datasetId": "abc123",
  "writeMode": "replace",
  "columns": ["title", "price.value", "address.city"],
  "headerMap": { "title": "Product", "price.value": "Price", "address.city": "City" }
}
```

#### Paste JSON directly (array of objects, or array of arrays with a header row)

```json
{ "spreadsheet": "YOUR_ID", "data": [["sku", "qty"], ["A-1", 4], ["B-2", 0]] }
```

#### Export a Google Sheet range to JSON or CSV

```json
{ "operation": "export", "spreadsheet": "YOUR_ID", "range": "'Price list'!A1:F", "serviceAccountJson": "<paste the JSON key>" }
```

#### Export a public Google Sheet with no setup

If the sheet's General access is "Anyone with the link: Viewer", leave the credential fields empty. The prefilled example does exactly this with Google's public sample spreadsheet:

```json
{ "operation": "export", "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit", "maxRows": 20 }
```

Without credentials, the Actor reads Google's CSV export, so values come back as displayed text (e.g. `"$1,200.00"`, not `1200`) and `exportFormattedValues` has no effect. Add a service account key to get raw numbers and dates.

### Input

| Field | What it does |
| --- | --- |
| `operation` | `import` (data → sheet, default) or `export` (sheet → dataset). |
| `spreadsheet` | Full URL or ID. `#gid=` in the URL selects the tab. |
| `sheetName` | Tab name. Created automatically on import if missing. |
| `serviceAccountJson` | Service account key (raw JSON or base64). Secret. Required for import; for export only when the sheet isn't public. |
| `oauthClientId` / `oauthClientSecret` / `oauthRefreshToken` | Use instead of a service account if you must. |
| `datasetId` / `data` / `dataUrl` | Exactly one data source when importing. |
| `writeMode` | `append`, `upsert` or `replace`. |
| `keyColumns` | Upsert key field(s); several fields form a composite key. |
| `columns`, `excludeColumns`, `headerMap` | Pick, order and rename columns. Nested fields use dots (`seller.name`). |
| `flattenArrays` | `json` (one cell) or `index` (`tags.0`, `tags.1` …). |
| `parseValuesAsTyped` | Let Google parse dates, currency and formulas (`USER_ENTERED`). Off by default. |
| `range`, `hasHeaderRow`, `skipEmptyRows`, `exportFormattedValues` | Export options. |
| `maxRows`, `offset`, `dryRun` | Limits and safe testing. |
| `requestsPerMinute`, `maxRetries`, `batchRows`, `maxCellsPerRequest` | Advanced reliability settings. The defaults suit almost everyone. |

### Output

- **Import:** the Google Sheet itself, plus an `OUTPUT` record in the run's key-value store with `rowsAppended`, `rowsUpdated`, `rowsUnchanged`, `columnsAdded`, `apiRequests` and `retries`. Replace mode also saves a `BACKUP` record of the old tab.
- **Export:** one dataset item per sheet row, keyed by the header row (blank headers become `column_N`, duplicates `name_2`).
- **On failure:** `OUTPUT` holds `{ "error": { "code", "message", "hint" } }`, and the run's status message says what to fix.

Example `OUTPUT` after an upsert:

```json
{ "mode": "upsert", "sheetName": "Leads", "rowsAppended": 12, "rowsUpdated": 3, "rowsUnchanged": 185, "columnsAdded": ["phone"], "apiRequests": 6, "retries": 0 }
```

### How much does it cost to send data to Google Sheets?

Pay per event, no subscription:

| Event | Price |
| --- | --- |
| Sheet sync (once per successful run) | $0.002 |
| Row written (appended or changed) | $0.01 per 1,000 rows |
| Row exported | $0.01 per 1,000 rows |

Rows are charged only after Google confirms the write. Failed runs don't pay the sync fee; dry runs and unchanged upsert rows cost nothing. A daily sync of 2,000 changed rows costs about $0.022 a day. Set **Maximum cost per run** and the Actor stops cleanly at the limit.

### Use it from the API, Python, JavaScript or an AI agent

```python
from apify_client import ApifyClient

client = ApifyClient("<YOUR_APIFY_TOKEN>")
run = client.actor("drop_data_solutions/google-sheets-io").call(run_input={
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "sheetName": "Leads",
    "datasetId": "YOUR_DATASET_ID",
    "writeMode": "upsert",
    "keyColumns": ["url"],
    "serviceAccountJson": open("service-account.json").read(),
})
print(client.key_value_store(run["defaultKeyValueStoreId"]).get_record("OUTPUT")["value"])
```

It also works from Apify **schedules**, **webhooks/integrations** (run it after any Actor finishes), the **Apify MCP server** for AI agents, and Apify's Make, Zapier and n8n integrations.

### Limits to know

- Google allows 10,000,000 cells per spreadsheet (all tabs combined) and 50,000 characters per cell. Longer values are truncated with a warning.
- Google allows 60 requests per minute per user per project. A service account counts as one user, so other tools sharing the same Google Cloud project share that budget.
- Imports and upserts read the target tab once at the start, so very large tabs take longer.
- The header is row 1.
- Datasets up to 200,000 items are read once and kept in memory. Larger datasets are read twice, so give those runs 1 GB+ of memory.
- Writing always needs credentials. Exporting needs either a public sheet ("Anyone with the link: Viewer") or the sheet shared with the service account (Viewer is enough).

### FAQ

#### Does my Google Sheet need to be public?

No. Share it only with the service account's email. Public sheets are a convenience for export: they can be read without any credentials.

#### Can it write formulas?

Yes. Turn on `parseValuesAsTyped` and send values that start with `=`.

#### Why use a service account instead of OAuth?

Google expires OAuth refresh tokens after 7 days for apps left in "Testing" mode, which breaks scheduled runs. A service account key doesn't expire.

#### My headers have different names than my data fields.

Use `headerMap`, e.g. `{"price.value": "Price"}`. Key columns can use either name.

#### Will re-running create duplicate rows?

Re-running an **append** run adds its rows again, so use **upsert** with a key column for repeated syncs. Within a single run, a retried write request targets the same explicit cell range, so a network retry doesn't add the rows twice.

### Support

Open the **Issues** tab with your run ID. The `OUTPUT` record and the log include an error code that pinpoints the problem. Built and maintained by drop_data_solutions.

# Actor input Schema

## `operation` (type: `string`):

Import writes a dataset/JSON into a sheet (needs credentials). Export reads a tab or range into this run's dataset; public sheets ("Anyone with the link: Viewer") can be exported without credentials. The prefilled example exports Google's public sample sheet.

## `spreadsheet` (type: `string`):

Paste the address bar URL (https://docs.google.com/spreadsheets/d/.../edit). If the URL contains #gid=..., that tab is used when no tab name is set. The prefill is Google's public sample spreadsheet, so a first run works without any setup.

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

Tab to write to or read from. Created automatically on import if missing. Default: the tab in the URL's #gid, else the first tab.

## `serviceAccountJson` (type: `string`):

Paste the full contents of the JSON key file (raw or base64). Stored encrypted by Apify. Required for import; for export it's only needed when the sheet isn't public.

## `oauthClientId` (type: `string`):

Only if you cannot use a service account. Needs client ID, client secret and a refresh token with the spreadsheets scope.

## `oauthClientSecret` (type: `string`):

OAuth client secret.

## `oauthRefreshToken` (type: `string`):

Long-lived refresh token. Note: apps in Google's 'Testing' mode expire refresh tokens after 7 days.

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

An Apify dataset, e.g. a scraper run's default dataset. In an integration/webhook use {{resource.defaultDatasetId}}.

## `data` (type: `array`):

Array of objects (\[{"name":"A"}]) or array of arrays with a header row first (\[\["name"],\["A"]]). Max ~9 MB (Apify input limit); use a dataset for more.

## `dataUrl` (type: `string`):

Public URL returning a JSON array (or {"items": \[...]}).

## `writeMode` (type: `string`):

Append only adds rows (and new columns on the right for new fields); it doesn't clear existing data. Upsert only touches the columns your data has, so your own notes/formula columns are left as they are. Replace saves a BACKUP record first and refuses to run on empty input unless you allow it.

## `keyColumns` (type: `array`):

Field(s) that identify a row, e.g. url or id. Several fields form a composite key. Matching ignores surrounding spaces.

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

Field names from your data, in the order you want. Nested fields use dots: price.value. Default: all fields in first-seen order.

## `excludeColumns` (type: `array`):

Field names to leave out.

## `headerMap` (type: `object`):

Rename fields to sheet headers: {"title": "Product name", "price.value": "Price"}. Key columns may use either name.

## `flattenArrays` (type: `string`):

How arrays inside items become cells. Nested objects are flattened into dotted columns (up to 5 levels deep; anything deeper is stored as JSON text).

## `parseValuesAsTyped` (type: `boolean`):

Off (default): values are stored exactly as sent (RAW), so scraped text starting with '=' is stored as text, not evaluated as a formula. On: Google parses dates, currency and formulas as if you typed them in.

## `allowEmptyReplace` (type: `boolean`):

Replace mode with zero input rows normally stops to protect your sheet. Enable to clear the tab intentionally.

## `backupBeforeReplace` (type: `boolean`):

Save the old tab contents to the key-value store record BACKUP before replacing.

## `range` (type: `string`):

e.g. Leads!A1:F or 'My tab'!B2:H500. Default: the whole tab.

## `hasHeaderRow` (type: `boolean`):

Use the first row as field names (blank headers become column_N, duplicates name_2).

## `skipEmptyRows` (type: `boolean`):

Drop rows where every cell is empty.

## `exportFormattedValues` (type: `boolean`):

On: '$1,234.50' as displayed. Off: raw numbers (1234.5).

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

Stop after this many rows (0 = no limit). Also capped automatically by your max cost per run.

## `offset` (type: `integer`):

Import only: skip this many input items.

## `dryRun` (type: `boolean`):

Connect, read and plan (rows to append/update, columns to add) without writing anything or being charged per row.

## `requestsPerMinute` (type: `integer`):

Google allows 60 per minute per user per project; 55 leaves headroom. Lower it if other tools share your Google Cloud project.

## `maxRetries` (type: `integer`):

Retries for 429/5xx/network errors with exponential backoff (1s, 2s, 4s ... capped at 64s).

## `batchRows` (type: `integer`):

Upper bound of rows per write request.

## `maxCellsPerRequest` (type: `integer`):

Keeps each request near Google's recommended 2 MB payload.

## Actor input object example

```json
{
  "operation": "export",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "writeMode": "append",
  "headerMap": {},
  "flattenArrays": "json",
  "parseValuesAsTyped": false,
  "allowEmptyReplace": false,
  "backupBeforeReplace": true,
  "hasHeaderRow": true,
  "skipEmptyRows": true,
  "exportFormattedValues": false,
  "maxRows": 20,
  "offset": 0,
  "dryRun": false,
  "requestsPerMinute": 55,
  "maxRetries": 8,
  "batchRows": 5000,
  "maxCellsPerRequest": 40000
}
```

# Actor output Schema

## `summary` (type: `string`):

JSON record OUTPUT: mode, sheetName, spreadsheetUrl, row counts, columnsAdded, apiRequests, retries; on failure an error object with code, message, hint.

## `exportedRows` (type: `string`):

One item per sheet row, keyed by the header row. Empty for import runs.

## `backup` (type: `string`):

JSON record BACKUP with the tab's previous rows.

# 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 = {
    "operation": "export",
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
    "headerMap": {},
    "maxRows": 20
};

// Run the Actor and wait for it to finish
const run = await client.actor("drop_data_solutions/google-sheets-io").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 = {
    "operation": "export",
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
    "headerMap": {},
    "maxRows": 20,
}

# Run the Actor and wait for it to finish
run = client.actor("drop_data_solutions/google-sheets-io").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 '{
  "operation": "export",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "headerMap": {},
  "maxRows": 20
}' |
apify call drop_data_solutions/google-sheets-io --silent --output-dataset

```

## MCP server setup

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

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/eZRb6HjFPLyNseeP3/builds/yd9OIYhdyApN2aMib/openapi.json