# Reliable Google Sheets Bridge (`groundedvisionmedia/reliable-sheets-bridge`) Actor

Two-way Apify dataset ↔ Google Sheets transfers with your service account. Append, replace, or strict composite-key upsert with Limited Permissions, deterministic nested JSON, safe row limits, and credential-safe errors.

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

## Pricing

from $0.75 / 1,000 row transferreds

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

## Reliable Google Sheets Bridge

Move rows safely in either direction between an Apify dataset and Google Sheets.

- **Apify dataset → Google Sheets:** append, replace, or upsert.
- **Google Sheets → Apify dataset:** one dataset item per non-empty row.
- **Limited Permissions:** the source dataset is chosen with Apify's resource picker and requests `READ` only.
- **Composite upsert keys:** match on one column or a combination such as `account_id + order_id`.
- **Failure-safe validation:** missing keys, duplicate keys, duplicate headers, oversized cells, malformed IDs, and row-limit overflows are rejected with actionable errors.
- **No interactive OAuth:** authenticate with your own Google service account.

### Why this Actor

Service-account authentication is now available in several Google Sheets Actors. The specific focus here is predictable synchronization under least privilege:

1. The Actor cannot browse your Apify account. Under Limited Permissions it can read only the dataset you select.
2. Upsert supports multiple key columns and rejects missing or ambiguous keys before changing sheet data.
3. Nested objects and arrays are stored as deterministic compact JSON instead of becoming `[object Object]`.
4. Append and upsert expand headers when new fields appear.

#### Upsert matching rules

- Header names and configured `keyColumns` are normalized with Unicode NFKC and surrounding whitespace is removed.
- Header matching remains case-sensitive: `Name` and `name` are different columns.
- The same normalization is applied to source row dictionaries and destination sheet headers before values are looked up.
- String key values are Unicode-normalized and trimmed. Numeric `101`, numeric `101.0`, and string `"101"` match; string `"00101"` remains distinct.
- Every key part must be non-blank. Duplicate normalized keys in either source or destination stop the run before any sheet mutation.
- Matching rows are replaced in place across the final header width. Nonmatching source rows append; unrelated destination rows are untouched.

5. Row limits fail safely by default instead of silently dropping data.
6. Google quota and temporary-server errors receive automatic SDK retries.

### One-time Google setup

1. Create or choose a Google Cloud project.
2. Enable the **Google Sheets API**.
3. Create a service account and download a JSON key.
4. Share the spreadsheet with the key's `client_email`.
   - Use **Viewer** for Sheet → dataset.
   - Use **Editor** for dataset → Sheet.
5. Paste the complete JSON key into **Google service-account JSON**. The input field is marked secret and is stored encrypted by Apify.

The Actor never logs the credential or raw Google error bodies. Revoke access at any time by unsharing the spreadsheet or disabling the service-account key.

### Operations

#### Dataset → Google Sheets

Select the source with **Source Apify dataset**. The picker grants the run read-only access to that one dataset.

##### Append

- Creates the destination tab when `autoCreateSheet` is enabled.
- Creates a header for an empty tab.
- Adds new columns to the end of an existing header.
- Aligns incoming values to the final header order.

```json
{
  "operation": "dataset_to_sheet",
  "datasetId": "SELECT_WITH_RESOURCE_PICKER",
  "spreadsheetIdOrUrl": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit",
  "sheetName": "Leads",
  "serviceAccountJson": "{ ... }",
  "writeMode": "append"
}
```

##### Replace

Validates and serializes the complete incoming transfer before clearing the tab. An empty source dataset intentionally clears the destination tab.

```json
{
  "operation": "dataset_to_sheet",
  "datasetId": "SELECT_WITH_RESOURCE_PICKER",
  "spreadsheetIdOrUrl": "SPREADSHEET_ID",
  "sheetName": "Current inventory",
  "serviceAccountJson": "{ ... }",
  "writeMode": "replace"
}
```

##### Upsert with a composite key

Matching rows are replaced in place; new keys are appended. Every key component must be present and non-empty. Duplicate incoming keys or duplicate destination keys stop the run before sheet writes.

```json
{
  "operation": "dataset_to_sheet",
  "datasetId": "SELECT_WITH_RESOURCE_PICKER",
  "spreadsheetIdOrUrl": "SPREADSHEET_ID",
  "sheetName": "Orders",
  "serviceAccountJson": "{ ... }",
  "writeMode": "upsert",
  "keyColumns": ["account_id", "order_id"]
}
```

#### Google Sheets → Apify dataset

The first configured header row becomes the object keys. Empty rows are skipped. If `readRange` is supplied, `headerRow` is relative to the returned range; otherwise it is the absolute row within the tab.

```json
{
  "operation": "sheet_to_dataset",
  "spreadsheetIdOrUrl": "SPREADSHEET_ID",
  "sheetName": "Customers",
  "serviceAccountJson": "{ ... }",
  "headerRow": 1,
  "maxRows": 50000
}
```

### Data handling

| Input value | Google Sheets value |
| --- | --- |
| string, integer, finite number, boolean | Native cell value |
| `null` or missing field | Empty cell |
| object or array | Compact JSON string with stable key order |
| NaN or Infinity | Rejected |
| value over 50,000 characters | Rejected |

`RAW` is the recommended value mode because text beginning with `=` is not executed as a formula. Choose `USER_ENTERED` only when Google formula/date parsing is intentional.

### Limits and batching

- `maxRows` defaults to 50,000 and can be set as high as 250,000.
- `rowLimitAction: "fail"` is the default. Set `"truncate"` only when deliberate truncation is acceptable.
- `batchSize` defaults to 2,000 and supports 1–10,000 rows.
- Upsert update requests are capped at 500 row ranges per Google batch request.
- Google Sheets API quotas and spreadsheet cell limits still apply.

### Output

Every run writes `SUMMARY` to the default key-value store.

- Sheet → dataset: transferred rows are in the default dataset.
- Dataset → Sheet: the default dataset contains a transfer receipt.
- Health check: the default dataset contains one credential-free status item.

Example summary:

```json
{
  "status": "ok",
  "operation": "dataset_to_sheet",
  "writeMode": "upsert",
  "sheetName": "Orders",
  "rowsRead": 1250,
  "rowsTransferred": 1250,
  "rowsUpdated": 900,
  "rowsAppended": 350,
  "columns": 12,
  "columnsAdded": ["last_seen"],
  "rowLimitTruncated": false
}
```

Errors use stable codes such as `CREDENTIAL_INVALID`, `SPREADSHEET_INACCESSIBLE`, `DATASET_INACCESSIBLE`, `UPSERT_KEY_VALUE_MISSING`, and `ROW_LIMIT_EXCEEDED`.

### Health-check mode

The prefilled default input is:

```json
{"operation": "health_check"}
```

It uses no Google credential or private resource, succeeds quickly, and writes a non-empty default dataset. This is designed for Apify Store's automatic daily test.

### Pricing behavior

The v0.3.1 source contains verified Apify Python SDK 4.0.2 charging calls, but charging is disabled unless the Actor environment variable `RSB_ENABLE_CHARGING=true` is set. Do not enable it until the matching Store events are configured. See `PRICING_AND_KILL_RULES.md` and `DEPLOY.md`.

### Important replace limitation

Google Sheets does not provide a transaction spanning clear and multiple value writes. The Actor validates all local data and spreadsheet access before clearing, but a network or Google outage during a large replace can still leave a partially written tab. Use a copied spreadsheet or scheduled backup for business-critical replacements.

### Support information

When reporting a problem, provide the Apify run ID, operation, write mode, approximate row count, and error code. Never send a service-account private key in an issue or message.

# Changelog

This Actor's version history is a separate document: https://apify.com/groundedvisionmedia/reliable-sheets-bridge/changelog.md

# Actor input Schema

## `operation` (type: `string`):

Health check uses no Google credential and is the safe Store-test default.

## `serviceAccountJson` (type: `string`):

Required for either transfer direction. Paste the complete JSON key. Apify stores this field encrypted.

## `spreadsheetIdOrUrl` (type: `string`):

Full docs.google.com/spreadsheets URL or its spreadsheet ID.

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

Case-sensitive source or destination tab.

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

Required for dataset → Sheet. Selecting it grants this Limited Permissions Actor read-only access to that dataset.

## `writeMode` (type: `string`):

Used only for dataset → Sheet.

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

One or more case-sensitive columns forming a unique composite key. Names are Unicode-normalized and trimmed. Blank or duplicate keys fail before data writes.

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

Used only for Sheet → dataset. Leave empty to read the selected tab.

## `headerRow` (type: `integer`):

1-based row containing column names for Sheet → dataset.

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

Safety cap per run. By default the Actor fails instead of silently truncating.

## `rowLimitAction` (type: `string`):

Fail before transferring, or deliberately transfer only the first maxRows data rows.

## `batchSize` (type: `integer`):

Rows per Google or Apify write batch.

## `autoCreateSheet` (type: `boolean`):

Used only for dataset → Sheet.

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

RAW prevents formulas from being executed. USER\_ENTERED lets Google parse formulas and dates.

## Actor input object example

```json
{
  "operation": "health_check",
  "sheetName": "Sheet1",
  "writeMode": "append",
  "keyColumns": [],
  "headerRow": 1,
  "maxRows": 50000,
  "rowLimitAction": "fail",
  "batchSize": 2000,
  "autoCreateSheet": true,
  "valueInputOption": "RAW"
}
```

# Actor output Schema

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

Counts, mode, row-limit status, and billing event counts. Error summaries use the same record key.

## `dataset` (type: `string`):

Sheet rows for imports; a transfer receipt for dataset-to-sheet runs; a status item for health checks.

# 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 = {
    "operation": "health_check"
};

// Run the Actor and wait for it to finish
const run = await client.actor("groundedvisionmedia/reliable-sheets-bridge").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 = { "operation": "health_check" }

# Run the Actor and wait for it to finish
run = client.actor("groundedvisionmedia/reliable-sheets-bridge").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 '{
  "operation": "health_check"
}' |
apify call groundedvisionmedia/reliable-sheets-bridge --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,groundedvisionmedia/reliable-sheets-bridge"
        }
    }
}
```

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/IexbwgABzhnFnq0Nn/builds/EUDNKPHXjZehRSXBk/openapi.json