# Dataset to Google Sheets - Reliable Writer (Validate, Upsert) (`amazing_wizdom/sheets-sync-guardrails`) Actor

Write Apify datasets or JSON rows to Google Sheets with fewer failed runs: preflight validation catches bad input before any charge, batching respects Google quotas, append/replace/upsert modes, readable errors, free dry run.

- **URL**: https://apify.com/amazing\_wizdom/sheets-sync-guardrails.md
- **Developed by:** [Constantine](https://apify.com/amazing_wizdom) (community)
- **Categories:** Automation, Developer tools, Integrations
- **Stats:** 2 total users, 0 monthly users, 100.0% runs succeeded, 1 bookmarks
- **User rating**: No ratings yet

## Pricing

$5.00 / 1,000 cells 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/platform/actors/running/actors-in-store#pay-per-event

## What's an Apify Actor?

Actors are web data automations that power AI and operations. They run on the Apify platform to scrape websites, process data, connect APIs, and automate workflows.
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.
Actors are written with capital "A".

## 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.
The best way to integrate Actors is as follows.

- **AI agents and MCP clients** — the [Apify MCP server](https://docs.apify.com/integrations/mcp.md) at `https://mcp.apify.com` (remote, streamable HTTP, OAuth on first use).
- **Agentic workflows and local Actor development** — [Agent Skills](https://apify.com/.well-known/agent-skills/index.json) with the [Apify CLI](https://docs.apify.com/cli/docs.md): `npm install -g apify-cli`, then `apify login`.
- **JavaScript/TypeScript projects** — the official [JS/TS client](https://docs.apify.com/api/client/js/docs.md): `npm install apify-client`.
- **Python projects** — the official [Python client](https://docs.apify.com/api/client/python/docs.md): `pip install apify-client`.
- **Any other language** — the [REST API](https://docs.apify.com/api/v2.md).

For usage examples, see the [API](#api) section below.

For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt).

# README

## Dataset to Google Sheets — Reliable Writer

**Send your Apify datasets or JSON rows straight into Google Sheets — with validation that catches bad input *before* it costs you a failed run.**

Most Sheets integrations fail *after* they start writing: they hit a quota mid-run, choke on an oversized cell, or die on an auth error — leaving you with a half-filled sheet and a generic `FAILED`. This actor is built the other way around. It checks everything first, tells you exactly what's wrong in plain language, and only then writes — in quota-safe batches. Broken input costs you nothing.

***

### Why this actor

| | This actor | Typical Sheets integration |
|---|---|---|
| **Auth** | Your own Google **service account** — nothing shared | Shared OAuth app (subject to "This app is blocked" and expiring tokens) |
| **Bad input** | Caught in a **free preflight** with row numbers | Fails mid-write with a generic error |
| **Large syncs** | **Batched & paced** under Google's quotas | Often dies partway through |
| **Column safety** | Matched **by name**; refuses ambiguous headers | Positional writes can silently shift data |
| **Billing** | Pay only for **cells actually written** | Often charged per run regardless of outcome |

***

### Features

- 🔐 **Your own service account.** You authenticate with a Google service account you control — no shared app that Google can block, no refresh tokens that expire on you. Two-minute setup below.
- 🧪 **Free preflight validation.** Bad spreadsheet URL, empty dataset, missing upsert keys, cells over Google's 50,000-character limit — all reported *before* any write and any charge, with the exact row numbers.
- 📦 **Quota-aware batching.** Writes are split under Google's request-size limits and paced under the per-minute write quota, so large jobs are built to finish instead of stopping at row 30,000.
- 🔁 **Three write modes.** `append` (add rows), `replace` (overwrite the sheet), and true `upsert` (update rows matching your key columns, append the rest).
- 🧭 **Header safety.** Columns are matched by name, never by position. If your data has columns the sheet doesn't — or the sheet's headers are blank/duplicated — the actor stops with a clear message instead of scrambling your columns.
- 💬 **Actionable errors.** A `403` becomes *"share the spreadsheet with `your-account@…iam.gserviceaccount.com` (Editor) and retry"* — not a stack trace.
- 💸 **Fair pricing.** One metered event: per 1,000 cells actually written. Stop at a budget limit and every charged cell was written — nothing beyond it. `replace` verifies your budget covers the whole write *before* it clears anything.
- 🔗 **Pipeline-friendly.** Reads the run's default dataset when chained after a scraper, and pushes a machine-readable report you can monitor or feed to the next actor.

***

### Setup: Google service account (one time, ~2 minutes)

1. Open the [Google Cloud Console](https://console.cloud.google.com/) and create a project (or use an existing one). Billing is **not** required.
2. Enable the **Google Sheets API**: *APIs & Services → Library → Google Sheets API → Enable*.
3. Create a service account: *IAM & Admin → Service Accounts → Create*. **Skip the optional roles** — none are needed.
4. Open the new account → **Keys → Add Key → Create new key → JSON**. A JSON file downloads. This is your `googleServiceAccountKey`.
5. Copy the account's email (looks like `name@project-id.iam.gserviceaccount.com`) and **Share** your spreadsheet with it as an **Editor**.

That's it — the same key works for every spreadsheet you share with that email.

***

### Input

| Field | Required | Description |
|---|---|---|
| `spreadsheet` | ✅ | Full Google Sheets URL or the bare spreadsheet ID. |
| `googleServiceAccountKey` | for real writes | Service account JSON key (stored encrypted). Not needed for a dry run. |
| `mode` | | `append` (default), `replace`, or `upsert`. |
| `keyColumns` | for upsert | Column names that uniquely identify a row. |
| `datasetId` | | Dataset to read. Omit to use the run's default dataset. |
| `rows` | | Inline JSON rows — an alternative to `datasetId`. |
| `sheetName` | | Target tab. Defaults to the first sheet; created if missing. |
| `valueInputOption` | | `RAW` (default) stores values as-is; `USER_ENTERED` parses dates/formulas. |
| `dryRun` | | `true` = validate and plan only, no writes, no charge. |

#### Example — append a dataset

```json
{
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "mode": "append",
    "datasetId": "YOUR_DATASET_ID",
    "googleServiceAccountKey": { "type": "service_account", "client_email": "…", "private_key": "…" }
}
```

#### Example — upsert by `id`

```json
{
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "mode": "upsert",
    "keyColumns": ["id"],
    "rows": [
        { "id": 1, "status": "updated in place" },
        { "id": 999, "status": "new row, appended" }
    ],
    "googleServiceAccountKey": { "type": "service_account", "client_email": "…", "private_key": "…" }
}
```

#### Example — validate for free

```json
{
    "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_ID/edit",
    "datasetId": "YOUR_DATASET_ID",
    "dryRun": true
}
```

### Output

Every run pushes one machine-readable report to the dataset. A successful sync:

```json
{
    "type": "sync_report",
    "status": "SUCCEEDED",
    "sheetTitle": "Sheet1",
    "mode": "upsert",
    "rowsAppended": 1,
    "rowsUpdated": 1,
    "cellsWritten": 6,
    "writeRequests": 2,
    "durationMs": 1840
}
```

A validation failure instead returns a `preflight_report` (with the failing checks and row numbers) or an `error_report` (with a `code`, `message`, and a `hint` on how to fix it) — and charges nothing.

### Error reference

| Code | Meaning | What to do |
|---|---|---|
| `INVALID_SPREADSHEET_REF` | URL/ID not recognized | Pass the full `docs.google.com/spreadsheets/d/…` URL |
| `EMPTY_DATA` | Nothing to write | Check `datasetId` / `rows` |
| `MISSING_KEY_COLUMNS` · `KEY_COLUMN_NOT_IN_DATA` · `EMPTY_UPSERT_KEY` | Upsert misconfigured | List key columns that exist and are filled in your data |
| `CELL_TOO_LARGE` | Cell over 50,000 chars (Google's limit) | Trim the listed rows |
| `SHEET_CELL_LIMIT` | Would exceed the 10M-cell spreadsheet limit | Split across spreadsheets |
| `HEADER_MISMATCH` · `SHEET_HEADER_BLANK` · `SHEET_HEADER_DUPLICATE` | Sheet headers can't safely receive this data | Fix the header row or use `mode=replace` |
| `PERMISSION_DENIED` | Sheet not shared with the service account | Share with the email shown in the error |
| `BUDGET_TOO_SMALL_FOR_REPLACE` | Run budget doesn't cover a full replace | Raise the budget — nothing was cleared |
| `SPREADSHEET_NOT_FOUND` | Wrong or deleted spreadsheet | Check the URL |

### Pricing

One pay-per-event charge: **$0.005 per 1,000 cells written** (rounded up per batch). Dry runs, validation failures, and permission errors are **free** — you pay only when data lands in your sheet.

### FAQ

**Why a service account instead of "Sign in with Google"?**
A service account is a robot identity you own. It can't be blocked as part of a shared app, and its access never silently expires — the two most common reasons Sheets integrations break. The trade-off is one extra step: sharing the sheet with the account's email.

**Is my key safe?**
The key is marked as a secret input and stored encrypted by Apify. It only grants access to spreadsheets you explicitly share with the account — nothing else in your Google account.

**Which permissions does it need?**
Just the Google Sheets API and Editor access on the specific spreadsheets you share. No Drive-wide access, no IAM roles.

**How much will a run cost?**
Cells × $0.005 / 1000. A 10,000-row sheet with 8 columns = 80,000 cells ≈ $0.40. Validate first with `dryRun` to see the exact cell count for free.

**Does it work with any scraper?**
Yes. Chain it after any actor and omit `datasetId` to pick up that run's default dataset, or point `datasetId` at a specific dataset.

**What if my data has nested objects?**
Nested objects and arrays are written as JSON strings, and the run reports how many cells that affected.

# Actor input Schema

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

Full Google Sheets URL (https://docs.google.com/spreadsheets/d/...) or the bare spreadsheet ID.

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

Tab to write into. Defaults to the first sheet. Created automatically if missing (see option below).

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

append = add rows after existing data; replace = clear the sheet and write headers + rows; upsert = update rows matching key columns, append the rest.

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

Column names that uniquely identify a row. Required when mode = upsert.

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

Apify dataset to read rows from. If omitted, the actor reads inline `rows` below, or the run's default dataset.

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

Array of JSON objects to write, e.g. \[{"name":"Alice","score":42}]. Alternative to datasetId.

## `createSheetIfMissing` (type: `boolean`):

If the named sheet tab does not exist, create it automatically. When false, a missing tab is a hard error.

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

RAW stores values as-is; USER\_ENTERED parses them like typed input (dates, formulas).

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

Run the full preflight validation and produce the write plan without touching the spreadsheet and without charges. Prefilled ON so your first run succeeds for free — turn it OFF (and add your service account key) to actually write.

## `googleServiceAccountKey` (type: `object`):

Service account credentials JSON. Share the target spreadsheet with the service account email (Editor role). Not required for dry runs.

## Actor input object example

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1ReplaceWithYourGoogleSheetIdHere0123456789/edit",
  "mode": "append",
  "rows": [
    {
      "id": 1,
      "name": "Ada Lovelace",
      "score": 95
    },
    {
      "id": 2,
      "name": "Alan Turing",
      "score": 88
    }
  ],
  "createSheetIfMissing": true,
  "valueInputOption": "RAW",
  "dryRun": true
}
```

# Actor output Schema

## `report` (type: `string`):

The run's sync / preflight / error report as dataset items.

## `reportCsv` (type: `string`):

The same report exported as CSV.

# 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/1ReplaceWithYourGoogleSheetIdHere0123456789/edit",
    "rows": [
        {
            "id": 1,
            "name": "Ada Lovelace",
            "score": 95
        },
        {
            "id": 2,
            "name": "Alan Turing",
            "score": 88
        }
    ],
    "dryRun": true
};

// Run the Actor and wait for it to finish
const run = await client.actor("amazing_wizdom/sheets-sync-guardrails").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/1ReplaceWithYourGoogleSheetIdHere0123456789/edit",
    "rows": [
        {
            "id": 1,
            "name": "Ada Lovelace",
            "score": 95,
        },
        {
            "id": 2,
            "name": "Alan Turing",
            "score": 88,
        },
    ],
    "dryRun": True,
}

# Run the Actor and wait for it to finish
run = client.actor("amazing_wizdom/sheets-sync-guardrails").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print("💾 Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"])
for item in client.dataset(run["defaultDatasetId"]).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/1ReplaceWithYourGoogleSheetIdHere0123456789/edit",
  "rows": [
    {
      "id": 1,
      "name": "Ada Lovelace",
      "score": 95
    },
    {
      "id": 2,
      "name": "Alan Turing",
      "score": 88
    }
  ],
  "dryRun": true
}' |
apify call amazing_wizdom/sheets-sync-guardrails --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "command": "npx",
            "args": [
                "mcp-remote",
                "https://mcp.apify.com/?tools=amazing_wizdom/sheets-sync-guardrails",
                "--header",
                "Authorization: Bearer <YOUR_API_TOKEN>"
            ]
        }
    }
}

```

## OpenAPI specification

Download the OpenAPI definition: https://api.apify.com/v2/actors/D5U1EeuNCqIwDQQMI/builds/op48hmAb3Qdzht55x/openapi.json