# Google Sheets Sync: Export & Import (`datadock-labs/google-sheets-sync`) Actor

Send any Apify dataset to Google Sheets: append, replace or upsert rows by key, with nested data flattened into columns. Import sheets back into datasets. Uses a service account, so scheduled runs and integrations never need a re-login.

- **URL**: https://apify.com/datadock-labs/google-sheets-sync.md
- **Developed by:** [DataDock Labs](https://apify.com/datadock-labs) (community)
- **Categories:** Integrations, Automation, Developer tools
- **Features:** Limited permissions
- **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 written or importeds

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 Sync: Export & Import

Send any Apify dataset to **Google Sheets**, keep a sheet **up to date with upserts**, or **import a sheet** into a dataset. It authenticates with a **Google service account**, so scheduled runs and integrations keep working without ever logging in again.

- ➕ **Append** new rows below existing data
- 🔄 **Replace** the sheet content with the latest results
- 🔑 **Upsert by key** (for example `url` or `id`): update rows that already exist, add the new ones
- 📥 **Import** a sheet or range into an Apify dataset (optionally rebuilding nested objects)
- 🧩 **Nested data flattened into columns** like Apify's CSV export (`address/city`, `images/0`), or dot notation, or JSON
- 🛡️ **Formulas in your own columns are never overwritten**; values are stored exactly as they are (no accidental formulas)
- ⚡ **Works as an integration**: attach it to any scraper and every finished run lands in your sheet
- 🧭 **Clear error messages**: it tells you exactly which e-mail to share the sheet with, or which API to enable

### Why a service account?

Tools that use "Sign in with Google" store a token that expires or gets revoked, so scheduled runs start failing. A service account is a robot Google account with a key that doesn't expire. You share a spreadsheet with it once, like you would with a colleague, and it can only touch the spreadsheets you shared.

### Setup in 5 minutes

1. Open [Google Cloud Console](https://console.cloud.google.com/) and create a project (any name). No billing is needed: the Google Sheets API is free. If the console offers a free trial, you can skip it.
2. Go to **APIs & Services → Library**, search for **Google Sheets API** and click **Enable**.
3. Go to **IAM & Admin → Service Accounts → Create service account**. Give it a name; 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** and add the service account's e-mail (`...@...iam.gserviceaccount.com`, also in the file as `client_email`) as **Editor**.
6. In this Actor, paste the spreadsheet URL and the **whole content of the JSON file** into **Service account key**. The key is stored encrypted.

### How to use it

#### As an integration (recommended)

1. Open the scraper whose results you want in Sheets and go to its **Integrations** tab.
2. Click **Add integration**, choose **Google Sheets Sync** and fill in the spreadsheet URL, the key and the mode.
3. Make sure **Dataset to export** contains `{{resource.defaultDatasetId}}` (it is prefilled when you add the integration). Each finished run of the scraper is then exported automatically. The Actor runs with limited permissions, so it can only read the dataset passed in this field.

#### Run it directly

Pick a dataset in **Dataset to export** (or paste JSON into **Rows**), choose a mode and click **Start**.

#### Via the API

```bash
curl -X POST "https://api.apify.com/v2/acts/datadock-labs~google-sheets-sync/runs?waitForFinish=120" \
  -H "Authorization: Bearer $APIFY_TOKEN" -H "Content-Type: application/json" \
  -d '{
    "mode": "upsert",
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit",
    "sheetName": "Products",
    "keyColumns": ["url"],
    "rows": [{ "url": "https://shop.example/a", "price": 19.9 }],
    "serviceAccountKey": "{ ...contents of the JSON key file... }"
  }'
```

### Modes

| Mode | What happens |
|---|---|
| `append` | Rows are added below the existing data. New fields become new columns on the right (unless you turn **Add new columns** off). |
| `replace` | The tab is cleared, then the header and all rows are written. |
| `upsert` | Rows whose **key columns** match an existing row update only the columns present in the data; other rows are added at the bottom. If the input contains the same key twice, the last one wins. |
| `import` | The tab (or **Range to import**) is read; the first row becomes the field names and every other non-empty row becomes a dataset item. |

### Input

| Field | Description |
|---|---|
| `mode` | `append` (default), `replace`, `upsert` or `import` |
| `spreadsheetId` | Spreadsheet URL or ID. A URL with `#gid=` selects that tab. |
| `sheetName` | Tab name. Missing tabs are created. Empty = first tab. |
| `serviceAccountKey` | Full JSON key of the service account (secret) |
| `keyColumns` | Key column(s) for upsert, for example `["url"]` |
| `datasetId` | Dataset to export. Empty in integrations: the triggering run's dataset is used. |
| `rows` | JSON array of objects to write instead of a dataset |
| `columns` / `excludeColumns` | Choose, order or drop columns. A parent name (`address`) matches its nested fields (`address/city`). |
| `flattenMode` | `apify` (default: `address/city`, `tags/0`), `dot` (`address.city`, lists as JSON) or `none` (nested values as JSON) |
| `addNewColumns` | Add unknown fields as new columns (default `true`) |
| `parseValues` | Let Google parse dates, numbers and formulas from text (default `false`: values are stored as they are) |
| `formatHeader` | Bold and freeze a newly written header row (default `true`) |
| `maxItems` | Export at most this many items |
| `importRange`, `importFormatted`, `unflatten` | Import options |
| `googleAccessToken` | Advanced: an OAuth access token instead of a service account |

### Output

After an export, the run's key-value store contains an `OUTPUT` record:

```json
{
    "mode": "upsert",
    "sheet": "Products",
    "sheetCreated": false,
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0",
    "rowsAppended": 12,
    "rowsUpdated": 230,
    "columnsAdded": ["discount"],
    "truncatedCells": 0,
    "stoppedByCostLimit": false,
    "warnings": []
}
```

In `import` mode the rows are stored in the run's dataset, ready to download as JSON, CSV or Excel or to pass to another Actor.

### Pricing

Pay per event, no subscription:

| Event | Price |
|---|---|
| Sync run (charged once per run that writes or imports at least one row) | $0.002 |
| Row written or imported | $0.00001 ($0.01 per 1,000 rows) |

Example: exporting 1,000 rows costs $0.012. Runs that fail before writing anything are not charged. You can set a maximum cost per run; the Actor stops cleanly when it is reached.

### FAQ

**Do I need Google Cloud billing?** No. The Google Sheets API is free and service accounts don't need a billing account.

**Is my key safe?** The key is stored as an encrypted secret input and is only used to call the Google Sheets API with the spreadsheets scope. The service account can only open spreadsheets that you explicitly share with it, and you can delete the key in Google Cloud at any time.

**Will it overwrite my formulas?** No. Upserts only write the columns that exist in your data, and values are written as plain values unless you turn on **Parse values**. Text like `=SUM(...)` coming from scraped data stays text.

**Why can't it create a new spreadsheet?** Files created by a service account belong to the robot account and would be invisible to you. Create an empty spreadsheet, share it, and the Actor creates the tabs it needs.

**What are the limits?** Google allows 10 million cells per spreadsheet and 50,000 characters per cell (longer text is truncated with a warning). Write-quota errors from Google are retried automatically with back-off.

**I get "no permission".** Share the spreadsheet with the service account's e-mail as Editor. The error message shows the exact address.

**I get "Google Sheets API is turned off".** Enable the Google Sheets API in the same Google Cloud project as your service account. The error message contains the direct link.

### More from DataDock Labs

- [Website Screenshot & PDF API](https://apify.com/datadock-labs/website-screenshot-pdf): full-page screenshots and PDFs of any website. Pair it with this Actor to keep a visual log of pages in a sheet.

### Support

Something not working? Open an issue on the **Issues** tab with your input (never paste your key). You can also email <datadocklabs@gmail.com> or see the [support page](https://datadock-labs.github.io/support.html). More tools from DataDock Labs: [datadock-labs.github.io](https://datadock-labs.github.io/). If this Actor saves you time, a short review on its Store page helps other people find it. Thank you!

### Changelog

- **0.1** (November 2026): first release. Append, replace, upsert by key, import, flattening modes, integration support, service account and access token authentication.

# Actor input Schema

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

<b>Append</b> adds rows below existing data. <b>Replace</b> clears the sheet first. <b>Upsert</b> updates rows with the same key and adds the rest. <b>Import</b> reads the sheet into this run's dataset.

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

Copy the address of your Google Sheet from the browser. If the URL contains #gid=..., that tab is used unless you set a sheet name.

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

Name of the tab at the bottom of the spreadsheet. Missing tabs are created. Empty = first tab.

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

Paste the whole JSON key file of a Google service account, and share the spreadsheet with the account's e-mail (client_email) as Editor. Stored encrypted. The README shows the 5-minute setup.

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

Column(s) that identify a row, for example <code>url</code> or <code>id</code>. Rows with the same key are updated instead of added.

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

Leave empty when the Actor runs as an integration: the dataset of the run that triggered it is used.

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

Only these columns, in this order. A parent name also selects its nested fields (<code>address</code> selects <code>address/city</code>). Empty = all columns.

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

Columns to leave out. A parent name also excludes its nested fields.

## `flattenMode` (type: `string`):

How nested objects and lists become columns.

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

When the data has fields that the sheet header doesn't, add them as new columns on the right. Off = skip those fields.

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

Let Google turn text such as <code>2026-01-31</code> into dates and <code>=SUM(A1:A3)</code> into formulas. Off = values are stored exactly as they are.

## `formatHeader` (type: `boolean`):

Applied when the Actor writes a new header.

## `createSheet` (type: `boolean`):

Create the sheet tab when it doesn't exist yet.

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

Export at most this many dataset items.

## `importRange` (type: `string`):

Optional A1 range such as <code>A1:F500</code>. The first row is used as column names. Empty = the whole tab.

## `importFormatted` (type: `boolean`):

Import values as displayed (for example "$1,200.00") instead of raw numbers.

## `unflatten` (type: `boolean`):

Turn columns like address/city back into nested objects (uses the Nested data setting).

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

JSON array of objects to write, useful from the API, Make or Zapier.

## `googleAccessToken` (type: `string`):

Advanced alternative to the service account: an OAuth 2.0 access token with the spreadsheets scope. Tokens expire after about an hour.

## Actor input object example

```json
{
  "mode": "append",
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit",
  "flattenMode": "apify",
  "addNewColumns": true,
  "parseValues": false,
  "formatHeader": true,
  "createSheet": true,
  "importFormatted": false,
  "unflatten": false
}
```

# Actor output Schema

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

No description

## `importedRows` (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 = {
    "spreadsheetId": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit"
};

// Run the Actor and wait for it to finish
const run = await client.actor("datadock-labs/google-sheets-sync").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 = { "spreadsheetId": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit" }

# Run the Actor and wait for it to finish
run = client.actor("datadock-labs/google-sheets-sync").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 '{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit"
}' |
apify call datadock-labs/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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

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/MirJoB9MH2a3SZ01X/builds/RMukWdVJfWBhVkztS/openapi.json