# Google Sheets Io (`devon_gtme/google-sheets-io`) Actor

Import Apify datasets into Google Sheets, or read sheets back into a dataset - authenticated with a Google service account, so the connection never expires and scheduled pipelines never break. Append, replace or read. Flattens nested objects, stable headers, auto 429 backoff. Free.

- **URL**: https://apify.com/devon\_gtme/google-sheets-io.md
- **Developed by:** [Devon Kellar](https://apify.com/devon_gtme) (community)
- **Categories:** Developer tools, Automation
- **Stats:** 1 total users, 0 monthly users, 0.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

Pay per usage

This Actor is paid per platform usage. The Actor is free to use, and you only pay for the Apify platform usage, which gets cheaper the higher subscription plan you have.

Learn more: https://docs.apify.com/platform/actors/running/actors-in-store#pay-per-usage

## What's an Apify Actor?

Actors are a software tools running on the Apify platform, for all kinds of web data extraction and automation use cases.
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.

In JavaScript/TypeScript projects, use official [JavaScript/TypeScript client](https://docs.apify.com/api/client/js.md):

```bash
npm install apify-client
```

In Python projects, use official [Python client library](https://docs.apify.com/api/client/python.md):

```bash
pip install apify-client
```

In shell scripts, use [Apify CLI](https://docs.apify.com/cli/docs.md):

````bash
# MacOS / Linux
curl -fsSL https://apify.com/install-cli.sh | bash
# Windows
irm https://apify.com/install-cli.ps1 | iex
```bash

In AI frameworks, you might use the [Apify MCP server](https://docs.apify.com/platform/integrations/mcp.md).

If your project is in a different language, use 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 — Service Account Auth (Never Expires)

Send Apify data to **Google Sheets** — or read a sheet back into a dataset — with a connection that **never expires and never needs re-authorizing**.

If you've ever had a scheduled export silently die with *"authorization expired"*, *"invalid_grant"*, or *"token has been revoked"*, this actor is the fix. OAuth-based Sheets integrations depend on a personal login token that Google expires or revokes — and every time it does, your pipeline breaks until a human clicks "re-authorize". This actor uses a **Google service account** instead: a robot identity with its own key. Keys don't expire, there's no consent screen, and a scheduled run in six months behaves exactly like the one you ran today. **Free to use.**

### What it does

| Mode | What happens |
|---|---|
| **append** | Adds rows below the existing data. Headers stay stable; genuinely new fields become new columns on the right. |
| **replace** | Clears the sheet (tab), then writes a fresh header row + all rows. |
| **read** | Reads the sheet (first row = headers) into the run's dataset — export a Sheet as JSON/CSV/Excel or feed it to another actor. |

Plus the details that make it dependable:

- **Never fails on rate limits** — writes are batched (up to 5,000 rows per request), calls are spaced under Google's ~60-requests/minute quota, and any 429 is retried with exponential backoff. The actor slows down; it doesn't die.
- **10-million-cell cap pre-check** — if the write would exceed Google Sheets' hard limit, you get one clear error *before* anything is written. No half-written sheets.
- **Nested data flattened** — `{"contact": {"email": "a@b.com"}}` becomes a `contact.email` column; arrays are stored as JSON.
- **Stable columns across appends** — the existing header row is respected; new keys extend it on the right, so your formulas and pivot ranges keep working.
- **Paste the URL, not just the ID** — `spreadsheetId` accepts the full browser URL.
- **Missing tab? Created automatically** in write modes.
- **Integration-friendly** — attach it to any actor as an integration and it auto-detects the triggering run's dataset; or pass a `datasetId` / inline `rawData` yourself.
- **Clear errors** — every failure tells you exactly what to do (which email to share the sheet with, which API to enable, what was wrong with the key).

### One-time setup (~5 minutes, never again)

You'll create a *service account* — think of it as a robot colleague with its own email address — and share your spreadsheet with it.

1. **Create (or pick) a Google Cloud project** — go to [console.cloud.google.com](https://console.cloud.google.com/), sign in, and create a project (any name, e.g. `sheets-automation`). Free; no billing needed.
2. **Enable the Google Sheets API** — in that project, open [APIs & Services → Library](https://console.cloud.google.com/apis/library/sheets.googleapis.com) and click **Enable**.
3. **Create a service account** — [IAM & Admin → Service Accounts](https://console.cloud.google.com/iam/service-accounts) → **Create service account**. Name it anything (e.g. `apify-sheets`). No roles needed — click through and **Done**.
4. **Download its JSON key** — click the new service account → **Keys** tab → **Add key → Create new key → JSON**. A `.json` file downloads. This file *is* the credential — treat it like a password.
5. **Share your spreadsheet with the robot** — open the `.json` file and copy the `client_email` value (looks like `apify-sheets@your-project.iam.gserviceaccount.com`). In Google Sheets, click **Share**, paste that email, give it **Editor**, untick "Notify", and share — exactly like sharing with a colleague.
6. **Paste the key into this actor** — open the `.json` file in any text editor, copy **everything**, and paste it into the *Service Account JSON Key* field. It's stored encrypted by Apify.

That's it — forever. No consent screens, no token refresh, no re-authorization. To revoke access later, just un-share the spreadsheet or delete the key in Google Cloud.

### Inputs

| Field | Notes |
|---|---|
| `serviceAccountKey` | Full contents of the service-account `.json` key (stored encrypted). |
| `mode` | `append` (default) / `replace` / `read`. |
| `spreadsheetId` | Bare ID or full `https://docs.google.com/spreadsheets/d/…` URL. |
| `sheetName` | Tab name. Defaults to the first tab; auto-created in write modes. |
| `datasetId` | Apify dataset to write. Optional when running as an integration — the triggering run's dataset is detected automatically. |
| `rawData` | Alternative inline data: JSON array of objects. |
| `range` | Optional A1 range for `read` (e.g. `A1:D50`). |

### Output

Write modes push a summary record to the dataset (and to the `OUTPUT` key-value record):

```json
{
  "mode": "append",
  "rowsWritten": 1250,
  "columns": ["company", "contact.name", "contact.email", "score"],
  "columnCount": 4,
  "sheetName": "Leads",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/…/edit",
  "durationSecs": 8.4
}
````

Read mode pushes each sheet row as a dataset item, keyed by the header row.

### Typical usage

- **Actor → Sheet, on every run:** add this actor as an [integration](https://docs.apify.com/platform/integrations) on any scraper with `mode: append` — new results land in your sheet automatically, and the connection never rots.
- **Nightly refresh:** `mode: replace` on a [schedule](https://docs.apify.com/platform/schedules) keeps a sheet as an always-current mirror of your latest dataset.
- **Sheet as input:** `mode: read` turns a spreadsheet your team edits into a dataset any actor can consume.

### Why service accounts beat OAuth here

| | OAuth (sign in with Google) | Service account (this actor) |
|---|---|---|
| Token lifetime | Expires; revoked by password changes, security events, 6-month inactivity | Key never expires |
| Scheduled runs | Break until someone re-authorizes | Deterministic, forever |
| Access scope | Your entire Drive | Only spreadsheets you explicitly share |
| Revoking | Hunt through Google security settings | Un-share the sheet, or delete the key |

***

**Need something more custom?** Different targets (Excel/BigQuery), formatting, dedup/upsert logic, multi-tab writes, or a fully managed data pipeline — message me via my Apify profile and I'll build it.

# Actor input Schema

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

Paste the FULL contents of your Google service-account JSON key file (starts with {"type": "service\_account", ...}). It is stored encrypted. One-time setup (~5 min, see README): create a Google Cloud project → enable the Google Sheets API → create a service account → download its JSON key → share your spreadsheet with the service account's email address (Editor).

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

append = add rows below existing data (headers stay stable, new columns are added on the right). replace = clear the sheet and write fresh (header row + data). read = read the sheet into the run's dataset (first row is treated as headers).

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

The target spreadsheet — either the bare ID (e.g. 1AbC...xyz) or the full URL copied from your browser (https://docs.google.com/spreadsheets/d/1AbC...xyz/edit). Remember to share this spreadsheet with your service account's email address.

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

The tab to write to / read from. Defaults to the first tab. In write modes, the tab is created automatically if it doesn't exist.

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

Dataset whose items to write to the sheet (append/replace modes). Leave empty when this actor runs as an integration on another actor — the triggering run's dataset is detected automatically. Ignored in read mode.

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

Alternative to a dataset: a JSON array of objects to write, e.g. \[{"name": "Acme", "contact": {"email": "a@b.com"}}]. Nested objects become dot-notation columns (contact.email).

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

Optional A1 range to read, e.g. A1:D50. Defaults to the whole sheet. Read mode only.

## `payload` (type: `object`):

Filled automatically when this actor is triggered as an integration/webhook from another actor's run — used to auto-detect the dataset. Leave empty otherwise.

## Actor input object example

```json
{
  "mode": "append"
}
```

# 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": "append"
};

// Run the Actor and wait for it to finish
const run = await client.actor("devon_gtme/google-sheets-io").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": "append" }

# Run the Actor and wait for it to finish
run = client.actor("devon_gtme/google-sheets-io").call(run_input=run_input)

# Fetch and print Actor results from the run's dataset (if there are any)
print("💾 Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"])
for item in client.dataset(run["defaultDatasetId"]).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": "append"
}' |
apify call devon_gtme/google-sheets-io --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "command": "npx",
            "args": [
                "mcp-remote",
                "https://mcp.apify.com/?tools=devon_gtme/google-sheets-io",
                "--header",
                "Authorization: Bearer <YOUR_API_TOKEN>"
            ]
        }
    }
}

```

## OpenAPI specification

```json
{
    "openapi": "3.0.1",
    "info": {
        "title": "Google Sheets Io",
        "description": "Import Apify datasets into Google Sheets, or read sheets back into a dataset - authenticated with a Google service account, so the connection never expires and scheduled pipelines never break. Append, replace or read. Flattens nested objects, stable headers, auto 429 backoff. Free.",
        "version": "0.1",
        "x-build-id": "atyKBxlpbvTIjDKeN"
    },
    "servers": [
        {
            "url": "https://api.apify.com/v2"
        }
    ],
    "paths": {
        "/acts/devon_gtme~google-sheets-io/run-sync-get-dataset-items": {
            "post": {
                "operationId": "run-sync-get-dataset-items-devon_gtme-google-sheets-io",
                "x-openai-isConsequential": false,
                "summary": "Executes an Actor, waits for its completion, and returns Actor's dataset items in response.",
                "tags": [
                    "Run Actor"
                ],
                "requestBody": {
                    "required": true,
                    "content": {
                        "application/json": {
                            "schema": {
                                "$ref": "#/components/schemas/inputSchema"
                            }
                        }
                    }
                },
                "parameters": [
                    {
                        "name": "token",
                        "in": "query",
                        "required": true,
                        "schema": {
                            "type": "string"
                        },
                        "description": "Enter your Apify token here"
                    }
                ],
                "responses": {
                    "200": {
                        "description": "OK"
                    }
                }
            }
        },
        "/acts/devon_gtme~google-sheets-io/runs": {
            "post": {
                "operationId": "runs-sync-devon_gtme-google-sheets-io",
                "x-openai-isConsequential": false,
                "summary": "Executes an Actor and returns information about the initiated run in response.",
                "tags": [
                    "Run Actor"
                ],
                "requestBody": {
                    "required": true,
                    "content": {
                        "application/json": {
                            "schema": {
                                "$ref": "#/components/schemas/inputSchema"
                            }
                        }
                    }
                },
                "parameters": [
                    {
                        "name": "token",
                        "in": "query",
                        "required": true,
                        "schema": {
                            "type": "string"
                        },
                        "description": "Enter your Apify token here"
                    }
                ],
                "responses": {
                    "200": {
                        "description": "OK",
                        "content": {
                            "application/json": {
                                "schema": {
                                    "$ref": "#/components/schemas/runsResponseSchema"
                                }
                            }
                        }
                    }
                }
            }
        },
        "/acts/devon_gtme~google-sheets-io/run-sync": {
            "post": {
                "operationId": "run-sync-devon_gtme-google-sheets-io",
                "x-openai-isConsequential": false,
                "summary": "Executes an Actor, waits for completion, and returns the OUTPUT from Key-value store in response.",
                "tags": [
                    "Run Actor"
                ],
                "requestBody": {
                    "required": true,
                    "content": {
                        "application/json": {
                            "schema": {
                                "$ref": "#/components/schemas/inputSchema"
                            }
                        }
                    }
                },
                "parameters": [
                    {
                        "name": "token",
                        "in": "query",
                        "required": true,
                        "schema": {
                            "type": "string"
                        },
                        "description": "Enter your Apify token here"
                    }
                ],
                "responses": {
                    "200": {
                        "description": "OK"
                    }
                }
            }
        }
    },
    "components": {
        "schemas": {
            "inputSchema": {
                "type": "object",
                "required": [
                    "serviceAccountKey",
                    "spreadsheetId"
                ],
                "properties": {
                    "serviceAccountKey": {
                        "title": "Service Account JSON Key",
                        "type": "string",
                        "description": "Paste the FULL contents of your Google service-account JSON key file (starts with {\"type\": \"service_account\", ...}). It is stored encrypted. One-time setup (~5 min, see README): create a Google Cloud project → enable the Google Sheets API → create a service account → download its JSON key → share your spreadsheet with the service account's email address (Editor)."
                    },
                    "mode": {
                        "title": "Mode",
                        "enum": [
                            "append",
                            "replace",
                            "read"
                        ],
                        "type": "string",
                        "description": "append = add rows below existing data (headers stay stable, new columns are added on the right). replace = clear the sheet and write fresh (header row + data). read = read the sheet into the run's dataset (first row is treated as headers).",
                        "default": "append"
                    },
                    "spreadsheetId": {
                        "title": "Spreadsheet ID or URL",
                        "type": "string",
                        "description": "The target spreadsheet — either the bare ID (e.g. 1AbC...xyz) or the full URL copied from your browser (https://docs.google.com/spreadsheets/d/1AbC...xyz/edit). Remember to share this spreadsheet with your service account's email address."
                    },
                    "sheetName": {
                        "title": "Sheet (Tab) Name",
                        "type": "string",
                        "description": "The tab to write to / read from. Defaults to the first tab. In write modes, the tab is created automatically if it doesn't exist."
                    },
                    "datasetId": {
                        "title": "Apify Dataset ID",
                        "type": "string",
                        "description": "Dataset whose items to write to the sheet (append/replace modes). Leave empty when this actor runs as an integration on another actor — the triggering run's dataset is detected automatically. Ignored in read mode."
                    },
                    "rawData": {
                        "title": "Raw JSON Data",
                        "type": "array",
                        "description": "Alternative to a dataset: a JSON array of objects to write, e.g. [{\"name\": \"Acme\", \"contact\": {\"email\": \"a@b.com\"}}]. Nested objects become dot-notation columns (contact.email)."
                    },
                    "range": {
                        "title": "Range (read mode)",
                        "type": "string",
                        "description": "Optional A1 range to read, e.g. A1:D50. Defaults to the whole sheet. Read mode only."
                    },
                    "payload": {
                        "title": "Integration payload (automatic)",
                        "type": "object",
                        "description": "Filled automatically when this actor is triggered as an integration/webhook from another actor's run — used to auto-detect the dataset. Leave empty otherwise."
                    }
                }
            },
            "runsResponseSchema": {
                "type": "object",
                "properties": {
                    "data": {
                        "type": "object",
                        "properties": {
                            "id": {
                                "type": "string"
                            },
                            "actId": {
                                "type": "string"
                            },
                            "userId": {
                                "type": "string"
                            },
                            "startedAt": {
                                "type": "string",
                                "format": "date-time",
                                "example": "2025-01-08T00:00:00.000Z"
                            },
                            "finishedAt": {
                                "type": "string",
                                "format": "date-time",
                                "example": "2025-01-08T00:00:00.000Z"
                            },
                            "status": {
                                "type": "string",
                                "example": "READY"
                            },
                            "meta": {
                                "type": "object",
                                "properties": {
                                    "origin": {
                                        "type": "string",
                                        "example": "API"
                                    },
                                    "userAgent": {
                                        "type": "string"
                                    }
                                }
                            },
                            "stats": {
                                "type": "object",
                                "properties": {
                                    "inputBodyLen": {
                                        "type": "integer",
                                        "example": 2000
                                    },
                                    "rebootCount": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "restartCount": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "resurrectCount": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "computeUnits": {
                                        "type": "integer",
                                        "example": 0
                                    }
                                }
                            },
                            "options": {
                                "type": "object",
                                "properties": {
                                    "build": {
                                        "type": "string",
                                        "example": "latest"
                                    },
                                    "timeoutSecs": {
                                        "type": "integer",
                                        "example": 300
                                    },
                                    "memoryMbytes": {
                                        "type": "integer",
                                        "example": 1024
                                    },
                                    "diskMbytes": {
                                        "type": "integer",
                                        "example": 2048
                                    }
                                }
                            },
                            "buildId": {
                                "type": "string"
                            },
                            "defaultKeyValueStoreId": {
                                "type": "string"
                            },
                            "defaultDatasetId": {
                                "type": "string"
                            },
                            "defaultRequestQueueId": {
                                "type": "string"
                            },
                            "buildNumber": {
                                "type": "string",
                                "example": "1.0.0"
                            },
                            "containerUrl": {
                                "type": "string"
                            },
                            "usage": {
                                "type": "object",
                                "properties": {
                                    "ACTOR_COMPUTE_UNITS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATASET_READS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATASET_WRITES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "KEY_VALUE_STORE_READS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "KEY_VALUE_STORE_WRITES": {
                                        "type": "integer",
                                        "example": 1
                                    },
                                    "KEY_VALUE_STORE_LISTS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "REQUEST_QUEUE_READS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "REQUEST_QUEUE_WRITES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATA_TRANSFER_INTERNAL_GBYTES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATA_TRANSFER_EXTERNAL_GBYTES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "PROXY_RESIDENTIAL_TRANSFER_GBYTES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "PROXY_SERPS": {
                                        "type": "integer",
                                        "example": 0
                                    }
                                }
                            },
                            "usageTotalUsd": {
                                "type": "number",
                                "example": 0.00005
                            },
                            "usageUsd": {
                                "type": "object",
                                "properties": {
                                    "ACTOR_COMPUTE_UNITS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATASET_READS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATASET_WRITES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "KEY_VALUE_STORE_READS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "KEY_VALUE_STORE_WRITES": {
                                        "type": "number",
                                        "example": 0.00005
                                    },
                                    "KEY_VALUE_STORE_LISTS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "REQUEST_QUEUE_READS": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "REQUEST_QUEUE_WRITES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATA_TRANSFER_INTERNAL_GBYTES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "DATA_TRANSFER_EXTERNAL_GBYTES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "PROXY_RESIDENTIAL_TRANSFER_GBYTES": {
                                        "type": "integer",
                                        "example": 0
                                    },
                                    "PROXY_SERPS": {
                                        "type": "integer",
                                        "example": 0
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
```