# Google Sheets Reader & Writer (`accurate_pouch/google-sheets-rw`) Actor

Read any Google Sheet to JSON or write rows to a sheet — via Service Account auth. No OAuth blocking, no failed logins. Supports read, append, replace, modify, and backup modes. Includes safe replace, dry run, auto-create tab, and rate limit retry.

- **URL**: https://apify.com/accurate_pouch/google-sheets-rw.md
- **Developed by:** [Manchitt Sanan](https://apify.com/accurate_pouch) (community)
- **Categories:** Developer tools, Other
- **Features:** Limited permissions
- **Stats:** 3 total users, 0 monthly users, 100.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

$4.00 / 1,000 operations

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

## Google Sheets Reader & Writer

Read any Google Sheet to structured JSON, or append rows from JSON to a sheet. No OAuth. No "This App Is Blocked" errors. Uses Google Service Account authentication — set up once, works forever.

***

### Why this exists

The most popular Google Sheets actor on Apify fails ~52% of the time because Google blocks its unverified OAuth login screen. The developer has confirmed this is unsolvable with OAuth.

This actor uses **Service Account authentication** — the approach Google recommends for server-to-server access. No consent screens, no token expiry, no blocked apps.

***

### Quick start

#### Reading a public sheet (no auth needed)

Set your sheet to **File → Share → Anyone with the link → Viewer**, then:

```json
{
    "spreadsheetId": "YOUR_SHEET_ID",
    "mode": "read"
}
```

#### Reading a private sheet

```json
{
    "spreadsheetId": "YOUR_SHEET_ID",
    "mode": "read",
    "serviceAccountKey": "{ ... paste your service account JSON here ... }"
}
```

#### Appending rows

```json
{
    "spreadsheetId": "YOUR_SHEET_ID",
    "mode": "append",
    "serviceAccountKey": "{ ... paste your service account JSON here ... }",
    "rows": [
        { "name": "John Smith", "email": "john@example.com", "status": "active" },
        { "name": "Jane Doe", "email": "jane@example.com", "status": "pending" }
    ]
}
```

***

### How to find your Spreadsheet ID

From your Google Sheet URL:

```
https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit
                                       ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
                                       This is your spreadsheetId
```

***

### Service Account setup (one-time, ~5 minutes)

Only needed for private sheets and append operations.

**Step 1 — Create a Google Cloud project**

1. Go to [console.cloud.google.com](https://console.cloud.google.com)
2. Click **New Project**, give it any name, click **Create**

**Step 2 — Enable the Sheets API**

1. In your project, go to **APIs & Services → Library**
2. Search for **Google Sheets API**, click it, click **Enable**

**Step 3 — Create a Service Account**

1. Go to **APIs & Services → Credentials**
2. Click **Create Credentials → Service Account**
3. Give it any name (e.g. `apify-sheets`), click **Create and Continue**
4. Skip the optional steps, click **Done**

**Step 4 — Download the key**

1. Click the service account you just created
2. Go to the **Keys** tab
3. Click **Add Key → Create new key → JSON**
4. A `.json` file downloads — open it and copy the entire contents

**Step 5 — Share your sheet with the service account**

1. Open your Google Sheet
2. Click **Share**
3. Paste the service account email (formatted as `{sa-name}@{project-id}.iam.gserviceaccount.com`)
4. Set role to **Editor** (for append) or **Viewer** (for read-only)
5. Click **Send**

**Step 6 — Paste the JSON key into the actor input**

Paste the full contents of the downloaded `.json` file into the `serviceAccountKey` field.

***

### Input

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `spreadsheetId` | string | Yes | The ID from your Google Sheet URL |
| `mode` | string | Yes | `"read"` or `"append"` |
| `range` | string | No | Sheet tab name or A1 range (default: first sheet) |
| `serviceAccountKey` | string | Conditional | Required for private sheets and all appends. Full JSON key contents. |
| `rows` | array | Conditional | Required for append. Array of objects matching your column headers. |

***

### Output

#### Read mode

```json
{
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
    "mode": "read",
    "rowCount": 150,
    "columns": ["name", "email", "status"],
    "data": [
        { "name": "John Smith", "email": "john@example.com", "status": "active" },
        { "name": "Jane Doe", "email": "jane@example.com", "status": "pending" }
    ],
    "status": "success",
    "error": null
}
```

#### Append mode

```json
{
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
    "mode": "append",
    "rowsAppended": 2,
    "updatedRange": "Sheet1!A152:C153",
    "status": "success",
    "error": null
}
```

***

### Pricing

**$0.003 per successful operation** (pay-per-event pricing).

- Failed operations (auth errors, invalid IDs, inaccessible sheets) are **not charged**.
- 1,000 operations = $3.00
- 10,000 operations = $30.00

***

### Limitations

- Append mode requires at least one existing row in the sheet to read column headers from. If the sheet is empty, column headers are taken from the keys of the first row in your input.
- Maximum sheet size is limited by Google Sheets' own 5 million cell limit.
- Service Account must be shared on the specific sheet — sharing at the Drive folder level is also supported.
- This actor does NOT use OAuth. It cannot access sheets you haven't explicitly shared with the service account email.

***

### Related actors in this suite

Other tools by [accurate_pouch](https://apify.com/accurate_pouch) that pair with Sheets for end-to-end workflows:

- **[Email Validator Pro](https://apify.com/accurate_pouch/email-validator)** — SMTP, disposable, catch-all, typo detection, deliverability score, Sheets export. $0.005/email.
- **[Broken Link Checker](https://apify.com/accurate_pouch/broken-link-checker)** — Recursive crawl, sitemap, robots.txt parsing, webhook, Sheets export. $0.005/page.
- **[Lighthouse Auditor](https://apify.com/accurate_pouch/lighthouse-auditor)** — PageSpeed Insights API, Core Web Vitals, deltas, competitor comparison, Sheets export. $0.005/audit.
- **[TheCrawler](https://apify.com/accurate_pouch/the-crawler)** — Web scraper + LLM-powered structured extraction. AGPL-3.0, also on npm (`thecrawler@0.1.1`). $0.005/page.

***

### Run on Apify

[![Run on Apify](https://apify.com/static/run-on-apify.svg)](https://apify.com/accurate_pouch/google-sheets-rw)

No setup needed. Click above to run in the cloud. $0.003 per operation.

# Actor input Schema

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

ID from your Google Sheet URL (between /d/ and /edit). Required unless using 'spreadsheets' for multiple sheets.

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

read: export sheet to JSON. append: add rows. replace: overwrite sheet. modify: transform existing data. loadBackup: restore a backup.

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

Tab name or A1 range. Examples: Sheet1, Sheet2!A1:D100. Default: first sheet.

## `serviceAccountKey` (type: `string`):

Required for private sheets and all write operations. Paste the full contents of your Google Service Account JSON key file. See README for 5-minute setup guide.

## `publicSpreadsheet` (type: `boolean`):

Set true to read a publicly shared sheet without a Service Account key. Read-only. Sheet must be shared as 'Anyone with the link'.

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

Array of objects for append/replace modes. Keys must match column headers. Used when you are not pulling from an Apify dataset.

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

Apify dataset ID to import data from. Use this to chain this actor after a scraper.

## `rawData` (type: `array`):

Array of objects or array of arrays (first row = headers). Max 9MB.

## `limit` (type: `integer`):

Max rows to pull from dataset. Default: 250000.

## `offset` (type: `integer`):

Skip this many rows from the start of the dataset.

## `deduplicateByField` (type: `string`):

Field name to deduplicate by. Rows with duplicate values in this field are skipped. Cannot be used with deduplicateByEquality or transformFunction.

## `deduplicateByEquality` (type: `boolean`):

Skip rows that are identical across all fields. Cannot be used with deduplicateByField or transformFunction.

## `transformFunction` (type: `string`):

Custom JS function: ({ spreadsheetData, datasetData }) => \[...]. Receives existing sheet data and new data, returns final array to write. Cannot be used with deduplication.

## `columnsOrder` (type: `array`):

Array of column keys. Listed keys appear first, remaining sorted alphabetically.

## `keepSheetColumnOrder` (type: `boolean`):

If true, preserves the existing column order from the sheet. Has no effect on an empty sheet.

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

Which row contains the column headers. Default: 1 (first row). Use if your sheet has title rows above the headers.

## `skipEmptyRows` (type: `boolean`):

Remove rows where all cells are blank before processing.

## `createBackup` (type: `boolean`):

Save existing sheet data to key-value store before making changes. Restore with loadBackup mode.

## `backupStore` (type: `string`):

Key-value store ID containing a backup to restore. Required for loadBackup mode.

## `safeReplace` (type: `boolean`):

For replace mode: write to a temp tab first, verify, then swap. If the write fails, your original data is untouched. Highly recommended. Default: true.

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

Preview exactly what would be written without touching the sheet. No charges applied.

## `autoCreateTab` (type: `boolean`):

If the specified sheet tab does not exist, create it automatically instead of failing. Default: true.

## `spreadsheets` (type: `array`):

Process multiple spreadsheets in one run. Each item: { spreadsheetId, range }. Overrides the spreadsheetId field.

## `maxConcurrency` (type: `integer`):

Parallel spreadsheet operations when using multiple spreadsheets. Default: 3. Max: 10.

## Actor input object example

```json
{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms",
  "mode": "read",
  "publicSpreadsheet": false,
  "limit": 250000,
  "offset": 0,
  "deduplicateByEquality": false,
  "keepSheetColumnOrder": false,
  "headerRow": 1,
  "skipEmptyRows": false,
  "createBackup": false,
  "safeReplace": true,
  "dryRun": false,
  "autoCreateTab": true,
  "maxConcurrency": 3
}
```

# 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 = {
    "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms"
};

// Run the Actor and wait for it to finish
const run = await client.actor("accurate_pouch/google-sheets-rw").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 = { "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms" }

# Run the Actor and wait for it to finish
run = client.actor("accurate_pouch/google-sheets-rw").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 '{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms"
}' |
apify call accurate_pouch/google-sheets-rw --silent --output-dataset

```

## MCP server setup

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

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/cCb9571aayJYx5KCp/builds/Wr2QLxln2JUTBdca2/openapi.json