# Google Sheets Export & Import (`adhistory/google-sheets-export-actor`) Actor

Send any Apify dataset or JSON to a Google Sheet (append, append only new rows with dedupe, or replace with backup), or import a sheet tab into a dataset. Public sheets import with no credentials. Works as an integration after any scraper.

- **URL**: https://apify.com/adhistory/google-sheets-export-actor.md
- **Developed by:** [Brandon Ob](https://apify.com/adhistory) (community)
- **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 writtens

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 Export & Import: Apify dataset ↔ Google Sheets (append, dedupe, replace, import)

Send any **Apify dataset** or **JSON** to a **Google Sheet**. Add it as an **integration** after your scraper and every finished run lands in your spreadsheet automatically. Three write modes:

- **Append**: add all rows below the existing data.
- **Append only new rows (dedupe)**: skip rows whose **key columns** (e.g. `url`, `id`) are already in the sheet. Perfect for scheduled scrapers that return the same items every day.
- **Replace**: overwrite the tab, after saving a **backup** of the old contents.

It also works the other way: **Import** reads a sheet tab into a dataset, so a Google Sheet can feed URLs, keywords or product lists into any scraper. **Public sheets import with no credentials at all.**

Columns are created for you: nested objects become `price.value`-style columns, lists are joined into one cell, and **new fields are added as new columns at the end** without disturbing the ones you already have.

### Who it's for

- **Anyone running a scraper on a schedule** who wants the results in a Google Sheet, not a CSV download.
- **Lead generation and sales teams:** scrape leads daily and keep one growing, duplicate-free sheet that a VA or CRM import can work from.
- **Price and SEO monitoring:** replace a "latest" tab on every run, or append a dated history.
- **No-code builders:** Sheets is the easiest place to hand data to Looker Studio, Zapier, Make, n8n or a client.

### Import a Google Sheet into a dataset

1. Set **Operation** to **Import**.
2. Paste the sheet URL. Copy it from the tab you want, so it ends in `#gid=...`.
3. If the sheet is shared as **Anyone with the link: Viewer**, run it. No Google setup is needed. For a private sheet, add a service account key (steps below) and share the sheet with it (Viewer is enough to import).

Row 1 becomes the field names (change **Header row** if your headers are lower down). Every row below becomes one dataset item. Empty rows are skipped. Blank or repeated headers get safe names (`column_3`, `name_2`). Turn on **Nest dot-path columns** to rebuild nested objects from columns like `price.value`, the reverse of what Export does. Public sheets are read through Google's CSV export, so plain numbers come back as numbers and everything else as text. The Sheets API path keeps Google's own types and shows dates as formatted text.

Example input:

```json
{
  "operation": "import",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
}
```

Feed the dataset into another Actor with its **Dataset ID** input, or call this Actor from your own code and read the rows over the API.

### How to use (export)

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

A service account is a robot Google account. You give it access to one sheet, and only that sheet.

1. Open [Google Cloud Console](https://console.cloud.google.com/), create a project (any name), and enable the **[Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com)** for it.
2. Go to **IAM & Admin > Service Accounts > Create service account**. Any name works. Skip the optional role steps.
3. Open the new service account, go to **Keys > Add key > Create new key > JSON**. A `.json` file downloads.
4. Open the file in a text editor, copy **all** of it, and paste it into **Service account JSON** in this Actor. It is stored encrypted.
5. Copy the `client_email` from the file (it looks like `name@project.iam.gserviceaccount.com`).

#### 2. Share your sheet with the service account

Open your Google Sheet, click **Share**, paste the `client_email`, choose **Editor**, and untick "Notify people". Viewer or Commenter access is not enough. If you forget this step, the run fails with a message telling you exactly which email to share with.

#### 3. Run it

1. Paste the **Google Sheet URL** (or ID).
2. Optional: a **Sheet (tab) name**. A missing tab is created for you.
3. Pick the source: a **Dataset ID**, or paste **JSON data**.
4. Pick a **write mode**. For append-new-only, set **Key columns** (e.g. `url`).

#### Use it as an integration after another Actor

1. Open your scraper (any Actor or task) and go to the **Integrations** tab.
2. Add **Google Sheets Export** as an Actor integration, triggered on **Run succeeded**.
3. In its input, fill in the sheet URL, service account JSON, tab and mode. Leave **Dataset ID** empty (or set it to `{{resource.defaultDatasetId}}`): the finished run's dataset is exported automatically.

Now every scheduled run of your scraper updates the sheet. Use **Append only new rows** so re-scraped items are not added twice.

#### Example input

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",
  "sheetName": "Leads",
  "datasetId": "Hk8sX2vqP4aZr1wTn",
  "mode": "append-new-only",
  "keyColumns": ["url"],
  "excludeColumns": ["html"]
}
```

#### Example output

A summary row in the dataset and an `OUTPUT` record in the key-value store:

```json
{
  "sheetUrl": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=184019232",
  "sheetName": "Leads",
  "mode": "append-new-only",
  "rowsInSource": 1250,
  "rowsWritten": 312,
  "rowsSkippedDuplicates": 936,
  "rowsSkippedEmptyKey": 2,
  "newColumnsAdded": ["company.linkedinUrl"],
  "totalColumns": 18,
  "tabCreated": false,
  "firstRowWritten": 4821,
  "lastRowWritten": 5132,
  "backupKey": null,
  "chargeLimitReached": false
}
```

### How rows and columns are written

- **Row 1 is the header.** The first run writes it; later runs match fields to it by name.
- **Automatic columns:** every field in the data, in first-seen order. `{"price": {"value": 9}}` becomes a `price.value` column. `["a", "b"]` becomes `a, b` (change the separator in **Array separator**). Lists of objects are written as JSON text. Fields starting with `#` (Apify hidden fields) are skipped.
- **Explicit columns:** set **Columns** to write only those, in that order.
- **New fields** in later runs are added as **new columns at the end**. Existing columns are never reordered, renamed or removed.
- **Values are written as-is (RAW)** by default, so scraped text that starts with `=` is never run as a formula. Choose `USER_ENTERED` if you want Google to parse dates and formulas.
- Cells longer than Google's 50,000-character limit are cut off with `…`.

### Reliability

- **Batched writes**, 500 rows per request by default.
- **Automatic retries** with exponential backoff on Google rate limits (429) and server errors (5xx), respecting `Retry-After`. After the first batch, rows go to exact row numbers, so a retried request can't create duplicate rows.
- **Checks the 10,000,000-cell limit before writing anything.** Google counts every cell in every tab, empty ones included. If the export would not fit, the run stops with nothing written and tells you how to fix it.
- **Never clears your sheet** unless you choose **replace**. Replace saves a backup of the old tab (formulas included) to the run's key-value store as `BACKUP-<tab>-<time>`, writes the new data, and only then trims leftover rows and columns.
- **Empty source = no change.** If the scraper returned 0 items, nothing is written, even in replace mode, so a failed scrape can't wipe your sheet.
- **Clear errors**, for example:
  - `Permission denied ... Open the spreadsheet, click Share, and add bot@my-project.iam.gserviceaccount.com as Editor`
  - `The Google Sheets API is not enabled for your Google Cloud project. Enable it at https://console.cloud.google.com/apis/library/sheets.googleapis.com?project=...`
  - `This file is an Excel (.xlsx) file stored in Google Drive, not a Google Sheet. Open it and use File > Save as Google Sheets`

### Pricing

Pay per event:

| Event | Price |
|---|---|
| Actor start | $0.001 per run |
| Row written | $0.0001 per row ($0.10 per 1,000 rows), for rows written to a sheet (export) or to the dataset (import) |

Only rows actually written are charged. Duplicates skipped by dedupe are free, and so are **dry runs**: turn on *Dry run* (or run without credentials) to preview the rows and columns that would be written without touching the sheet. A daily scraper that adds 200 new rows costs about $0.02 per day. Set a **maximum cost per run** in Apify: the Actor writes as many rows as fit within it, stops cleanly, and reports how many were left out.

### Why another Sheets Actor?

Pushing scraper results into Google Sheets is usually a scheduled, unattended job, so the export has to work every time and say clearly what went wrong when it can't. This Actor is built for that: one job (dataset to sheet), retries for Google's rate limits, a cell-limit check before any write, a backup before any overwrite, and dedupe by key columns so scheduled runs don't fill your sheet with copies. It does one thing and tries to do it reliably.

### FAQ

**Why a service account and not "Sign in with Google"?** A service account works in scheduled and integration runs without anyone signing in, never expires because a browser session ended, and only sees the sheets you share with it. If you already have an OAuth app, you can use the **OAuth client ID, secret and refresh token** fields instead (the refresh token must have the `https://www.googleapis.com/auth/spreadsheets` scope).

**Can it import a Google Sheet into a dataset?** Yes. Set **Operation** to **Import**. Public sheets need no credentials; private sheets need a service account with Viewer access.

**What does dedupe compare?** The values in your key columns, as text, against every row already in the tab, plus earlier rows in the same run. Rows where all key columns are empty are skipped (and counted in `rowsSkippedEmptyKey`), because they can't be told apart and would be added again on every run.

**Can I use a key column that is nested?** Yes, use the dot path, e.g. `product.sku`.

**Will it change my formatting, formulas or extra columns?** Append modes only add rows at the bottom and new header cells at the end of row 1. Replace mode rewrites the whole tab, including columns you added by hand, so keep formulas on a separate tab.

**My data has more than 10 million cells.** Google Sheets can't hold it. Split by tab or spreadsheet, choose fewer columns, or use replace instead of an ever-growing append.

**Something broke or you need a feature?** Open an issue on the Issues tab. I usually reply within a day.

# Actor input Schema

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

export: write a dataset or JSON into the sheet. import: read a sheet tab into this run's dataset (public sheets work without credentials).

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

Paste the full Google Sheets URL (https://docs.google.com/spreadsheets/d/.../edit#gid=...) or just the spreadsheet ID. Export: share the sheet with your service account email as Editor. Import: a sheet shared as "Anyone with the link" needs no credentials.

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

Export: tab to write to (created if missing). Import: tab to read. Leave empty to use the tab in the URL (#gid=...) or the first tab.

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

Paste the entire JSON key file of a Google Cloud service account. Then share your sheet with the service account's client_email as Editor. See the README for a 5-step setup. Stored encrypted.

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

append: add all rows below existing data. append-new-only: add only rows whose key column values are not in the sheet yet (set Key columns). replace: overwrite the whole tab (a backup is saved first). The sheet is never cleared except in replace mode.

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

Preview the rows and columns that would be written, without touching the sheet. Free. Runs without credentials are always dry runs.

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

Columns that identify a row, e.g. url or id. Used by append-new-only: a row is skipped if a row with the same values in all key columns is already in the sheet or earlier in this run. Use dot paths for nested fields, e.g. product.sku.

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

Apify dataset to export. When you add this Actor as an integration on another Actor, leave it empty (the finished run's dataset is used automatically) or set it to {{resource.defaultDatasetId}}.

## `rawData` (type: `array`):

Instead of a dataset, paste an array of objects to write, e.g. \[{"name": "A", "price": 1}]. Used when set; Dataset ID is then ignored.

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

Optional. Exact columns to write, in this order (dot paths for nested fields, e.g. price.value). Leave empty for automatic columns: every field found, in first-seen order, nested objects flattened to dot paths.

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

Fields to leave out in automatic mode. Excluding a parent (e.g. address) also excludes its nested fields (address.city).

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

Read at most this many items from the source. Empty = all.

## `arraySeparator` (type: `string`):

Lists of plain values (e.g. tags) are joined into one cell with this separator. Lists of objects are written as JSON.

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

RAW writes values exactly as they are (safe default; text starting with = is not run as a formula). USER_ENTERED lets Google parse values as if typed in, e.g. dates and formulas.

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

In replace mode, save the tab's current contents (including formulas) to this run's key-value store before overwriting it.

## `batchSize` (type: `integer`):

Rows sent to Google per API call. 500 is a good default; lower it for very wide rows.

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

Import only: the row that holds column names. Rows below it become dataset items.

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

Import only: leave out rows where every cell is empty.

## `unflattenColumns` (type: `boolean`):

Import only: turn columns like price.value into nested objects {"price": {"value": ...}}, the reverse of the export's flattening.

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

Advanced alternative to a service account: OAuth 2.0 client ID of a Google Cloud app. Use together with client secret and refresh token. Leave empty when using a service account.

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

OAuth 2.0 client secret matching the client ID.

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

Refresh token issued to this client with the https://www.googleapis.com/auth/spreadsheets scope.

## `payload` (type: `object`):

Filled automatically when this Actor runs as an integration or from a webhook. The dataset at resource.defaultDatasetId is exported when Dataset ID and JSON data are empty. You don't need to set this.

## Actor input object example

```json
{
  "operation": "export",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcdEFG/edit",
  "mode": "append",
  "dryRun": false,
  "rawData": [
    {
      "company": "Acme Inc",
      "website": "acme.com",
      "city": "Austin",
      "tags": [
        "saas",
        "b2b"
      ]
    },
    {
      "company": "Globex",
      "website": "globex.com",
      "city": "Denver",
      "tags": [
        "manufacturing"
      ]
    }
  ],
  "arraySeparator": ", ",
  "valueInputOption": "RAW",
  "backupBeforeReplace": true,
  "batchSize": 500,
  "headerRow": 1,
  "skipEmptyRows": true,
  "unflattenColumns": false
}
```

# Actor output Schema

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

No description

## `output` (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 = {
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcdEFG/edit",
    "rawData": [
        {
            "company": "Acme Inc",
            "website": "acme.com",
            "city": "Austin",
            "tags": [
                "saas",
                "b2b"
            ]
        },
        {
            "company": "Globex",
            "website": "globex.com",
            "city": "Denver",
            "tags": [
                "manufacturing"
            ]
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("adhistory/google-sheets-export-actor").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 = {
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcdEFG/edit",
    "rawData": [
        {
            "company": "Acme Inc",
            "website": "acme.com",
            "city": "Austin",
            "tags": [
                "saas",
                "b2b",
            ],
        },
        {
            "company": "Globex",
            "website": "globex.com",
            "city": "Denver",
            "tags": ["manufacturing"],
        },
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("adhistory/google-sheets-export-actor").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 '{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz0123456789abcdEFG/edit",
  "rawData": [
    {
      "company": "Acme Inc",
      "website": "acme.com",
      "city": "Austin",
      "tags": [
        "saas",
        "b2b"
      ]
    },
    {
      "company": "Globex",
      "website": "globex.com",
      "city": "Denver",
      "tags": [
        "manufacturing"
      ]
    }
  ]
}' |
apify call adhistory/google-sheets-export-actor --silent --output-dataset

```

## MCP server setup

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

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/hcQ7RF05S4GkQGxah/builds/dVf7nKa5DhMvFdnHM/openapi.json