# Google Sheets Export: Apify Dataset to Sheets (`plainsource/google-sheets-export`) Actor

Write an Apify dataset, JSON URL or JSON array to a Google Sheets tab. Append, replace, upsert by key or append only new rows. New fields become new columns instead of breaking the run. Uses your own service account key; no Google sign-in prompt.

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

## Pricing

from $0.01 / 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

### What this Actor does

It writes rows to a tab in a Google Sheets spreadsheet. The rows can come from an Apify dataset, a JSON URL or a JSON array you paste in. You choose how they're written:

- **append** adds rows below the existing data.
- **replace** overwrites the tab.
- **upsert** updates rows whose key already exists and appends the rest.
- **dedupe-append** appends only rows whose key isn't in the tab yet.

It signs in with a Google service account that you create, so there is no "Sign in with Google" screen and no third-party app needs access to your Google account. Setup takes about 5 minutes and is described below.

The input form opens with **Dry run** turned on and a two-row sample. Run it as is to see the exact rows and columns that would be written. Dry runs write nothing and cost nothing.

### How it handles the usual problems

- **New fields between runs.** If today's data has a field that yesterday's didn't, it becomes a new column on the right. Existing columns never move, and rows are matched to columns by the header text in row 1, not by position.
- **Large exports.** The total size is checked before anything is written. A spreadsheet can hold at most 10,000,000 cells across all tabs. If the export would go over, the run stops with the numbers and nothing is written. Rows are written in batches of up to 5,000 rows or about 1 MB per request.
- **Google rate limits.** Requests are spaced at least 1.1 seconds apart, under Google's default of 60 requests per minute per account. Responses 408, 429 and 5xx, and network errors, are retried up to 8 times with exponential backoff (honouring `Retry-After`).
- **Safe retries.** Every write goes to explicit row numbers and resizes are absolute, so a retried request can't add a row twice.
- **Access errors.** Spreadsheet access is checked before reading your data. Errors say what to change, for example: "The sheet isn't shared with bot@project.iam.gserviceaccount.com. Share it as Editor and run again."
- **Empty input.** In replace mode, an empty dataset leaves the sheet as it was instead of clearing it. A scraper run that returned nothing won't wipe your sheet.
- **Missing tab.** It is created, sized to fit the data.

### Set up Google access (about 5 minutes, once)

You need a Google account. Creating the project and using the Sheets API doesn't need a billing account.

1. **Create a project.** Open https://console.cloud.google.com/projectcreate, enter any name (for example `sheets-export`) and click **Create**.
2. **Enable the Sheets API.** Open https://console.cloud.google.com/apis/library/sheets.googleapis.com, check that your new project is selected at the top, and click **Enable**.
3. **Create a service account.** Open https://console.cloud.google.com/iam-admin/serviceaccounts, click **Create service account**, enter a name (for example `apify-sheets`) and click **Done**. You don't need to give it any roles.
4. **Create a JSON key.** Click the new service account, open the **Keys** tab, choose **Add key > Create new key**, select **JSON** and click **Create**. A `.json` file downloads.
   - If Google says key creation is disabled by an organization policy (common on company Workspace accounts), ask your Workspace admin, or use a personal Google account for the project.
5. **Paste the key.** Open the `.json` file in a text editor, copy all of it, and paste it into **Google service account key** in this Actor's input. Apify stores secret input fields encrypted. The Actor never logs the key or saves it anywhere.
6. **Share the spreadsheet.** In the file you'll see `"client_email": "apify-sheets@your-project.iam.gserviceaccount.com"`. In Google Sheets, click **Share**, paste that email, choose **Editor** and click **Send**.

To stop access later, remove the email from the sheet's sharing settings or delete the key in Google Cloud.

### First run

1. Paste the key, then the spreadsheet URL (straight from the address bar).
2. Optionally set **Tab name**. If it's empty, the first tab is used.
3. Keep **Dry run** on and run. With a key, the preview is checked against the real tab: it reads the existing header, keys and last row, so the row numbers in the output are the ones that would be written. Nothing is written.
4. Turn **Dry run** off and run again to write.

### Input

| Field | What it does |
|---|---|
| `serviceAccountKey` | Full JSON key of the service account (secret). Needed unless `dryRun` is on. |
| `spreadsheetId` | Spreadsheet URL or ID. IDs of any length are accepted. |
| `sheetName` | Tab to write to. Created if missing. Empty = first tab. |
| `mode` | `append` (default), `replace`, `upsert`, `dedupe-append`. |
| `keyColumns` | For `upsert` and `dedupe-append`: header names or field paths that identify a row. Several = combined key. |
| `dryRun` | Output the rows that would be written; write and charge nothing. |
| `datasetId` | Apify dataset ID or URL. |
| `dataUrl` | Public URL returning a JSON array, JSON Lines, or an object with an `items`, `data` or `results` array. |
| `rawData` | JSON array of objects, or of arrays whose first array is the header. |
| `maxRows`, `offset` | Limit and skip input rows. |
| `columns` | Write only these fields, in this order (dot paths such as `price.value`). |
| `excludeColumns` | Fields to leave out when `columns` is empty. |
| `columnRenames` | Header text per field, e.g. `{"price.value": "Price"}`. |
| `addNewColumns` | Add fields missing from the header as new columns (default on). Off: leave them out. |
| `flatten` | Nested objects become dot-path columns (default on). Off: written as JSON text. |
| `arrayHandling` | `join` (default, one cell joined by `arraySeparator`), `json`, or `expand` (one column per item). |
| `arraySeparator` | Default `, `. |
| `valueInputOption` | `RAW` (default) keeps text as text, so a value like `=IMPORTXML(...)` is not run as a formula. `USER_ENTERED` parses values as if typed. |
| `previewLimit` | Rows output by a dry run (default 200). The counts cover all rows. |

If more than one data source is set, `datasetId` is used first, then `dataUrl`, then `rawData`.

#### Example: keep a product sheet up to date

```json
{
    "serviceAccountKey": "<pasted JSON key>",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
    "sheetName": "Products",
    "mode": "upsert",
    "keyColumns": ["url"],
    "datasetId": "<dataset ID>",
    "columns": ["url", "title", "price.value", "inStock"],
    "columnRenames": { "price.value": "Price" }
}
```

Each run updates the rows whose `url` is already in the tab and appends the new ones. In upsert, only the columns this export writes are changed; other columns, such as your own notes, are left alone.

### How the modes treat the sheet

- Row 1 is always the header row.
- **append** writes below the last row that has any value, even if there are empty rows in between.
- **replace** writes the header and rows from row 1, then clears any old values below and to the right. If a replace run fails halfway, the tab holds a mix of old and new rows. Running it again fixes that.
- **upsert** matches keys as text, after trimming spaces, so `101` in the sheet matches `"101"` in the data. If the same key appears several times in the input, the last one is written. If it appears several times in the sheet, only the first of those rows is updated.
- **dedupe-append** keeps the first row of each key from the input and skips keys already in the tab.
- Rows whose key is empty are skipped in both key modes and counted in the summary.

### Run it automatically after another Actor

Save your settings as a task first, so the key is stored once, encrypted, and never has to go in a webhook.

1. In this Actor, fill in the key, spreadsheet, tab and mode. Turn **Dry run** off. Click **Save as a new task**.
2. Open the Actor or task that produces the data, go to its **Integrations** tab, and choose **Connect Actor or task**. Pick the task you saved and the event **Run succeeded**.
3. In the integration's input, set:

   ```json
   { "datasetId": "{{resource.defaultDatasetId}}" }
   ```

   Use the `datasetId` field even though integrations also attach a `payload` field. Apify only lets this Actor read datasets passed in the `datasetId` field.

To do the same through the API, create a webhook on the source Actor for `ACTOR.RUN.SUCCEEDED`. Point it at `https://api.apify.com/v2/actor-tasks/<your task ID>/runs` and use this payload template:

```
{ "datasetId": {{resource.defaultDatasetId}} }
```

The fields you send are merged into the task's saved input.

Don't run two exports into the same tab at the same time. Each run decides its row numbers when it starts.

### Output

- **Dataset.** A dry run outputs one item per row, up to `previewLimit`: `{ "dryRun": true, "action": "append" | "update", "sheetRow": 5, "values": { header: value } }`. Every run ends with one summary item (`"action": "summary"`) with the counts, the spreadsheet link and any warnings. A real run doesn't copy the written rows into the dataset.
- **Key-value store, record `SUMMARY`.** Full details: rows read, appended, updated and skipped (by reason), the final column list, columns added, grid size, total cells in the spreadsheet, API requests and retries.

### Pricing

You pay a small fee for each run that writes rows, plus a fee per row actually written (appended or updated). Rows are counted per batch, after Google confirms the write. Skipped rows, dry runs and runs that write nothing are not charged. See the Pricing tab for current prices.

If you set a maximum cost per run, the Actor writes only as many rows as that allows. In replace mode it won't start at all, rather than leave a half-written sheet.

### Limits

- 10,000,000 cells per spreadsheet, counted across all tabs including empty grid cells. To make room, delete unused rows and columns.
- 50,000 characters per cell. Longer values are cut to fit and counted in `truncatedCells`.
- 18,278 columns per tab. With `arrayHandling: "expand"`, long arrays can create many columns.
- Only Google Sheets files work. An `.xlsx` stored in Drive has to be converted first (File > Save as Google Sheets).
- In append, upsert and dedupe-append, columns this Actor doesn't write are left alone. Replace clears all old values in the tab (formatting stays). Cells it writes are overwritten.

### Moving from lukaskrivka/google-sheets

This Actor accepts that Actor's main input field names, so an existing integration needs few changes. It is a separate project and not affiliated with it.

| Their field | Here |
|---|---|
| `spreadsheetId`, `datasetId`, `rawData`, `offset` | Same meaning |
| `limit` | Same as `maxRows` |
| `mode` `append` / `replace` | Same. `modify`, `read` and `load backup` are not supported. |
| `range` | Only the tab name is used (`Data!A1:Z` writes to tab `Data`) |
| `deduplicateByField` | Same as `keyColumns`. With mode `append` it runs as `dedupe-append`. |
| `columnsOrder` | Listed fields first, the rest in the order first seen |

Sign-in is different: instead of authorizing a Google account in the browser, you paste a service account key once (see above).

### Errors and fixes

| Message starts with | Fix |
|---|---|
| The sheet isn't shared with ... | Share the spreadsheet with that email as Editor. |
| ... can read this spreadsheet but not edit it | Change the email's access from Viewer to Editor. |
| The Google Sheets API is not enabled | Enable it for the project named in the message (step 2). |
| Google rejected the service account key | The key was deleted or disabled. Create a new JSON key. |
| This export needs ... rows x ... columns | The 10M-cell limit. Nothing was written; reduce the data or free up cells. |
| That is a "Publish to the web" link | Use the normal URL from the address bar. |

Found a problem? Open an issue on the Issues tab with the run ID. Please don't include your key.

# Actor input Schema

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

Paste the whole JSON key file of a Google Cloud service account, then share the spreadsheet with that account's email (client_email) as Editor. The README shows how to create one. The key is stored encrypted by Apify and is never logged.

## `spreadsheetId` (type: `string`):

The URL from the browser address bar, or the part between /d/ and /edit.

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

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

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

append: add rows below the existing data. replace: overwrite the tab with this data. upsert: update rows whose key already exists and append the rest. dedupe-append: append only rows whose key is not in the tab yet.

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

Required for upsert and dedupe-append. Column header(s) or field path(s) that identify a row, e.g. url or id. Several columns form a combined key.

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

Output the exact rows and columns that would be written, without writing anything and without charging. With a key, the preview is checked against the real tab (existing header, keys and row numbers). Turn it off to write.

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

An Apify dataset. Pick one, or paste its ID. For automatic runs after another Actor, set it to {{resource.defaultDatasetId}} (see the README).

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

A public URL returning a JSON array, JSON Lines, or an object with an items, data or results array.

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

Rows as objects, or as arrays whose first array is the header.

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

Write at most this many input rows. Empty or 0 means all.

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

Skip this many input rows before reading.

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

Only these fields, in this order. Use dot paths for nested fields, e.g. price.value. Leave empty to write every field in the order first seen.

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

Fields to skip when "Columns to write" is empty.

## `columnRenames` (type: `object`):

Rename headers: {"price.value": "Price"}. Keys are field paths, values are the header text in the sheet.

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

When the data has fields that the tab's header row doesn't, add them as columns on the right. Off: leave those fields out.

## `flatten` (type: `boolean`):

Turn {"price": {"value": 1}} into a price.value column. Off: nested objects are written as JSON text.

## `arrayHandling` (type: `string`):

join: one cell with the items joined by the separator. json: one cell with JSON text. expand: one column per item (tags.0, tags.1, ...).

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

Used when Arrays is join.

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

RAW: text stays text, so values starting with = are not run as formulas. USER_ENTERED: Sheets parses values as if typed (dates, formulas, 00123 becomes 123).

## `previewLimit` (type: `integer`):

How many rows a dry run outputs. Counts in the summary always cover every row.

## Actor input object example

```json
{
  "mode": "append",
  "dryRun": true,
  "rawData": [
    {
      "title": "Blue mug",
      "price": {
        "value": 12.5,
        "currency": "EUR"
      },
      "tags": [
        "kitchen",
        "ceramic"
      ],
      "url": "https://example.com/p/1"
    },
    {
      "title": "Green mug",
      "price": {
        "value": 13,
        "currency": "EUR"
      },
      "tags": [
        "kitchen"
      ],
      "url": "https://example.com/p/2",
      "inStock": true
    }
  ],
  "offset": 0,
  "addNewColumns": true,
  "flatten": true,
  "arrayHandling": "join",
  "arraySeparator": ", ",
  "valueInputOption": "RAW",
  "previewLimit": 200
}
```

# Actor output Schema

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

In a dry run, one item per row that would be written (dryRun: true). Every run ends with one summary item.

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

Counts, final column list, grid size, API requests and warnings.

# 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 = {
    "dryRun": true,
    "rawData": [
        {
            "title": "Blue mug",
            "price": {
                "value": 12.5,
                "currency": "EUR"
            },
            "tags": [
                "kitchen",
                "ceramic"
            ],
            "url": "https://example.com/p/1"
        },
        {
            "title": "Green mug",
            "price": {
                "value": 13,
                "currency": "EUR"
            },
            "tags": [
                "kitchen"
            ],
            "url": "https://example.com/p/2",
            "inStock": true
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("plainsource/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 = {
    "dryRun": True,
    "rawData": [
        {
            "title": "Blue mug",
            "price": {
                "value": 12.5,
                "currency": "EUR",
            },
            "tags": [
                "kitchen",
                "ceramic",
            ],
            "url": "https://example.com/p/1",
        },
        {
            "title": "Green mug",
            "price": {
                "value": 13,
                "currency": "EUR",
            },
            "tags": ["kitchen"],
            "url": "https://example.com/p/2",
            "inStock": True,
        },
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("plainsource/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 '{
  "dryRun": true,
  "rawData": [
    {
      "title": "Blue mug",
      "price": {
        "value": 12.5,
        "currency": "EUR"
      },
      "tags": [
        "kitchen",
        "ceramic"
      ],
      "url": "https://example.com/p/1"
    },
    {
      "title": "Green mug",
      "price": {
        "value": 13,
        "currency": "EUR"
      },
      "tags": [
        "kitchen"
      ],
      "url": "https://example.com/p/2",
      "inStock": true
    }
  ]
}' |
apify call plainsource/google-sheets-export --silent --output-dataset

```

## MCP server setup

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