# Google Sheets Import & Export - No OAuth, API & AI Agents (`feedsmith/google-sheets-sync`) Actor

Append, replace, upsert or read Google Sheets with a service account: no OAuth popup, works from the API, schedules and AI agents (MCP).

- **URL**: https://apify.com/feedsmith/google-sheets-sync.md
- **Developed by:** [TRUONG VAN HOA](https://apify.com/feedsmith) (community)
- **Categories:** Integrations, Automation, Developer tools
- **Stats:** 1 total users, 0 monthly users, 0.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $2.00 / 1,000 sheet synceds

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 — no OAuth, works with the API & AI agents

Sync any data into Google Sheets, or export a tab out of it, using a **Google service account** —
no interactive OAuth login, no popup that can fail or expire. That makes it work reliably from the
Apify API, from Scheduled runs, from Integrations, and from AI agents through the Apify MCP server,
none of which can click through a browser consent screen.

- **append** — add rows below the existing data, mapping incoming keys onto existing columns by name.
- **replace** — clear the tab and rewrite it (with an automatic backup of the old values).
- **upsert** — update rows that match a key, insert the rest.
- **read** — export a tab to the dataset as clean JSON.

### Why a service account instead of "Login with Google"?

An OAuth login button only works when a human is present in a browser to click "Allow". It cannot
run unattended on a schedule, cannot be called from the Apify API, and stops working the moment a
token expires or a permission screen changes. A service account is a robot Google identity that
you create once, in your own Google Cloud project: it authenticates with a signed key that never
expires and never shows a popup, so this Actor works identically whether you start it by hand, on a
schedule, through an Integration, or from an AI agent.

### Setup: create a service account (5 minutes, one-time)

1. **Create a Google Cloud project** — go to [console.cloud.google.com](https://console.cloud.google.com/), click the project dropdown (top bar) → **New Project** → give it a name → **Create**.
2. **Enable the Google Sheets API** — with your project selected, go to **APIs & Services → Library**, search for "Google Sheets API", open it, click **Enable**.
3. **Create a service account** — go to **APIs & Services → Credentials → Create Credentials → Service account**, give it a name (e.g. "sheets-sync"), click **Create and Continue**, then **Done** (no roles needed).
4. **Create a JSON key** — open the service account you just created → tab **Keys** → **Add Key → Create new key** → type **JSON** → **Create**. A `.json` file downloads — open it and copy the whole content.
5. **Share the spreadsheet** — open your Google Sheet → **Share** → paste the service account's `client_email` (looks like `sheets-sync@your-project.iam.gserviceaccount.com`, also visible inside the JSON key) → give it **Editor** access → **Share**.

Paste the full JSON file content into the **Service account key** input. That's it — no further
authorization step, ever, for this key.

### Modes

| Mode | Before | After |
| --- | --- | --- |
| `append` | Tab has 100 rows, columns `id, name, email` | Your new rows are added below; a new key like `phone` becomes a new column on the right, existing rows get a blank `phone` cell |
| `replace` | Tab has last week's export | Tab is cleared and rewritten with this run's data; last week's values are saved as JSON to the key-value store record `BACKUP` |
| `upsert` (`keyColumns: ["id"]`) | Row with `id=42` exists | That row's columns are updated in place (columns not present in the incoming item are left untouched); rows with unmatched `id`s are appended |
| `read` | Tab has data | Every row becomes one dataset item, keyed by the header row |

### Input

| Field | Example | Notes |
| --- | --- | --- |
| `serviceAccountKey` | *(paste JSON)* | Secret input, encrypted by Apify, never logged |
| `spreadsheet` | `"https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0"` or a bare ID | Any ID length; a URL's `gid` selects a tab unless `sheetName` is set |
| `sheetName` | `"Leads"` | Default: first tab (or the URL's `gid` tab). Created automatically for write modes if missing |
| `mode` | `"append"` | `"replace"` | `"upsert"` | `"read"` | Default `append` |
| `keyColumns` | `["id"]` | Required for `upsert` |
| `backupBeforeReplace` | `true` | `replace` only, default `true` |
| `datasetId` | `"{{resource.defaultDatasetId}}"` | One of `datasetId`/`rawData`, write modes only |
| `rawData` | `[{"id":1,"name":"Ann"}]` | Array of objects, or array of arrays with a header row first |
| `offset`, `limit` | `0`, `5000` | `datasetId` paging |
| `fields` | `["id", "address.city"]` | Keep only these columns, dotted paths reach into nested objects |
| `omitFields` | `["password"]` | Drop these columns |
| `flatten` | `true` | Nested objects → `a.b.c` columns; arrays of primitives joined with `, `; arrays of objects → JSON string |
| `deduplicateBy` | `["email"]` | Drop incoming duplicates before writing, keep the last |
| `valueInputOption` | `"RAW"` | `"USER_ENTERED"` | `USER_ENTERED` lets Sheets parse dates/formulas like typing |
| `allowFormulas` | `false` | With `USER_ENTERED`, values starting `=`, `+`, `-`, `@` are escaped unless this is `true` (formula-injection guard) |
| `readRange` | `"Sheet1!A1:F200"` | `read` only |
| `maxItems` | `5000` | `read` only |
| `dryRun` | `false` | Validate + compute the plan without touching the sheet; not charged |

#### Example: append a scraper's output

Chain after any Actor via **Integrations**, setting `datasetId` to `{{resource.defaultDatasetId}}`:

```json
{
  "serviceAccountKey": "{{secrets.GOOGLE_SA_KEY}}",
  "spreadsheet": "1AbCDefGhIjKlmNoPQRstuVWxyz0123456789ABCDEfg",
  "mode": "append",
  "datasetId": "{{resource.defaultDatasetId}}"
}
```

#### Example: upsert from an API call

```bash
curl "https://api.apify.com/v2/acts/YOUR_USERNAME~google-sheets-sync/run-sync-get-dataset-items?token=$APIFY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "serviceAccountKey": "...",
    "spreadsheet": "1AbCDefGhIjKlmNoPQRstuVWxyz0123456789ABCDEfg",
    "mode": "upsert",
    "keyColumns": ["order_id"],
    "rawData": [{"order_id": "A1002", "status": "shipped"}]
  }'
```

#### Example: from an AI agent (Apify MCP)

An agent that already has data in hand (no dataset to point to) can pass it directly as `rawData`:

```json
{ "spreadsheet": "1AbC...", "mode": "append", "rawData": [{"task": "Follow up", "done": false}] }
```

### Output

`OUTPUT` (key-value store record):

```json
{
  "mode": "upsert",
  "spreadsheetId": "1AbCDefGhIjKlmNoPQRstuVWxyz0123456789ABCDEfg",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1AbCDefGhIjKlmNoPQRstuVWxyz0123456789ABCDEfg",
  "sheetName": "Leads",
  "rowsRead": 0,
  "rowsAppended": 3,
  "rowsUpdated": 12,
  "rowsSkippedDuplicate": 1,
  "newColumns": ["phone"],
  "totalCellsAfter": 4200,
  "warnings": [],
  "dryRun": false
}
```

For `mode: "read"`, the exported rows are pushed to the dataset (one row = one item). For write
modes, one summary item matching `OUTPUT` is also pushed to the dataset, so calling
`run-sync-get-dataset-items` returns the result directly.

### Pricing

Pay per event, charged only once the sheet write/read actually succeeds — **a failed run costs
nothing**:

| Event | Price | When |
| --- | --- | --- |
| `sheet-sync` | $0.002 | Once per successful run |
| `rows-1000` | $0.005 | Per started block of 1,000 rows written or read |

Examples: a daily sync of 500 rows costs $0.007 per run, about $0.21 a month. An hourly sync of 500
rows is about $5 a month. A one-off import of 50,000 rows costs $0.252.

If a run would exceed **Max total charge**, it writes only the rows it can afford and says so in
`warnings` — it never leaves your spreadsheet half-written past the point your budget allows further
charges.

### FAQ

**Is my key safe?** `serviceAccountKey` is an Apify secret input: encrypted at rest, never shown in
logs, and used only in memory for the duration of the run. Nothing is stored by this Actor. Revoke
the key any time from Google Cloud Console → your service account → Keys, with zero code changes
needed here.

**What stops someone injecting a formula into my sheet through the data?** With the default
`valueInputOption: "RAW"`, Sheets never interprets cell content as a formula. If you opt into
`USER_ENTERED` (to get automatic date/number parsing), any incoming string starting with `=`, `+`,
`-` or `@` is automatically prefixed with an apostrophe so it is stored as text, not evaluated —
unless you explicitly set `allowFormulas: true`.

**What are the limits?** Google Sheets caps a spreadsheet at 10,000,000 cells total (across every
tab) and a cell at 50,000 characters; this Actor checks the cell limit *before* writing and fails
with the exact row count that would fit, and truncates any single oversized cell with a warning
rather than a silent data-loss error. Sheets' write quota is 60 requests/minute/user; this Actor
paces itself under that automatically and retries `429`/`5xx` responses with backoff.

**Can I use any spreadsheet ID length?** Yes — this Actor accepts an ID of any length, or a full
Sheets URL (the ID and `gid` are parsed out for you).

**Is this affiliated with Google?** No. This is an independent tool that uses the official public
Google Sheets API with your own service-account credentials in your own Google Cloud project.

**Something wrong or missing?** Open an issue on the Actor's Issues tab.

# Actor input Schema

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

Full content of a Google Cloud service-account JSON key file. Never expires, no OAuth popup. See the README for the 5-step setup (create project, enable Sheets API, create service account, create key, share the sheet with the key's client\_email as Editor).

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

A spreadsheet ID (any length) or a full Google Sheets URL. A URL's #gid=... selects that tab unless "Sheet/tab name" below is set.

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

Target tab name. Leave empty to use the first tab (or the URL's gid tab). Write modes create the tab if it does not exist yet.

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

Write modes only (append/replace/upsert): an Apify dataset ID to read items from, e.g. {{resource.defaultDatasetId}} when chaining after another Actor via Integrations. Provide this OR "Raw data", not both.

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

Write modes only: a JSON array of objects, or an array of arrays with the header row first. Provide this OR "Apify dataset ID", not both.

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

Skip this many items at the start of the dataset before reading (datasetId only).

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

Read at most this many items from the dataset (datasetId only). Leave empty to read all of them.

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

append never clears anything. replace clears the tab first (values only; a JSON backup of the old values is saved to the run's key-value store by default). upsert needs "Key column(s)". read exports the tab and ignores the data source fields above.

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

Column name(s) that uniquely identify a row, e.g. \["id"] or \["order\_id", "sku"]. Required when mode = upsert. A number and the equivalent string (123 vs "123") are treated as the same key.

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

mode = replace only: before clearing the tab, save its current values as JSON to the key-value store record "BACKUP" so you can restore them if needed.

## `readRange` (type: `string`):

mode = read only: an A1 range like "Sheet1!A1:F200" to export just part of the tab. Leave empty to read the whole tab.

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

mode = read only: stop after exporting this many rows. Leave empty for no cap.

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

Write modes only: keep only these keys, in this order, as columns. Dotted paths reach into nested objects, e.g. "address.city". Leave empty to keep every field.

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

Write modes only: drop these columns after flattening/field selection, e.g. \["password", "internalId"].

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

Write modes only: turn nested objects into "a.b.c" columns, join arrays of plain values with ", ", and JSON-stringify arrays of objects. Turned off, nested values are written as a single JSON-string cell.

## `deduplicateBy` (type: `array`):

Write modes only: before writing, drop incoming rows that share the same value(s) for these column name(s), keeping the last one. Leave empty to keep every incoming row.

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

USER\_ENTERED lets Sheets interpret values as it would if you typed them (formulas, dates, currency). With USER\_ENTERED, any string starting with =, +, -, or @ is prefixed with an apostrophe unless "Allow formulas" is on (formula-injection guard).

## `allowFormulas` (type: `boolean`):

Only relevant with valueInputOption = USER\_ENTERED. When off (default), strings starting with =, +, -, or @ are escaped so incoming data can never execute a spreadsheet formula. Turn on only if you intentionally write formulas.

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

Validate credentials, tab access and compute the plan (rows to append/update, new columns, cells after) without changing the sheet. Written to OUTPUT only. Not charged.

## Actor input object example

```json
{
  "offset": 0,
  "mode": "append",
  "backupBeforeReplace": true,
  "flatten": true,
  "valueInputOption": "RAW",
  "allowFormulas": false,
  "dryRun": false
}
```

# Actor output Schema

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

Summary of what was read from or written to the sheet.

# 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": ""
};

// Run the Actor and wait for it to finish
const run = await client.actor("feedsmith/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 = { "spreadsheet": "" }

# Run the Actor and wait for it to finish
run = client.actor("feedsmith/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 '{
  "spreadsheet": ""
}' |
apify call feedsmith/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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