# Google Sheets Sync – Reliable Import & Export (`chris_elder/google-sheets-sync`) Actor

Import a Google Sheet into an Apify dataset or export a dataset/JSON into a Sheet tab. Public sheets need no auth; private sheets use your service-account JSON. Chunked writes with retries. $0.50 per 1,000 rows; failed writes are free.

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

## Pricing

from $0.30 / 1,000 row synceds

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 – Reliable Import & Export

**Google Sheets Sync** moves data between Apify and Google Sheets without the flaky timeouts that plague the popular free Sheets Actors. **Import** a public or private sheet into the default dataset; **export** an Apify dataset or pasted JSON into a sheet tab (append or replace), with nested objects flattened, batched writes and retries on 429/5xx.

Store page: [apify.com/chris_elder/google-sheets-sync](https://apify.com/chris_elder/google-sheets-sync)

✅ Pay per result: **$0.50 per 1,000 rows synced** · 🆓 Failed writes / reads are never charged · 🔑 You bring your own Google service account (no shared Google project)

[▶ Try it with one click](#how-to-use-it) · [📄 Real example output](#output-example-from-a-real-public-run) · [💲 Pricing](#how-much-does-it-cost-to-sync-google-sheets) · [🔗 Use after any Actor run](#use-as-an-integration-after-any-actor-run) · [🔌 API & integrations](#integrations-api-ai-agents-make-zapier) · [❓ FAQ](#faq) · [🛟 Support](#support-reliability-and-feature-requests)

### What does Google Sheets Sync do?

- 📥 **Import** a Google Sheet tab into an Apify dataset (JSON/CSV/Excel download from Storage)
- 📤 **Export** an Apify dataset or pasted JSON rows into a Sheet tab (**append** or **replace**)
- 🌐 **Public sheets need no auth** – uses Google's CSV export URL (one-click prefill)
- 🔐 **Private sheets** use **your** service-account JSON; share the sheet with that email as Editor
- 📦 **Flatten nested JSON** into dotted columns (`meta.city`) on export
- 🔁 **Chunked writes + retries** with backoff on HTTP 429/5xx so large jobs do not time out
- 🆘 **Actionable errors** for the usual failures: sheet not shared, wrong ID, Sheets API not enabled, bad JSON
- 🆓 **Failed rows free** – only successfully synced rows are charged

### Use cases

- **Scraper users** – push a run's dataset into Google Sheets for ops / clients without writing Apps Script
- **Agents & n8n/Make/Zapier** – "write this dataset to Sheet X" or "read Sheet Y into Apify" as one step
- **Analysts** – pull a public reporting sheet into Apify for further Actors (e.g. [Dataset Transformer](https://apify.com/chris_elder/dataset-transformer))
- **Teams on private Sheets** – keep credentials in *your* Google Cloud project; nothing is shared with GPT Shop Ltd

### How to use it

1. Click **Try for free** – the prefill **imports** Google's public sample "Class Data" sheet (no service account). Expect about 30 student rows for roughly **$0.015** (start + rows at Free tier).
2. For a **private** sheet or any **export**, create a Google Cloud service account (2 minutes – see below), paste the JSON into `serviceAccountJson`, and Share the sheet with the `client_email` as **Editor**.
3. Download the dataset from the run's Storage tab, or open the Sheet to see exported rows.

#### Service account setup (export & private import) – about 2 minutes

1. Open [Google Cloud Console](https://console.cloud.google.com/) → create or pick a project.
2. **APIs & Services → Library → "Google Sheets API" → Enable**.
3. **IAM & Admin → Service accounts → Create** → open the account → **Keys → Add key → JSON** → download the file.
4. Open your Google Sheet → **Share** → paste the `client_email` from the JSON → role **Editor** → uncheck "Notify people" → Share.
5. Paste the **entire JSON file** into the Actor input field `serviceAccountJson` (secret; never logged).

You do **not** need to give GPT Shop Ltd or Apify access to your Google account. The key stays in your Actor run input.

#### Input example (public import – prefill)

```json
{
  "mode": "import",
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Class Data",
  "maxRows": 30
}
```

#### Input example (export)

```json
{
  "mode": "export",
  "spreadsheetId": "YOUR_SHEET_ID",
  "sheetName": "Apify Export",
  "writeMode": "replace",
  "flattenNested": true,
  "datasetId": "YOUR_DATASET_ID",
  "serviceAccountJson": "{ \"type\": \"service_account\", \"client_email\": \"...\", \"private_key\": \"...\" }",
  "maxRows": 5000
}
```

Other options: `items` (paste JSON instead of `datasetId`), `dryRun` (free export preview, nothing written), `gid` (public import), `range` (A1 range for private import).

#### Output example (from a real public run)

Public run data (no login needed): [dataset JSON](https://api.apify.com/v2/datasets/odHFoqKb9KgGR5r3Z/items?format=json\&clean=1) · [dataset CSV](https://api.apify.com/v2/datasets/odHFoqKb9KgGR5r3Z/items?format=csv\&clean=1) · [RUN_SUMMARY](https://api.apify.com/v2/key-value-stores/2TcyX7QdwqOvV3QZ0/records/RUN_SUMMARY) · [console run](https://console.apify.com/view/runs/blKBe30pElCGrZ3Cy)

First dataset item:

```json
{
  "Student Name": "Alexandra",
  "Gender": "Female",
  "Class Level": "4. Senior",
  "Home State": "CA",
  "Major": "English",
  "Extracurricular Activity": "Drama Club"
}
```

`RUN_SUMMARY` from that run (30 rows charged; platform usage ≈ $0.000055):

```json
{
  "mode": "import",
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Class Data",
  "auth": "public_csv",
  "rowsRead": 30,
  "rowsSynced": 30,
  "skippedBudget": 0,
  "budgetHit": false,
  "clientEmail": null
}
```

| Field | Meaning |
|---|---|
| `mode` | `import` or `export` |
| `rowsSynced` | Rows charged (successfully written to dataset or Sheet) |
| `auth` | `public_csv` or `service_account` (import) |
| `sheetTitle` / `headers` | Export: tab written and column headers used |
| `budgetHit` | Stopped early because of Max cost per run |

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

| Event | Free / Bronze | Silver | Gold+ |
|---|---|---|---|
| Actor start (× memory GB, min 1) | $0.0005 | $0.0005 | $0.0005 |
| Row synced | $0.0005 | $0.0004 | $0.0003 |

**Failed, skipped and invalid rows are free.** Only rows successfully imported into the dataset or written to the Sheet are charged.

| Job | Approx. cost |
|---|---|
| Prefill import (~30 rows, 512 MB) | 30 × $0.0005 + $0.0005 start ≈ **$0.0155** |
| 1,000 rows import or export | **$0.50** + start |
| 10,000 rows (Gold) | 10,000 × $0.0003 = **$3.00** + start |

Use **Max cost per run** to cap spend; the Actor stops syncing when the cap is reached (`budgetHit: true` in `RUN_SUMMARY`).

### Use as an integration after any Actor run

Send every new scraper result to a Sheet automatically, with no code:

1. Open the Actor (or saved task) whose results you want in Sheets → **Integrations** tab → **Add integration** → search **Google Sheets Sync**.
2. **Start when:** *Run succeeded*.
3. **Input:** `mode` = `export`, `datasetId` = `{{resource.defaultDatasetId}}` (Apify swaps in the triggering run's dataset and grants this Actor read access to just that dataset), plus your `spreadsheetId`, `sheetName`, `writeMode` (`append` keeps history, `replace` keeps only the latest run) and your `serviceAccountJson`.
4. Click **Test** with a past run. Turn on `dryRun` first to preview the column headers and 3 sample rows for free (nothing is written to Google, no service account needed). Then turn `dryRun` off.

```json
{
  "mode": "export",
  "spreadsheetId": "YOUR_SHEET_ID",
  "sheetName": "Scraper results",
  "writeMode": "append",
  "datasetId": "{{resource.defaultDatasetId}}",
  "serviceAccountJson": "{ ...your key JSON... }"
}
```

The dataset is read in pages of 1,000 items (up to `maxRows`), and `RUN_SUMMARY` records which run triggered the export (`triggeredBy`). The Actor keeps Apify's **limited permissions**: it can read only the dataset you pass in `datasetId`, nothing else in your account.

### Integrations (API, AI agents, Make, Zapier)

```bash
curl -X POST "https://api.apify.com/v2/acts/chris_elder~google-sheets-sync/run-sync-get-dataset-items?token=YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"mode":"import","spreadsheetId":"1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms","sheetName":"Class Data","maxRows":30}'
```

Python (`pip install apify-client`):

```python
from apify_client import ApifyClient
client = ApifyClient("YOUR_TOKEN")
run = client.actor("chris_elder/google-sheets-sync").call(run_input={
    "mode": "export",
    "spreadsheetId": "YOUR_SHEET_ID",
    "sheetName": "Export",
    "writeMode": "append",
    "datasetId": "YOUR_DATASET_ID",
    "serviceAccountJson": open("sa.json").read(),
})
print(client.dataset(run["defaultDatasetId"]).list_items().items)
```

- MCP: `https://mcp.apify.com/?tools=chris_elder/google-sheets-sync`
- Make / Zapier / n8n: module **Run Actor** → `chris_elder/google-sheets-sync`
- Chain after scrapers: add it on the scraper's **Integrations** tab ([see above](#use-as-an-integration-after-any-actor-run)), or pass the previous run's `defaultDatasetId` as `datasetId`
- Pair with [Dataset Transformer](https://apify.com/chris_elder/dataset-transformer) to clean/dedupe before export

### Limits and good to know

- Public import requires the sheet to be viewable by anyone with the link (or fully public). Otherwise use a service account.
- Export and private import need the Google Sheets API enabled on **your** Cloud project and the sheet shared with the service account email.
- Very large sheets: raise memory if needed; writes are chunked (500 rows) with retries. Soft cap via `maxRows` (default 50,000).
- Nested objects are flattened on export; arrays become JSON strings in a single cell.
- This Actor does not use any Google Cloud project owned by GPT Shop Ltd / Chris Elder. Your key, your project, your quotas.

### Is it legal?

You must only sync spreadsheets you own or are allowed to access. The Actor calls Google's documented Sheets API / public CSV export using credentials **you** supply. Follow [Google APIs Terms](https://developers.google.com/terms) and Apify's [legal / ToS guidance](https://blog.apify.com/is-web-scraping-legal/). Do not scrape or export personal data unlawfully.

### Need the data done for you?

If you'd rather not set up runs yourself, the UK company behind these tools (GPT Shop Ltd) offers a fixed-price done-for-you scrape. Tell us the website and the fields you need, and you get a clean CSV or Google Sheet within 48 hours of the confirmed brief, up to 5,000 rows, for £149 or $179. If we can't get the data, you're refunded in full. [See the done-for-you scraper service](https://tools.gptshopltd.com/done-for-you-scraper/?utm_source=apify\&utm_medium=readme\&utm_campaign=google-sheets-sync)

### FAQ

**Do I need a Google API key from you?**\
No. Public import needs nothing. Private import and export use a service account **you** create. We never ask for your Google password.

**Why did I get "Permission denied"?**\
Share the Sheet with the exact `client_email` from your JSON as Editor, and enable the Google Sheets API on that Cloud project. The error message repeats both steps.

**Why did public import return an HTML page error?**\
The sheet is not link-shared for viewing. Change sharing to "Anyone with the link → Viewer", or switch to a service account.

**Can I append without wiping the sheet?**\
Yes – `writeMode: "append"` (default). Use `"replace"` to clear the tab and rewrite header + rows.

**Are failed writes charged?**\
No. Only successfully synced rows trigger the `row-synced` event.

**How is this different from lukaskrivka/google-sheets?**\
That Actor has hundreds of monthly users but a high public failure rate. This one focuses on chunked writes, retries, public-CSV import without OAuth, and errors that tell you exactly what to fix. Pricing is pay-per-event so idle failures cost you nothing.

### Support, reliability and feature requests

- Failed items are free.
- Every release is tested on real public inputs; the one-click example works.
- Issues answered within 2 business days via the Actor **Issues** tab.
- Breaking input or price changes are announced in this README first.

Built by Chris Elder with AI-assisted development; every release is reviewed and tested. Tested on: Google's public Class Data sample spreadsheet (import), mocked Sheets API (export unit tests).

### More tools by Chris Elder

- [Dataset Transformer](https://apify.com/chris_elder/dataset-transformer) – filter, dedupe, flatten, JSON↔CSV before you export
- [Bulk File Downloader](https://apify.com/chris_elder/bulk-file-downloader)
- [HTTP Request Sender](https://apify.com/chris_elder/http-request-sender)
- All tools: [apify.com/chris_elder](https://apify.com/chris_elder)

# Actor input Schema

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

import = Sheet → Apify dataset. export = dataset/JSON → Sheet. Prefill uses import on a public sample sheet (no auth, ~$0.015). Using this Actor as an integration after another Actor's run? Choose export – the triggering run's dataset is used automatically.

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

Google Sheet ID (the long string in /d/SHEET_ID/edit) or the full spreadsheet URL.

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

Tab name (exact). Leave empty to use the first tab (import) or create/use Sheet1 behaviour via API (export).

## `gid` (type: `string`):

Optional numeric gid from the Sheet URL (#gid=…). Used only for public CSV import when sheetName is unreliable.

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

Paste the full service-account key JSON. Required for export and for private sheets. Share the Sheet with the client_email as Editor. Never logged. Secret field.

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

Export mode: the Apify dataset to write into the Sheet. Use this OR items. Using this Actor as an integration (Integrations tab of another Actor/task)? Set this to {{resource.defaultDatasetId}} – Apify fills in the triggering run's dataset and grants this Actor read access to it.

## `items` (type: `array`):

Export mode: paste rows directly instead of datasetId.

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

append = add rows under existing data (keeps header if present). replace = clear the tab then write header + rows.

## `flattenNested` (type: `boolean`):

Turn { meta: { city } } into a meta.city column. Arrays become JSON strings.

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

Export mode: resolve the dataset (or integration payload), flatten it and output the column headers + 3 sample rows WITHOUT writing to Google Sheets. No service account needed, no rows charged. Handy for testing an integration.

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

Cap on rows read or written (protects cost). Charged only for successfully synced rows.

## `range` (type: `string`):

Optional A1 range for service-account import, e.g. 'Class Data'!A1:F100. Ignored for public CSV import.

## Actor input object example

```json
{
  "mode": "import",
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Class Data",
  "writeMode": "append",
  "flattenNested": true,
  "dryRun": false,
  "maxRows": 30
}
```

# Actor output Schema

## `rows` (type: `string`):

No description

## `summary` (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 = {
    "mode": "import",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Class Data",
    "maxRows": 30
};

// Run the Actor and wait for it to finish
const run = await client.actor("chris_elder/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 = {
    "mode": "import",
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
    "sheetName": "Class Data",
    "maxRows": 30,
}

# Run the Actor and wait for it to finish
run = client.actor("chris_elder/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 '{
  "mode": "import",
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Class Data",
  "maxRows": 30
}' |
apify call chris_elder/google-sheets-sync --silent --output-dataset

```

## MCP server setup

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