# Google Sheets Import & Export: Append & Dedupe (`ctriolab/google-sheets-import-export`) Actor

Export any Apify dataset to Google Sheets or import a sheet to JSON/CSV. Append or replace, skip duplicates by a key column, map columns. Service account key (no expiring login), clear setup errors, public sheets without a key.

- **URL**: https://apify.com/ctriolab/google-sheets-import-export.md
- **Developed by:** [Ctrio Lab](https://apify.com/ctriolab) (community)
- **Categories:** Integrations, Automation
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.03 / 1,000 row read or writtens

This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events.
Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have.

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

## Google Sheets Import & Export

Send any Apify dataset to Google Sheets, or read a Google Sheet into a dataset. Built to not fail silently: your key, sheet access, tab, range and Google's limits are checked before anything is written, and every error tells you exactly what to fix.

- **Export**: append or replace rows in a tab from an Apify dataset (for example the results of another Actor run) or from inline JSON.
- **Import**: read a whole tab or an A1 range into the run's dataset (JSON, CSV, Excel, API).
- **Skip duplicates** on append by a key column (url, id, email...). Duplicates are not written and not charged.
- **Column mapping**: keep only some columns, set their order, rename them.
- **No OAuth token that expires**: you use a Google service account key, so scheduled runs keep working.
- **Public sheets without any key** (read only).

### Why this Actor?

Most failed Google Sheets runs are setup problems: an expired Google login, a sheet that is not shared, a wrong ID, or a sheet that hits Google's cell limit. This Actor is designed around them:

| Situation | What this Actor does |
|---|---|
| Scheduled runs for months | Service account key: no browser login, nothing expires |
| The sheet is not shared | The error tells you which email to share the sheet with |
| Appending to a big sheet | Append only writes the new rows; existing rows and columns are not touched |
| The data would not fit | Checks Google's 10,000,000 cell limit **before** writing anything |
| A replace run fails half way | New data is written first, old rows are cleared after, so the sheet is never left empty |
| 429 "Quota exceeded" from Google | Waits and retries with exponential backoff (honours Retry-After) |
| `1,1` or `=SUM()` in scraped text | Values are written as-is by default (no formula injection) |

### Set up in 3 minutes

A Google service account is a robot Google account that only has access to the sheets you share with it.

You need this for export and for private sheets. It is free.

1. Open [Google Cloud Console](https://console.cloud.google.com/), create or pick a project, and **enable the Google Sheets API**: [APIs & Services > Library > Google Sheets API > Enable](https://console.cloud.google.com/apis/library/sheets.googleapis.com).
2. Go to **IAM & Admin > Service Accounts > Create service account**. A name is enough, no roles are needed. Open the new account > **Keys > Add key > Create new key > JSON**. A `.json` file downloads.
3. Open the JSON file in a text editor and paste **all of it** into the *Service account JSON key* field. Apify stores it encrypted.
4. In Google Sheets click **Share** and add the `client_email` from the JSON file (looks like `name@project.iam.gserviceaccount.com`) as **Editor** (Viewer is enough for import). Untick "Notify people".

That's it. The same key works for every sheet you share with that email.

> Your organization blocks service account keys? Some Google Workspace admins enforce the policy `iam.disableServiceAccountKeyCreation`. Ask the admin, or use a project in a personal Google account and share the sheet with it.

### Quick start

#### Export the results of another Actor run

Add this Actor as an **integration** to your scraper (Integrations > Connect Actor) and set *Dataset to export* to `{{resource.defaultDatasetId}}`. Every finished run then lands in your sheet.

```json
{
  "mode": "export",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/<ID>/edit",
  "serviceAccountKey": "{ ...contents of your JSON key... }",
  "sheetName": "Leads",
  "datasetId": "{{resource.defaultDatasetId}}",
  "writeMode": "append",
  "dedupeKey": "url"
}
```

#### Import a sheet

```json
{
  "mode": "import",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/<ID>/edit#gid=0",
  "range": "A1:F500"
}
```

Without a key this works when the sheet is shared as **Anyone with the link can view**. The `#gid=` in the URL picks the tab. With a key you can use *Tab name* and private sheets.

#### Call it from code

```bash
curl -X POST "https://api.apify.com/v2/acts/ctriolab~google-sheets-import-export/run-sync-get-dataset-items?token=<APIFY_TOKEN>" \
  -H "Content-Type: application/json" \
  -d '{"mode":"import","spreadsheet":"https://docs.google.com/spreadsheets/d/<ID>/edit#gid=0"}'
```

### How the data is laid out

- **Nested fields** become columns with `/`: `{"address": {"city": "Paris"}, "tags": ["a","b"]}` becomes `address/city`, `tags/0`, `tags/1` (same convention as other Apify tools).
- **Append**: the sheet's header row stays as it is. Rows are matched to columns by header name. New fields are added as new columns at the right end (turn off *Add new columns* to skip them).
- **Replace**: writes header + rows from A1, then clears any old rows below and columns to the right.
- **Inline rows** can be objects `[{"name":"A"}]` or arrays with a header row first `[["name"],["A"]]`.
- Cells longer than 50,000 characters (Google's limit) are cut, and the run log says how many.
- **Import** returns one item per row with the header names as keys. Empty rows are skipped. Duplicate or empty header names become `Name_2`, `C`.

The run summary (rows written, duplicates skipped, added columns, target range, a link to the tab) is saved as `RUN_SUMMARY` in the key-value store.

### Pricing

Pay per event, you only pay for rows that are actually read or written:

| Event | Price |
|---|---|
| Actor start | $0.002 per run |
| Row read or written | $0.05 per 1,000 rows |

Example: a daily export of 2,000 rows costs $0.102 per run (Apify plan discounts lower the row price further). Duplicates skipped by *Skip duplicates* are free. If you set a maximum cost per run, the Actor writes only as many rows as fit and says so in the log, it never writes rows it cannot charge.

### Limits

- Google allows about 60 write requests per minute per user. This Actor writes up to ~1.5 MB per request (thousands of rows) and retries on 429, so large exports are fine.
- A Google spreadsheet can hold 10,000,000 cells across all tabs (empty grid cells count too).
- Apify input is limited to 9 MB: put large data in a dataset rather than *Inline rows*.
- *Append* reads the existing tab once to find the last row, the header and the duplicate keys. Very large tabs (millions of cells) make this step slower.

### Troubleshooting

| Message | Fix |
|---|---|
| "The service account has no access to this spreadsheet" | Share the sheet with the `client_email` shown in the message |
| "The Google Sheets API is not enabled" | Open the link in the message, click Enable, wait 1 to 2 minutes |
| "is an OAuth client file" / "contains an API key" | You pasted the wrong file. Create a **service account** JSON key (step 2) |
| "This file is an Excel (.xlsx) file" | In Sheets: File > Save as Google Sheets, use the new URL |
| "Could not read the spreadsheet as a public sheet" | Share as "Anyone with the link can view", or add a key |
| "Tab ... not found. Tabs in this spreadsheet: ..." | Use one of the listed names (import does not create tabs) |
| "Dataset ... not found" | Use the dataset ID (Storage > Datasets, or `defaultDatasetId` of a run), not the run ID |

### Switching from lukaskrivka/google-sheets

Keep your input JSON. Change the Actor name to `ctriolab/google-sheets-import-export` and add **one field**, `serviceAccountKey` ([3 minute setup](#set-up-in-3-minutes)). The Actor recognises the old format, translates it, and logs what it did.

Before (lukaskrivka/google-sheets, connected Google account):

```json
{
  "mode": "append",
  "spreadsheetId": "1yKx...Oz0w",
  "range": "Leads",
  "datasetId": "{{resource.defaultDatasetId}}",
  "deduplicateByField": "url"
}
```

After (this Actor, same input plus the key):

```json
{
  "mode": "append",
  "spreadsheetId": "1yKx...Oz0w",
  "range": "Leads",
  "datasetId": "{{resource.defaultDatasetId}}",
  "deduplicateByField": "url",
  "serviceAccountKey": "{ ...contents of your JSON key... }"
}
```

Remember to share the sheet with the service account email. For `publicSpreadsheet: true` reads you do not need a key at all.

| Old field | What happens here |
|---|---|
| `mode: "append"` / `"replace"` | Export with append / replace |
| `mode: "read"` | Import: rows go to the dataset **and** to the `OUTPUT` record, as before |
| `mode: "modify"`, `"load backup"` | Not supported, the run stops with an explanation |
| `spreadsheetId`, the spreadsheet picker | Used as the spreadsheet |
| `range` (`Sheet1`, `Sheet1!A1:D100`, `A1:D100`) | Tab and range. Export always writes from A1 of the tab |
| `datasetId`, `rawData`, `limit`, `offset` | Same meaning |
| `deduplicateByField` | Same field is the key. Existing rows in the sheet are never changed or removed |
| `deduplicateByEquality` | Rows equal in every column to an existing or earlier row are skipped |
| `columnsOrder` | These columns first, then the rest alphabetically (as before). Arrays from `rawData` keep their order |
| `keepSheetColumnOrder` | Existing header order is kept. Append always keeps it |
| `publicSpreadsheet` | Read without a key. The tab in `range` is found by name |
| Connected Google account (`oAuthAccount...`, `tokensStore`) | Replaced by `serviceAccountKey`. Without it the run stops at once and links the setup |
| `googleCredentials` | Used if it is a service account key, otherwise treated as a Google account connection |
| `transformFunction` | Ignored with a warning: this Actor does not run code from the input |
| `createBackup`, `backupStore` | Ignored with a warning. Replace never leaves the sheet empty |

Measured by us: all 26 of our own runs with valid input succeeded (export append and replace, dedupe, import of private and public sheets, legacy input; 2026-10-23 to 2026-10-24). Inputs with a setup error stop within about 2 seconds with a message that names the fix.

### FAQ

**Is my key safe?** The field is marked secret, so Apify stores it encrypted and hides it in the UI and API. The Actor only uses it to get a one-hour token from Google for the Sheets scope and never logs it. You can delete the key in Google Cloud at any time.

**Can it write to a sheet I don't own?** Only if the owner shares it with your service account email.

**Does it support Excel files?** Only real Google Sheets. Convert .xlsx with File > Save as Google Sheets.

**Can I use it from AI agents (MCP)?** Yes, like any Apify Actor, via the [Apify MCP server](https://mcp.apify.com/).

Made by [Ctrio Lab](https://ctriolab.com). Issues and feature requests: the Issues tab of this Actor.

# Actor input Schema

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

Import reads rows from a sheet into this run's dataset. Export writes rows from an Apify dataset (or inline JSON) into a sheet. The legacy values append, replace and read accept input written for lukaskrivka/google-sheets (README: Switching from lukaskrivka/google-sheets).

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

Paste the address bar URL of the sheet (https://docs.google.com/spreadsheets/d/<ID>/edit#gid=...) or just the ID. For public import without a key, the #gid=... part picks the tab.

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

Required for export and for private sheets. Paste the whole JSON key file of a Google Cloud service account, then share the sheet with its client_email (Editor for export, Viewer for import). Stored encrypted. Leave empty to import a sheet shared as "Anyone with the link can view". Setup steps are in the README.

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

Tab (sheet) to read or write. Empty: the tab from #gid in the URL, else the first tab. Export creates the tab if it does not exist. Needs a service account key.

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

Optional A1 range for import, e.g. A1:F500, A:D or 2:100. Empty: the whole tab. The first row of the range is the header when 'First row is header' is on.

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

Apify dataset to write to the sheet. In an integration after another Actor use {{resource.defaultDatasetId}}. Nested fields become columns like address/city, tags/0.

## `rows` (type: `array`):

Instead of a dataset: a JSON array of objects \[{"name":"A"}] or of arrays with a header row first \[\["name"],\["A"]]. Apify inputs are limited to 9 MB, use a dataset for more.

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

Append keeps existing rows and columns in place and adds new rows at the bottom. Replace writes the new table from A1 and then clears the rest of the tab.

## `dedupeKey` (type: `string`):

Column name (e.g. url or id). Rows whose value already exists in this column of the sheet, or appears twice in the new data, are not written and not charged. Rows with an empty value are always written.

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

When the data has fields that the sheet header does not have, add them as new columns at the right end. Off: such fields are skipped.

## `parseValues` (type: `boolean`):

Off (recommended): values are written exactly as given, text that starts with = is not run as a formula. On: Google parses values like typed by a user (dates, percentages, formulas).

## `headerRow` (type: `boolean`):

Import: use the first row as field names (else A, B, C...). Export: write a header row (else data only).

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

Optional. Field names to keep, in this order. Import: sheet header names. Export: dataset field names (nested as a/b). Empty: all columns.

## `columnNames` (type: `object`):

Optional mapping from source field to new name, e.g. {"price": "Price (USD)", "url": "Link"}.

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

Formatted returns text like "$1,200.00". Raw returns 1200. Public import (no key) always returns formatted text.

## `maxItems` (type: `integer`):

Stop after this many rows (0 = no limit). Also caps cost.

## Actor input object example

```json
{
  "mode": "import",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "writeMode": "append",
  "addNewColumns": true,
  "parseValues": false,
  "headerRow": true,
  "readValues": "formatted",
  "maxItems": 0
}
```

# Actor output Schema

## `rows` (type: `string`):

Rows read from the sheet (import mode). Empty in export mode.

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

Rows read or written, duplicates skipped, added columns, target range and sheet link.

# 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": "import",
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
};

// Run the Actor and wait for it to finish
const run = await client.actor("ctriolab/google-sheets-import-export").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": "import",
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
}

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

```

## MCP server setup

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

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/EBf776nfR9rdF8cmf/builds/EEfOvqAp7pvZjLywY/openapi.json