# Google Sheets Export: Append, Upsert & Dedupe (`codemagnet/google-sheets-export`) Actor

Export Apify datasets or JSON to Google Sheets. Append, append only new rows, upsert by key, or replace with backup. Read tabs back into datasets.

- **URL**: https://apify.com/codemagnet/google-sheets-export.md
- **Developed by:** [Kirby Bookhart-Tsai](https://apify.com/codemagnet) (community)
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.20 / 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: Append, Upsert & Dedupe

Send any Apify dataset, or JSON you paste, to a Google Sheets tab, and keep that tab up to date run after run without duplicates. Run it after a scraper, on a schedule, from the API or from an AI agent.

- **Append**, **Append new only** (skip rows already in the sheet), **Upsert** (update matching rows, add the rest), **Replace** (with automatic backup) and **Read** (load a tab into a dataset).
- **Reliable in pipelines.** Rows go exactly below your last row, even if the tab has blank rows. Google rate limits and outages are retried with backoff. Google's 10-million-cell limit is checked *before* anything is written. Errors explain what to fix.
- **Respects your sheet.** New data columns are added at the right end. Upsert only touches the columns in your data, so notes and formulas you add in other columns stay.
- **Clean columns.** Nested data is flattened into readable columns (`seller.name`, `stats.rating`), while fields that vary wildly between items (like maps keyed by IDs) stay as one JSON column instead of thousands of columns.
- **Safe values.** By default values are stored exactly as they are, so scraped text that starts with `=` can't run as a formula in your sheet.
- **Connect in 3 minutes without Google Cloud**, or use a Google service account. No interactive sign-in during runs, so it works in schedules, integrations and AI agents.
- **Try it free.** Without a Google connection the Actor shows a preview of the columns and rows it would write. **Dry run** connects and shows what would be appended, updated and skipped, without changing anything.

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

### Quick start

1. **Connect Google** using [Option A](#option-a-connector-script-no-google-cloud-needed) (easiest) or [Option B](#option-b-service-account).
2. **Choose what to export:** pick a dataset (for example, the results of a scraper run) or paste JSON.
3. **Choose where:** paste the spreadsheet link and a tab name (created if missing), then pick a mode.
4. **Run.** The run summary shows how many rows were appended, updated and skipped, with a link to the sheet.

### How to connect

#### Option A: connector script (no Google Cloud needed)

You add a small script to your spreadsheet. The Actor sends rows to that script, and the script writes them with your own Google account. Your Google password and sign-in tokens never leave Google. The script can only touch the spreadsheet it's installed in, and only accepts requests that carry your secret.

1. Open the spreadsheet and click **Extensions > Apps Script**.
2. Delete any code in the editor and paste the [connector code](#connector-code) below.
3. In the first lines, replace `CHANGE-ME-to-a-long-random-password` with your own long random text (16+ characters). This is your **connector secret**. Click the save icon.
4. In the left sidebar, click **+** next to **Services**, choose **Google Sheets API** and click **Add**. (This switches on the API the connector uses; no Google Cloud setup needed.)
5. Click **Deploy > New deployment**. Next to "Select type" click the gear icon and choose **Web app**. Set **Execute as: Me** and **Who has access: Anyone**. Click **Deploy**.
6. Google asks you to authorize the script. Choose your account. You'll see *"Google hasn't verified this app"*: that's normal for a script you wrote yourself in your own account. Click **Advanced > Go to … (unsafe) > Allow**. The permissions it asks for are explained in the [FAQ](#faq).
7. Copy the **Web app URL** (it ends in `/exec`). In the Actor input, paste it as **Connector URL** and your secret as **Connector secret**.

The secret and URL are stored encrypted by Apify as secret inputs. Anyone without the secret gets an error and can't touch your data. If you ever change the secret, click **Deploy > Manage deployments > Edit (pencil) > Version: New version > Deploy**; the URL stays the same.

> Some company Google accounts don't allow "Who has access: Anyone". If you can't pick it, use Option B.

#### Option B: service account

A service account is a robot Google account you create in Google Cloud and share the spreadsheet with. Teams like it because it doesn't depend on any person's account.

1. Go to [Google Cloud Console](https://console.cloud.google.com/) and create a project (or pick one).
2. Open [Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com) and click **Enable**.
3. Go to **IAM & Admin > Service Accounts > Create service account**. Give it a name and click **Done** (no roles are needed).
4. Open the new account, go to **Keys > Add key > Create new key > JSON**. A `.json` file downloads.
5. In your spreadsheet click **Share**, paste the service account's email (it ends in `.iam.gserviceaccount.com`), choose **Editor**, and share.
6. In the Actor input choose **Google service account**, paste the whole JSON file into **Service account key (JSON)**, and paste the spreadsheet link.

Keep the key file private: anyone with it can edit spreadsheets shared with that service account. Apify stores it encrypted.

### Modes

| Mode | What happens | Needs key columns |
|---|---|---|
| **Append** (default) | Adds every item as a new row below the last non-empty row. | No |
| **Append new only** | Adds only items whose key isn't in the tab yet; also drops duplicates within the input. Ideal for daily scrapes. | Yes |
| **Upsert** | Updates rows whose key matches (only in your data's columns) and appends the rest. If the input repeats a key, the last item wins. | Yes |
| **Replace** | Saves the tab's current contents to the run's key-value store (`BACKUP-001`, …), clears the tab, and writes fresh data with a header row. | No |
| **Read** | Loads the tab into the run's dataset. Row 1 is used as field names. | No |

**Key columns** identify a row: usually `url` or `id`. Use several for a combined key, like `sku` + `country`. Values are compared as text, so `123` and `"123"` match.

### Use it after a scraper (integration)

1. Open your scraper (Actor or task) and go to the **Integrations** tab.
2. Add an integration, choose **Google Sheets Export**, and fill in the connection, spreadsheet, tab and mode.
3. Make sure the input contains `"datasetId": "{{resource.defaultDatasetId}}"`. Each time the scraper succeeds, its results go straight to your sheet.

Tip: with **Append new only** and `url` as the key column, a daily scraper builds one clean list in your sheet with no duplicates.

### Input examples

Append a dataset with the connector:

```json
{
    "datasetId": "YOUR_DATASET_ID",
    "sheetName": "Leads",
    "mode": "append",
    "connection": "appsScript",
    "appsScriptUrl": "https://script.google.com/macros/s/AKfy.../exec",
    "appsScriptSecret": "your-connector-secret"
}
```

Keep a price list current by product URL, using a service account and only three columns:

```json
{
    "datasetId": "YOUR_DATASET_ID",
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
    "sheetName": "Prices",
    "mode": "upsert",
    "keyColumns": ["url"],
    "fields": ["url", "title", "price"],
    "connection": "serviceAccount",
    "serviceAccountKey": "{ \"type\": \"service_account\", ... }"
}
```

Read a tab of URLs into a dataset, for example to feed a scraper:

```json
{
    "mode": "read",
    "sheetName": "URLs to scrape",
    "connection": "appsScript",
    "appsScriptUrl": "https://script.google.com/macros/s/AKfy.../exec",
    "appsScriptSecret": "your-connector-secret"
}
```

### Output

Each export adds one summary item to the run's dataset (also saved as `OUTPUT` in the key-value store):

```json
{
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/.../edit",
    "tab": "Leads",
    "mode": "append-new",
    "itemsRead": 900,
    "rowsAppended": 25,
    "rowsUpdated": 0,
    "rowsSkippedExisting": 875,
    "duplicatesInInput": 0,
    "columnsAdded": [],
    "stoppedAtChargeLimit": false,
    "warnings": []
}
```

In **Read** mode, the dataset contains the rows and the summary is in `OUTPUT`. In **Replace** mode, the old contents are in `BACKUP-001` (and more parts for big tabs).

### Pricing

You pay only for what the Actor does. Platform usage is included.

| Event | Price |
|---|---|
| Run (only when at least one row is written or read) | $0.005 |
| Row written (appended or updated) | $0.20 per 1,000 rows |
| Row read (Read mode) | $0.10 per 1,000 rows |
| Actor start (Apify's standard fee, every run) | $0.00005 |

Previews, dry runs, skipped rows and runs that find nothing new cost only the $0.00005 start fee.

Examples:

- A daily export of 500 new rows: $0.005 + $0.10 = **$0.105 per run**, about **$3.15 a month**.
- A weekly upsert of 2,000 rows: **$0.405 per run**.

Set **Maximum cost per run** in the run options to cap spending. The Actor writes only what fits and says so in the summary. Replace mode refuses to start if the limit can't cover every row, so a tab is never left half-written.

### Limits

- **Google's limits:**
  - 10 million cells per spreadsheet, across all tabs; empty cells count too. The Actor checks this before writing and tells you how big the result would be.
  - 50,000 characters per cell. Longer values are shortened and counted in the summary.
  - About 60 write requests per minute per user. The Actor batches up to 5,000 rows per request and paces itself.
- Up to 1,000 columns per export. Use **Columns to include** for very wide data.
- Don't run two exports into the same tab at the same time; they could interleave rows.
- With the connector, Google's daily Apps Script quota applies: 20,000 requests a day for personal accounts, more for Workspace. Each request carries up to 5,000 rows, so the spreadsheet's cell limit usually comes first.

### FAQ

**Why does the connector need "See, edit, create and delete all your Google Sheets spreadsheets" and "Connect to an external service"?**
The connector writes through Google's official Sheets API, and Google only offers that permission for all your spreadsheets. The code itself can only reach the spreadsheet it's installed in: it builds every request from its own spreadsheet ID and allows only a short list of read and write calls. "External service" refers to Google's Sheets API at sheets.googleapis.com. You can read the whole script below; it's under 90 lines.

**The run failed with "The connector rejected the secret".**
The **Connector secret** must match `SECRET` in the script exactly. If you edited the script, deploy a new version (Deploy > Manage deployments > Edit > Version: New version).

**"Google asked for a sign-in, so the connector is not public".**
Set **Who has access** to **Anyone** in the deployment and deploy a new version. If your company blocks that, use a service account.

**"The service account … can't edit this spreadsheet".**
Share the spreadsheet with the service account's email as an Editor. The email is shown in the error and the run log.

**Why are some nested fields one JSON column?**
Fields whose keys differ a lot between items (for example, objects keyed by IDs or names) would create thousands of columns, so they stay as JSON. To get a specific value as its own column, list it in **Columns to include** with a dot path, like `pricing.events.start.price`.

**Dates and numbers look like text.**
By default values are stored exactly as in the data; numbers stay numbers, but a date like `2026-10-02` stays text. Choose **Let Google interpret them** to have Google parse dates and percentages like typed input. Don't use that option for untrusted scraped text, since text starting with `=` would become a formula.

**How do I undo a Replace?**
Open the run's **Storage > Key-value store**, download `BACKUP-001`, and copy the rows back.

**Is my data stored?**
The Actor reads your dataset, writes to your sheet, and keeps only the run summary (and Replace backups) in that run's own storage, under your Apify account's retention settings.

### Connector code

Paste this into **Extensions > Apps Script** in your spreadsheet (see [Option A](#option-a-connector-script-no-google-cloud-needed)):

```javascript
/**
 * Google Sheets Export connector (version 1)
 *
 * Lets the "Google Sheets Export" Apify Actor read and write THIS spreadsheet only, using your own
 * Google account. Your Google password and tokens never leave Google.
 *
 * Setup (about 3 minutes):
 *  1. Replace the SECRET below with your own long random text (at least 16 characters).
 *  2. At the left, click + next to "Services", choose "Google Sheets API" and click Add.
 *  3. Click Deploy > New deployment > Select type: Web app.
 *     Execute as: Me. Who has access: Anyone. Click Deploy and allow access.
 *  4. Copy the Web app URL. In the Actor input, paste it as "Connector URL" and your SECRET as
 *     "Connector secret".
 *
 * If you change the SECRET later: Deploy > Manage deployments > Edit > Version: New version.
 */
const SECRET = 'CHANGE-ME-to-a-long-random-password';

const VERSION = 1;
const SHEETS_API = 'https://sheets.googleapis.com/v4/spreadsheets/';
const PLACEHOLDER = 'CHANGE-ME-to-a-long-random-password';

function doPost(e) {
  var req;
  try {
    req = JSON.parse(e.postData.contents);
  } catch (err) {
    return reply_({ ok: false, error: 'bad-request', message: 'Body is not JSON' });
  }
  if (SECRET === PLACEHOLDER || SECRET.length < 16) return reply_({ ok: false, error: 'secret-not-set' });
  if (!req || typeof req.token !== 'string' || !safeEquals_(req.token, SECRET)) return reply_({ ok: false, error: 'bad-token' });

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var id = ss.getId();
  if (req.action === 'ping') return reply_({ ok: true, version: VERSION, spreadsheetId: id, spreadsheetUrl: ss.getUrl() });
  if (req.action !== 'proxy') return reply_({ ok: false, error: 'bad-request', message: 'Unknown action' });

  var method = String(req.method || 'GET').toUpperCase();
  var path = String(req.path || '');
  if (method !== 'GET' && method !== 'POST') return reply_({ ok: false, error: 'bad-request', message: 'Method not allowed' });
  if (!isAllowedPath_(path)) return reply_({ ok: false, error: 'bad-request', message: 'Path not allowed' });

  var options = {
    method: method.toLowerCase(),
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
    muteHttpExceptions: true,
  };
  if (method === 'POST') {
    options.contentType = 'application/json';
    options.payload = JSON.stringify(req.body || {});
  }
  var res = UrlFetchApp.fetch(SHEETS_API + id + path, options);
  return reply_({ ok: true, status: res.getResponseCode(), body: res.getContentText() });
}

function doGet() {
  return reply_({ ok: true, message: 'The connector is deployed. Paste this URL into the Actor input.' });
}

/** Only these Sheets API calls on this spreadsheet are forwarded. */
function isAllowedPath_(p) {
  if (p.indexOf('..') !== -1 || p.indexOf('#') !== -1 || p.indexOf('\\') !== -1) return false;
  var query = '(\\?[^/?#]*)?$';
  return new RegExp('^' + query).test(p) // spreadsheet info
    || new RegExp('^:batchUpdate' + query).test(p)
    || new RegExp('^/values:(batchUpdate|batchGet|batchClear)' + query).test(p)
    || new RegExp('^/values/[^/?#]+(:clear|:append)?' + query).test(p);
}

function safeEquals_(a, b) {
  if (a.length !== b.length) return false;
  var diff = 0;
  for (var i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i);
  return diff === 0;
}

function reply_(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

/** Never called. Makes Apps Script ask for full Sheets access, which the Sheets API calls above need. */
function requestScopes_() {
  SpreadsheetApp.openById('unused');
}
```

### Feedback

Found a bug or need a feature? Open an issue on the **Issues** tab and include a link to the run.

# Actor input Schema

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

Pick a dataset, for example the results of a scraper run. When this Actor runs as an integration after another Actor, it uses that run's dataset automatically.

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

A JSON array of objects. Used only when no dataset is selected.

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

The full link from your browser, like https://docs.google.com/spreadsheets/d/.../edit. Required with a service account; optional with the connector (it already knows its spreadsheet).

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

The tab to write to. Created if it doesn't exist. Leave empty to use the first tab.

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

How rows are written. Append new only and Upsert need key columns. Replace saves the old contents to the run's key-value store first.

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

Columns that identify a row, such as url or id. Needed for Append new only and Upsert. Use several for a combined key.

## `connection` (type: `string`):

See the README for 3-minute setup steps for either option.

## `appsScriptUrl` (type: `string`):

The Web app URL of the connector script, ending in /exec.

## `appsScriptSecret` (type: `string`):

The SECRET you set at the top of the connector script.

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

Paste the whole JSON key file. Then share the spreadsheet with the service account's email as an Editor.

## `fields` (type: `array`):

Only these columns, in this order. Nested fields use dots, like seller.name. Leave empty for all columns.

## `omitFields` (type: `array`):

Columns to skip, such as html or debug fields.

## `nestedData` (type: `string`):

With Flatten, objects that vary a lot between items (for example keyed by IDs) still stay as JSON in one column, so the sheet doesn't explode into thousands of columns.

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

When the data has columns the tab doesn't, add them at the right end of the header. Turn off to keep the tab's columns fixed.

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

Keep the default for scraped data: interpreting values would let text from websites run as spreadsheet formulas.

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

Skip this many items (or rows, in Read mode) from the start.

## `limit` (type: `integer`):

Export at most this many items (or read at most this many rows).

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

Connect and plan, but don't change the spreadsheet. Shows what would be appended, updated and skipped. Free.

## Actor input object example

```json
{
  "rawData": [
    {
      "name": "Example product",
      "price": 19.99,
      "url": "https://example.com/p/1",
      "inStock": true,
      "tags": [
        "new"
      ]
    },
    {
      "name": "Second product",
      "price": 5,
      "url": "https://example.com/p/2",
      "inStock": false,
      "seller": {
        "name": "Example Shop",
        "country": "US"
      }
    }
  ],
  "mode": "append",
  "connection": "appsScript",
  "nestedData": "flatten",
  "addNewColumns": true,
  "valueInput": "raw",
  "dryRun": false
}
```

# Actor output Schema

## `dataset` (type: `string`):

One summary item per export with counts of appended, updated and skipped rows and a link to the sheet. In Read mode, the tab's rows.

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

The same summary as a single JSON record.

## `backups` (type: `string`):

BACKUP-001 and further parts: the tab's contents saved before Replace mode cleared it.

# 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 = {
    "rawData": [
        {
            "name": "Example product",
            "price": 19.99,
            "url": "https://example.com/p/1",
            "inStock": true,
            "tags": [
                "new"
            ]
        },
        {
            "name": "Second product",
            "price": 5,
            "url": "https://example.com/p/2",
            "inStock": false,
            "seller": {
                "name": "Example Shop",
                "country": "US"
            }
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("codemagnet/google-sheets-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 = { "rawData": [
        {
            "name": "Example product",
            "price": 19.99,
            "url": "https://example.com/p/1",
            "inStock": True,
            "tags": ["new"],
        },
        {
            "name": "Second product",
            "price": 5,
            "url": "https://example.com/p/2",
            "inStock": False,
            "seller": {
                "name": "Example Shop",
                "country": "US",
            },
        },
    ] }

# Run the Actor and wait for it to finish
run = client.actor("codemagnet/google-sheets-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 '{
  "rawData": [
    {
      "name": "Example product",
      "price": 19.99,
      "url": "https://example.com/p/1",
      "inStock": true,
      "tags": [
        "new"
      ]
    },
    {
      "name": "Second product",
      "price": 5,
      "url": "https://example.com/p/2",
      "inStock": false,
      "seller": {
        "name": "Example Shop",
        "country": "US"
      }
    }
  ]
}' |
apify call codemagnet/google-sheets-export --silent --output-dataset

```

## MCP server setup

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