# Google Sheets Import & Export – Read, Append & Dedupe Rows (`openharvest/google-sheets-import-export`) Actor

Google Sheets import and export for Apify: read any sheet into a dataset, or append, replace and dedupe-append dataset or JSON rows into Google Sheets. Service-account key (no OAuth pop-ups) or keyless read of public sheets. Connect any Actor via datasetId.

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

## Pricing

$0.05 / 1,000 row moveds

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

## Google Sheets Import & Export – Read, Append & Dedupe Rows

Google Sheets import and export for Apify: read any Google Sheet into a dataset, or append, replace and dedupe-append rows from an Apify dataset or JSON into Google Sheets. It uses a Google service-account key instead of OAuth pop-ups, and public sheets can be read without any key.

**Price: $0.05 per 1,000 rows** read or written. Errors and skipped duplicates are free.

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

It moves rows between Google Sheets and Apify in four modes:

| Mode | What happens |
|---|---|
| `read` | Sheet → dataset. Row 1 is the header and each following row becomes one dataset item. |
| `append` | Dataset/JSON → sheet. Rows are added under the existing data. New fields become new columns at the right. |
| `replace` | Clears the tab and writes the header and all rows from scratch. |
| `dedupe-append` | Appends only rows whose key column (e.g. `url` or `id`) is not in the sheet yet. You can run it on a schedule and never get duplicates. |

- **Service-account authentication.** You paste a JSON key once. It is stored as an encrypted secret input and used only while the run is active. There is no OAuth consent screen and no refresh token that can expire.
- **Keyless read of public sheets.** If a sheet is shared as *Anyone with the link can view*, you only need the link.
- **Works with any Actor.** Pass `datasetId` (for example `{{resource.defaultDatasetId}}` in an integration) and the results of a scraper go straight into your sheet.
- **Flattening.** Nested objects become columns such as `author.name`. Lists of simple values are joined with `; ` and lists of objects are stored as JSON text. Cells are cut at Google's 50,000-character limit, so they never cause an error.
- **Tabs.** Pick a tab by name or by the `#gid=` in the link. A missing tab is created automatically in write modes.
- **No formula injection by default.** Values are written `RAW`, so text that starts with `=` is not run as a formula. Switch to `USER_ENTERED` if you want Google to parse dates, numbers and formulas.

### Use cases

- **Export scraper results to Google Sheets** after every run: product prices, leads from public directories, news, jobs, reviews.
- **Build a growing, de-duplicated list.** Schedule a scraper with `dedupe-append` on `url`, and only new items are added each day.
- **Use a sheet as input.** Read a list of URLs, keywords or product IDs that your team keeps in Google Sheets, then feed the dataset to another Actor.
- **Share dashboards.** `replace` mode keeps a tab in sync with the latest run for Looker Studio or for colleagues.
- **AI agents.** Let an agent read a sheet or write its findings to one through the Apify MCP server.

### How to set up a service account (needed only for writing or for private sheets)

1. Open Google Cloud Console and create (or pick) a project.
2. **APIs & Services → Library →** enable **Google Sheets API**.
3. **IAM & Admin → Service accounts → Create service account.** No roles are needed.
4. Open the service account, go to **Keys → Add key → Create new key → JSON**, and a `.json` file is downloaded.
5. Open your spreadsheet → **Share** → add the service account e-mail (`name@project.iam.gserviceaccount.com`) as **Editor**.
6. Paste the whole content of the JSON file into **Service account key** in this Actor's input.

The service account can access only the spreadsheets you share with it, and nothing else in your Google account.

### Input

| Field | Type | Description |
|---|---|---|
| `spreadsheetUrl` | string | Sheet link or ID. A `#gid=` in the link selects the tab. Defaults to Google's public sample sheet. |
| `mode` | enum | `read` (default), `append`, `replace`, `dedupe-append`. |
| `sheetName` | string | Tab name. Optional. A missing tab is created in write modes. |
| `serviceAccountKey` | secret string | Service-account JSON key. Needed for writing and for private sheets. |
| `datasetId` | string | Apify dataset to write, e.g. another run's `defaultDatasetId`. |
| `jsonData` | array | Rows to write, given as JSON objects. Can be used instead of a dataset or together with one. |
| `dedupeKey` | string | Key column for `dedupe-append`. |
| `columns` | array | Only these columns, in this order. Optional. |
| `range` | string | A1 range for read mode, e.g. `A1:F500`. |
| `maxRows` | integer | Row limit, `0` = all. |
| `flattenNested` | boolean | Flatten nested objects into dotted columns (default `true`). |
| `valueInputOption` | enum | `RAW` (default) or `USER_ENTERED`. |
| `failOnError` | boolean | Mark the run as FAILED on problems instead of returning a free error item. |

Read a public sheet (this is the default input):

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

Append only new rows from another Actor's run:

```json
{
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit",
    "mode": "dedupe-append",
    "sheetName": "Products",
    "datasetId": "{{resource.defaultDatasetId}}",
    "dedupeKey": "url",
    "serviceAccountKey": "{ \"type\": \"service_account\", ... }"
}
```

### Output

**Read mode:** one dataset item per row. The keys are the sheet's header names. Empty headers become `column_<n>`, and repeated headers get `_2`, `_3`. Real output from the default input (Google's public sample sheet):

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

**Write modes:** one free summary item:

```json
{
    "type": "summary",
    "mode": "dedupe-append",
    "spreadsheetId": "1AbC...",
    "spreadsheetTitle": "Price tracker",
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
    "sheetTitle": "Products",
    "rowsInSource": 120,
    "rowsWritten": 17,
    "duplicatesSkipped": 103,
    "rowsWithoutKey": 0,
    "columns": ["url", "title", "price", "currency"],
    "newColumns": [],
    "tabCreated": false,
    "chargeLimitReached": false
}
```

**Problems:** one free error item, and the run still ends as *Succeeded* unless `failOnError` is on:

```json
{
    "type": "error",
    "errorCode": "PERMISSION_DENIED",
    "error": "Permission denied. Share the spreadsheet with the service account e-mail bot@my-project.iam.gserviceaccount.com (Share → add as Editor for writing, Viewer for reading)."
}
```

Error codes: `INVALID_INPUT`, `NO_CREDENTIALS`, `INVALID_CREDENTIALS`, `PERMISSION_DENIED`, `API_DISABLED`, `NOT_PUBLIC`, `SPREADSHEET_NOT_FOUND`, `SHEET_NOT_FOUND`, `NO_SOURCE`, `NO_ROWS`, `DATASET_NOT_FOUND`, `NO_DEDUPE_KEY`, `DEDUPE_KEY_MISSING`, `BAD_REQUEST`, `GOOGLE_API_ERROR`.

### Pricing

Pay-per-event. You pay only for rows that are moved, and platform usage is included.

| Event | Price | When it is charged |
|---|---|---|
| `row` | **$0.00005** per row ($0.05 / 1,000) | Each row read from the sheet into the dataset, or written to the sheet |
| Errors, summary items, skipped duplicates | **Free** | |

| Job | Cost |
|---|---|
| Read a 30-row sheet | $0.0015 |
| Append 1,000 scraped products | $0.05 |
| Daily dedupe-append of 5,000 items where 200 are new | $0.01 per day |

Set **Max cost per run** in the run options to cap spending. The Actor then writes only as many rows as your budget allows and reports `chargeLimitReached: true`.

### Use with AI agents (MCP)

Add the Actor as a tool in the [Apify MCP server](https://mcp.apify.com) (`https://mcp.apify.com?tools=openharvest/google-sheets-import-export`), or let agents find it with `search-actors` ("google sheets"). MCP clients such as Claude, ChatGPT and Cursor can then read and write Google Sheets. Example tool input that an agent can send:

```json
{
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
    "mode": "read",
    "columns": ["Student Name", "Major"],
    "maxRows": 10
}
```

For write modes, the agent passes `jsonData` (an array of objects). Keep the service-account key in a saved task, so the agent never has to see it.

### FAQ

**Why a service account instead of "Sign in with Google"?**
OAuth tokens saved between runs expire or get revoked, and interactive sign-in does not work in scheduled runs or for AI agents. A service account key is passed as a secret input, works the same way every time, and can reach only the sheets you share with it.

**Is my key stored?**
The key is a secret input, so Apify stores it encrypted with your run input. The Actor uses it only in memory during the run, never logs it and never writes it to a dataset or key-value store. You can revoke it at any time in Google Cloud Console.

**Can I read a sheet without any key?**
Yes, if the sheet is shared as *Anyone with the link can view*. The Actor downloads Google's CSV export. Values come out as displayed text, exactly what CSV export gives you.

**What does `dedupe-append` compare?**
The text value of the key column, with spaces trimmed. Rows already in the sheet and repeated rows within the same input are both skipped. Rows that have no key value are appended.

**How big can it go?**
Google Sheets allows 10 million cells per spreadsheet. The Actor writes in batches of 5,000 rows and retries Google's rate limits (HTTP 429) with backoff. Very large datasets (hundreds of thousands of rows) need more memory, so raise the run memory to 2–4 GB.

**Can it create a new spreadsheet?**
No. Service accounts no longer get Drive storage of their own. Create the spreadsheet yourself, share it with the service account, and the Actor fills it. Missing tabs are created automatically.

**Formulas?**
With the default `RAW`, a value like `=SUM(A1:A3)` is stored as text. With `USER_ENTERED` it becomes a working formula. Use `USER_ENTERED` only with data you trust.

### Privacy and terms

The Actor accesses only spreadsheets that you own or that are shared with your service account, plus sheets their owners made public. It uses Google's official Sheets API and Google's own CSV export. You are responsible for having the right to read and write the data you move, and for following Google's Terms of Service and data-protection laws (e.g. GDPR) when the data includes personal information. The default sample sheet is Google's public demo spreadsheet.

### Related actors

Other actors by the same developer whose results are often sent to Google Sheets:

- *Google News Scraper – Full Article Text & Real Publisher URLs*
- *Google Trends Scraper – Interest Over Time & Related Queries*
- *Keyword Suggestions – Google, YouTube & Amazon Autocomplete*
- *Website SEO Audit Crawler – Broken Links, Schema & llms.txt*
- *Shopify & WooCommerce Products Scraper – Any Store, Schema.org*

### Support & feedback

Something does not work with your sheet? Open an issue in the **Issues** tab with the error code from the error item. Never paste your key into an issue.

# Actor input Schema

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

Link to the spreadsheet (e.g. <code>https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0</code>) or just its ID. A <code>#gid=</code> in the link selects that tab.

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

<b>read</b> = sheet → dataset (one item per row, row 1 is the header). <b>append</b> = add rows under the existing data. <b>replace</b> = clear the tab and write fresh data. <b>dedupe-append</b> = append only rows whose key column value is not in the sheet yet. All write modes need a service account key.

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

Optional. Name of the tab to read or write, e.g. <code>Leads</code>. If empty, the tab from the link's <code>#gid=</code> is used, otherwise the first tab. In write modes a missing tab is created automatically.

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

Needed for writing and for reading private sheets. Paste the whole JSON key file of a Google Cloud service account, then share the spreadsheet with the service account's e-mail (<code>...@...iam.gserviceaccount.com</code>) as Editor. The key is stored encrypted by Apify, used only during the run and never logged or saved. Leave empty to read a public sheet ("Anyone with the link can view").

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

Write modes: the Apify dataset to export, e.g. the <code>defaultDatasetId</code> of another Actor run. In an integration/webhook use <code>{{resource.defaultDatasetId}}</code>. Only this dataset is shared with the Actor (read-only).

## `jsonData` (type: `array`):

Write modes: rows to write as a JSON array of objects. Used together with or instead of the dataset. Each key becomes a column.

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

For <b>dedupe-append</b>: the column whose value identifies a row, e.g. <code>url</code> or <code>id</code>. Rows whose key is already in the sheet (or repeated in the input) are skipped and not charged.

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

Optional. Only these columns, in this order. Read mode: output only these sheet columns. Write modes: write only these fields (nested fields use dots, e.g. <code>price.value</code>). Empty = all.

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

Optional A1 range inside the tab for read mode, e.g. <code>A1:F500</code>. The first row of the range is used as the header.

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

Maximum number of rows to read or write. 0 = no limit. You pay per row, so this also caps your cost.

## `flattenNested` (type: `boolean`):

Write modes: turn nested objects into columns like <code>author.name</code>. Lists of simple values are joined with "; ", lists of objects are stored as JSON text.

## `valueInputOption` (type: `string`):

<b>RAW</b> (safe default) stores values exactly as given. <b>USER_ENTERED</b> lets Google parse them like typed input (dates, numbers, and formulas starting with =).

## `failOnError` (type: `boolean`):

By default a problem (e.g. sheet not shared) is reported as a free error item and the run ends normally. Turn this on if a pipeline should see the run as FAILED instead.

## Actor input object example

```json
{
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "mode": "append",
  "sheetName": "Leads",
  "datasetId": "aBcD1234eFgH5678i",
  "jsonData": [
    {
      "id": "1",
      "title": "First row",
      "price": 9.99
    },
    {
      "id": "2",
      "title": "Second row",
      "price": 4.5
    }
  ],
  "dedupeKey": "url",
  "columns": [
    "id",
    "title",
    "price"
  ],
  "range": "A1:F500",
  "maxRows": 1000,
  "flattenNested": true,
  "valueInputOption": "RAW",
  "failOnError": false
}
```

# Actor output Schema

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

All scraped items in the default dataset (JSON, CSV, Excel and more via the dataset export).

## `files` (type: `string`):

Records saved in the default key-value store, e.g. the run summary.

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

// Run the Actor and wait for it to finish
const run = await client.actor("openharvest/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 = {
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
    "mode": "read",
}

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

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,openharvest/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/aQYf5iuvUzCxkNreB/builds/SyuhL6le8zNwo83hd/openapi.json