# Google Sheets Import & Export (`cypherstone/google-sheets-import-export`) Actor

Read a Google Sheet into a dataset (public sheets need no key) and write datasets or JSON back - append, replace, or upsert - using a service account, with no interactive OAuth and automatic rate-limit retries.

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

## Pricing

from $0.30 / 1,000 rows

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?

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

**Google Sheets Import & Export** moves tabular data between **Google Sheets** and your Apify datasets or raw JSON — **read a sheet into structured data**, or **write scraped data back into a spreadsheet** with append, replace, and upsert. Reading a link-shared sheet needs **no login and no API key**. Writing uses a **Google service account**, so a scheduled run never stalls waiting for someone to click an OAuth consent screen.

### Why this Google Sheets actor

Most Sheets integrations authenticate with interactive OAuth: the first time a run needs to write, it waits for a human to open a link and approve access. On a schedule, or inside an automation, that silently hangs or fails. This actor authenticates **non-interactively** with a service-account key, **retries Google's rate limits** (HTTP 429 and 5xx) with exponential backoff, and returns a report of **exactly how many rows and columns it wrote** — so an import that half-succeeds tells you, instead of failing quietly.

### What can the Google Sheets actor do?

- **Import a Google Sheet to JSON, CSV, or Excel** — pull a whole tab into an Apify dataset you can download or feed to another actor.
- **Export scraped data to Google Sheets** — send any dataset (or inline JSON) straight into a spreadsheet for reporting or manual review.
- **Append rows to a Google Sheet** — add new records to the bottom of a tab without touching what is already there.
- **Replace a sheet with fresh data** — clear a tab and write a new header plus rows in one run.
- **Upsert by a key column** — write only the rows whose key is not already in the sheet, so re-runs don't create duplicates.
- **Read a public sheet with no credentials** — if a sheet is shared "Anyone with the link → Viewer," just paste the URL.
- **Flatten nested JSON into columns** — nested objects and arrays become columns like `address/city`, so scraped data lands in a clean grid.

### How to read a Google Sheet (no key needed)

1. Set **Mode** to `read`.
2. Paste the sheet's URL (or its ID) into **Spreadsheet URL or ID**.
3. If the sheet is private, either share it "Anyone with the link → Viewer," or add a service-account key (below).
4. Run it. Each row becomes one dataset item, using the first row as column names.

### How to write data into a Google Sheet

Writing changes your spreadsheet, so it needs a Google **service account** — a robot Google account you control:

1. In the [Google Cloud Console](https://console.cloud.google.com/), create a **service account** and a **JSON key**, and enable the **Google Sheets API**.
2. Open the JSON key and copy the `client_email` (it looks like `name@project.iam.gserviceaccount.com`).
3. In Google Sheets, **Share** your spreadsheet with that email as an **Editor**.
4. Paste the entire key JSON into the **Service-account key JSON** field.
5. Choose a mode — `append`, `replace`, or `upsert` — set the **Tab name**, and supply the rows (inline **data**, a **source dataset ID**, or this run's default dataset).

The service-account key is stored as a secret input.

### Modes

| Mode | What it does | Needs a key |
|------|--------------|-------------|
| `read` | Sheet → dataset (JSON/CSV/Excel) | Only for private sheets |
| `append` | Add rows to the bottom of a tab | Yes |
| `replace` | Clear the tab, write header + rows | Yes |
| `upsert` | Append only rows whose key column is new | Yes |

### Input example

Read a public sheet:

```json
{
  "mode": "read",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "headerRow": true
}
```

Append a scraper's results to a spreadsheet:

```json
{
  "mode": "append",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/your-sheet-id/edit",
  "tab": "Leads",
  "datasetId": "your-dataset-id",
  "serviceAccount": { "type": "service_account", "client_email": "...", "private_key": "..." }
}
```

### Output

In `read` mode, every row is pushed to the dataset as a JSON object keyed by the header, ready to export as JSON, CSV, or Excel, or to pass to another actor. In write modes, the run's log and result report the mode, the number of rows written, the column count, and — for `upsert` — how many existing rows were skipped.

### Use cases

- **Push scraped leads, products, or listings into a shared spreadsheet** your team can read and edit.
- **Build a live report** that appends fresh rows on a schedule and never duplicates them (upsert).
- **Turn a spreadsheet into an API** — read a sheet your team maintains and feed it into other actors or your own code.
- **Back up or migrate** rows between datasets and sheets.
- **Chain after any scraper** — run a scraper, then this actor with "use this run's default dataset" to land the data in Sheets automatically.

### Notes and limits

- Cells over Google's 50,000-character limit are truncated (with a trailing `…`) and the run continues.
- Large writes are automatically split into chunks to stay within Google's request limits.
- Reading a private sheet without a key returns a clear message telling you how to share it or add a key — it never returns empty rows silently.
- The actor reads and writes only the spreadsheets you point it at and share with your service account. It collects no personal data on its own.

# Actor input Schema

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

read = sheet → dataset. append/replace/upsert = data → sheet (need a service-account key).

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

The Google Sheets link (or just its ID). To read without a key, the sheet must be shared 'Anyone with the link → Viewer'.

## `headerRow` (type: `boolean`):

Use the first row as column names when reading. Turn off to get col1, col2, …

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

Optional A1 range, e.g. 'Sheet1!A1:D100'. Only used when reading a private sheet with a key.

## `tab` (type: `string`):

The tab to write to, e.g. Sheet1.

## `data` (type: `array`):

Rows to write, as an array of JSON objects. Leave empty to use a dataset instead.

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

Write the rows from this Apify dataset. Leave empty to use inline data or the run's default dataset.

## `useDefaultDataset` (type: `boolean`):

When chained after a scraper, write that run's default dataset.

## `keyColumn` (type: `string`):

Column name used to skip rows already present in the sheet.

## `includeHeader` (type: `boolean`):

Write column names as the first row when replacing a sheet.

## `flattenDelimiter` (type: `string`):

Nested JSON is flattened to columns like address/city using this separator.

## `serviceAccount` (type: `object`):

Paste a Google service-account key (the whole JSON). Share the sheet with its client\_email as Editor. Not needed to read a link-shared sheet.

## Actor input object example

```json
{
  "mode": "read",
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "headerRow": true,
  "tab": "Sheet1",
  "useDefaultDataset": false,
  "includeHeader": true,
  "flattenDelimiter": "/"
}
```

# Actor output Schema

## `results` (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/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
};

// Run the Actor and wait for it to finish
const run = await client.actor("cypherstone/google-sheets-import-export").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/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0" }

# Run the Actor and wait for it to finish
run = client.actor("cypherstone/google-sheets-import-export").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/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0"
}' |
apify call cypherstone/google-sheets-import-export --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,cypherstone/google-sheets-import-export"
        }
    }
}
```

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/ySROhj62aJeDps7bh/builds/jP6UTweHGwtE0VtPc/openapi.json