# Google Sheets Safe Upsert (Validated Diff) (`kumo-data-tools/google-sheets-safe-upsert`) Actor

Upsert JSON rows into a Google Sheets tab safely: typed schema validation, a cell-by-cell diff preview, and only the approved diff is written. Same operationId sent 10 times = written once. Made for AI agents, MCP clients and automations.

- **URL**: https://apify.com/kumo-data-tools/google-sheets-safe-upsert.md
- **Developed by:** [Kumo Data Tools](https://apify.com/kumo-data-tools) (community)
- **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.02 / verified write

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 does Google Sheets Safe Upsert do?

**Google Sheets Safe Upsert** writes JSON rows into a Google Sheets tab **without breaking it**. Every row is checked against your **typed schema**, you get a **cell-by-cell diff preview**, and only the **approved diff** is written. Re-sending the same `operationId` never writes twice — not even 10 times in parallel.

It is made for **AI agents, MCP clients and automations** (Make, n8n, Zapier, your own scripts) that produce tables — inventory, prices, KPIs, product feeds, reports — and must not corrupt the sheet a human relies on.

- ✅ **Typed validation, all-or-nothing** — `string`, `number`, `integer`, `boolean`, `date`, plus `required`, `enum`, `min`/`max`, `minLength`/`maxLength`, `pattern`. One bad cell or unknown field → **zero cells written**, and you get every error with its row and column.
- 🔍 **Diff preview** — `mode: plan` returns inserts and updates (before → after for each cell) plus a `planHash`. Nothing is written.
- 🔐 **Approved diff only** — `mode: apply` with `approvedPlanHash` writes only if the current diff is exactly the approved plan (same cells, same values, same schema). If the sheet or the input changed, nothing is written and you get the new plan.
- ♻️ **Idempotent upserts** — the same `operationId` is applied once; later runs return `duplicate` with the original result. Runs on the same tab are serialized with a lock (tested: 10 parallel runs of one operation → 1 write, 9 duplicates).
- ⚛️ **Atomic write + read-back check** — data, ledger entry and lock release go out in one atomic Google Sheets batch, and the tab is compared again right after. A run only succeeds, and is only charged, when that check passes.
- 🧱 **Nothing outside your schema is touched** — other columns stay as they are, rows are never deleted, and text such as `=IMPORTXML(...)` is stored as plain text, never executed as a formula.

### How to write JSON to Google Sheets safely

**Try it first:** the prefilled input runs a read-only `plan` against our example sheet, so you can see the output before any setup. The example sheet can never be written to.

1. **Share** your spreadsheet with this service account as **Editor**: `sheets-writer@kumo-sheets-writer.iam.gserviceaccount.com`
2. **Allow your Apify account**: add a tab named `kumo_access` and put your **Apify user ID** in cell A1 (Apify Console → Settings → API & Integrations; or just run the Actor once — the error message prints your ID). This stops other Apify users from writing to a sheet you shared with the same service account.
3. Make sure row 1 of your target tab contains the column names from your schema, or leave the tab empty and the Actor writes the header.
4. Run with `mode: plan`, check the diff, then run with `mode: apply`, the `planHash` and an `operationId`.
   AI agents can do it in one call: `mode: apply`, `skipApproval: true`, `operationId` (validation, idempotency and read-back still apply).

The Actor creates one hidden tab, `_kumo_ledger`, that remembers operation IDs, and a short-lived hidden lock tab (`_kumo_lock_…`) while it writes. Don't edit them.

### How much does it cost to write to Google Sheets?

Pay per event — platform usage is included, you only pay for these events:

| Event | When | Price |
|---|---|---|
| `diff-apply` | A run that wrote changes and passed the read-back check | $0.02 per run |
| `diff-plan` | A run that returned a plan, or an apply with nothing to write / a duplicate | $0.002 per run |
| Actor start | Every run (Apify standard) | $0.00005 |

One `diff-apply` covers up to **1,000 rows**. So writing 1,000 rows costs about **$0.02**, and a plan + apply cycle about **$0.022**. Runs rejected by validation, access checks or a changed plan are not charged for either event.

### Input example

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
  "sheetName": "Inventory",
  "mode": "plan",
  "keyColumns": ["sku"],
  "columns": [
    {"name": "sku", "type": "string", "pattern": "[A-Z]{3}-\\d{3}"},
    {"name": "qty", "type": "integer", "min": 0},
    {"name": "price", "type": "number", "min": 0},
    {"name": "active", "type": "boolean"},
    {"name": "updated", "type": "date"}
  ],
  "rows": [
    {"sku": "ABC-001", "qty": 5, "price": 9.5, "active": true, "updated": "2026-10-21"},
    {"sku": "ABC-002", "qty": 0, "price": 12, "active": false, "updated": "2026-10-20"}
  ]
}
```

To write it, send the same input with `"mode": "apply"`, `"operationId": "inventory-2026-10-21"` and `"approvedPlanHash": "<planHash from the plan run>"`.

### Output example

The key-value store record `OUTPUT` holds the full result:

```json
{
  "status": "applied",
  "verified": true,
  "writtenCells": 7,
  "message": "Applied {'insert': 1, 'update': 2, ...}. Read-back check: the sheet now matches the input.",
  "plan": {
    "planHash": "404ae3e2…",
    "summary": {"insert": 1, "update": 2, "delete": 0, "unchanged": 0, "unkeyedSheetRowsLeftAlone": 0},
    "changes": [
      {"op": "update", "row": 2, "key": {"sku": "ABC-001"},
       "cells": [{"column": "qty", "before": 4, "after": 5}]},
      {"op": "insert", "key": {"sku": "ABC-002"},
       "values": {"sku": "ABC-002", "qty": 0, "price": 12, "active": false, "updated": "2026-10-20"}}
    ]
  }
}
```

`status` is one of `planned`, `applied`, `noop`, `duplicate` (run succeeds) or `plan_changed`, `rejected`, `unverified`, `error` (run fails, nothing charged). The **dataset** shows a preview of the first 100 changes or validation errors; the complete list is always in `OUTPUT`.

### FAQ

#### Can an AI agent use this through MCP?

Yes. Call the Actor with `mode: apply`, `skipApproval: true` and a unique `operationId` per logical write. If the agent retries after a timeout, the retry returns `duplicate` instead of writing again. Use `mode: plan` first when a human should approve the change.

#### What happens if two runs write to the same tab at the same time?

They are serialized: the second waits for the first (up to about 2.5 minutes) and then works on the updated tab. If it cannot get its turn, it fails with nothing written.

#### Can someone edit the sheet while the Actor writes?

The lock keeps runs of this Actor apart, but it cannot stop a person typing into the same rows during the one or two seconds of a write. If that happens, the read-back check fails, the run fails and it is not charged — check the tab.

#### Does it delete rows?

No. This version only inserts and updates. Rows that are not in your input stay as they are.

#### What are the limits?

Up to **1,000 rows** and **1 MB** of row data per run (measured: 1,000 inserts with read-back ≈ 2.5 s of Google time). One tab per run. Keys must be unique in the input and in the sheet. Integers must be within ±9,007,199,254,740,991 — store longer IDs as `string`. `date` values are `YYYY-MM-DD` and are written as real dates; times are not supported yet (use a `string` column). The service account shares Google's per-minute request budget across all users; under heavy load a run waits and retries automatically.

#### What if I delete rows from `_kumo_ledger`?

Then the deleted `operationId`s can be written again. Leave the ledger alone if you rely on idempotency.

#### How is my data handled?

- The Actor reads and writes only the spreadsheet you name, and only after the `kumo_access` check passes. Users not listed there never see your tab names or data.
- Your rows pass through the run and are stored only in **your** Apify run storage (dataset and `OUTPUT`) under your Apify retention settings. The developer does not copy them elsewhere.
- Google access uses a service account whose key is stored as an encrypted Apify secret. No Google password or OAuth token of yours is collected. Remove the share at any time to revoke access.
- Anything shared with the service account is technically reachable by whoever holds its key (the developer). The developer does not open shared sheets outside your runs. Share only the spreadsheet you want this Actor to write to.
- Data is not used for advertising or training and is not shared with third parties beyond Google (which processes the API calls) and Apify (which runs the Actor).

#### How do I get help?

Open an issue on the **Issues** tab. Issues are answered within 14 days, usually much sooner.

***

Google Sheets is a trademark of Google LLC. This Actor is not affiliated with or endorsed by Google.

# Actor input Schema

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

The Google Sheets spreadsheet to write to. Share it with the service account shown in the README as Editor, and add a `kumo_access` tab listing your Apify user ID.

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

The tab (sheet) inside the spreadsheet. Row 1 must be the header, or the tab must be empty.

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

`plan` = validate and show the diff, write nothing. `apply` = write the diff (needs `operationId`, and `approvedPlanHash` unless `skipApproval`).

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

Column(s) that identify a row. Rows are matched on these; keys must be unique in the input and in the sheet.

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

One object per column: `name`, `type` (string | number | integer | boolean | date), optional `required`, `enum`, `min`, `max`, `minLength`, `maxLength`, `pattern`. Only these columns are compared and written; other columns in the sheet are never touched.

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

Up to 1,000 objects and 1 MB per run. Any type error or unknown field rejects the whole run: zero cells are written.

## `operationId` (type: `string`):

Required for `apply`. Sending the same ID again (even 10 times, even concurrently) never writes twice; later runs return `duplicate`.

## `approvedPlanHash` (type: `string`):

The `planHash` from a `plan` run. `apply` writes only if the current diff is exactly that plan; if the sheet or input changed, nothing is written.

## `skipApproval` (type: `boolean`):

Apply the current diff in one run without a prior `plan`. Validation, idempotency and read-back verification still apply.

## `createHeaderIfEmpty` (type: `boolean`):

If the tab is empty, write the column names from the schema as row 1.

## Actor input object example

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1EZLnhDCfmhGUnlubmGE-WIfFie8KrJnJkKE2w9f-4AQ/edit",
  "sheetName": "Demo",
  "mode": "plan",
  "keyColumns": [
    "sku"
  ],
  "columns": [
    {
      "name": "sku",
      "type": "string",
      "pattern": "[A-Z]{3}-\\d{3}"
    },
    {
      "name": "qty",
      "type": "integer",
      "min": 0
    },
    {
      "name": "price",
      "type": "number",
      "min": 0
    },
    {
      "name": "active",
      "type": "boolean"
    },
    {
      "name": "updated",
      "type": "date"
    }
  ],
  "rows": [
    {
      "sku": "ABC-001",
      "qty": 5,
      "price": 9.5,
      "active": true,
      "updated": "2026-10-21"
    },
    {
      "sku": "ABC-002",
      "qty": 0,
      "price": 12,
      "active": false,
      "updated": "2026-10-20"
    }
  ],
  "skipApproval": false,
  "createHeaderIfEmpty": true
}
```

# Actor output Schema

## `result` (type: `string`):

No description

## `changes` (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 = {
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1EZLnhDCfmhGUnlubmGE-WIfFie8KrJnJkKE2w9f-4AQ/edit",
    "sheetName": "Demo",
    "keyColumns": [
        "sku"
    ],
    "columns": [
        {
            "name": "sku",
            "type": "string",
            "pattern": "[A-Z]{3}-\\d{3}"
        },
        {
            "name": "qty",
            "type": "integer",
            "min": 0
        },
        {
            "name": "price",
            "type": "number",
            "min": 0
        },
        {
            "name": "active",
            "type": "boolean"
        },
        {
            "name": "updated",
            "type": "date"
        }
    ],
    "rows": [
        {
            "sku": "ABC-001",
            "qty": 5,
            "price": 9.5,
            "active": true,
            "updated": "2026-10-21"
        },
        {
            "sku": "ABC-002",
            "qty": 0,
            "price": 12,
            "active": false,
            "updated": "2026-10-20"
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("kumo-data-tools/google-sheets-safe-upsert").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/1EZLnhDCfmhGUnlubmGE-WIfFie8KrJnJkKE2w9f-4AQ/edit",
    "sheetName": "Demo",
    "keyColumns": ["sku"],
    "columns": [
        {
            "name": "sku",
            "type": "string",
            "pattern": "[A-Z]{3}-\\d{3}",
        },
        {
            "name": "qty",
            "type": "integer",
            "min": 0,
        },
        {
            "name": "price",
            "type": "number",
            "min": 0,
        },
        {
            "name": "active",
            "type": "boolean",
        },
        {
            "name": "updated",
            "type": "date",
        },
    ],
    "rows": [
        {
            "sku": "ABC-001",
            "qty": 5,
            "price": 9.5,
            "active": True,
            "updated": "2026-10-21",
        },
        {
            "sku": "ABC-002",
            "qty": 0,
            "price": 12,
            "active": False,
            "updated": "2026-10-20",
        },
    ],
}

# Run the Actor and wait for it to finish
run = client.actor("kumo-data-tools/google-sheets-safe-upsert").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": "https://docs.google.com/spreadsheets/d/1EZLnhDCfmhGUnlubmGE-WIfFie8KrJnJkKE2w9f-4AQ/edit",
  "sheetName": "Demo",
  "keyColumns": [
    "sku"
  ],
  "columns": [
    {
      "name": "sku",
      "type": "string",
      "pattern": "[A-Z]{3}-\\\\d{3}"
    },
    {
      "name": "qty",
      "type": "integer",
      "min": 0
    },
    {
      "name": "price",
      "type": "number",
      "min": 0
    },
    {
      "name": "active",
      "type": "boolean"
    },
    {
      "name": "updated",
      "type": "date"
    }
  ],
  "rows": [
    {
      "sku": "ABC-001",
      "qty": 5,
      "price": 9.5,
      "active": true,
      "updated": "2026-10-21"
    },
    {
      "sku": "ABC-002",
      "qty": 0,
      "price": 12,
      "active": false,
      "updated": "2026-10-20"
    }
  ]
}' |
apify call kumo-data-tools/google-sheets-safe-upsert --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,kumo-data-tools/google-sheets-safe-upsert"
        }
    }
}
```

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/aIqYhzotBFK7HD1sp/builds/rObw8JcU2L1xOYjVf/openapi.json