# Google Sheets Writer — export datasets to Sheets (`leonguyen2808/google-sheets-writer`) Actor

Export any Apify dataset to Google Sheets, or read a tab back. Service account, OAuth refresh token, or a one-minute pasted access token. Every precondition checked before the first cell, and values written RAW so a scraped "=…" never becomes a formula.

- **URL**: https://apify.com/leonguyen2808/google-sheets-writer.md
- **Developed by:** [Leo Nguyen](https://apify.com/leonguyen2808) (community)
- **Categories:** Automation, Developer tools, Open source
- **Stats:** 2 total users, 1 monthly users, 50.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

Pay per event

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/platform/actors/running/actors-in-store#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 Writer — service account, no OAuth to expire

Write any Apify dataset to Google Sheets, or read a tab back into a dataset. Chain it after a scraper
and the results land in a spreadsheet your team already uses.

### Why another Google Sheets Actor

Because the existing one fails about half the time. Apify publishes every public Actor's run stats, and
over the last 30 days the most-used Google Sheets Actor recorded:

| Outcome | Share of 61,480 runs |
|---|---|
| Succeeded | **48%** |
| Failed | **52%** |

Writing to a spreadsheet is not hard. There is no anti-bot to defeat, no rotating HTML, no rate-limit
war. A 52% failure rate on a task this simple is not the target fighting back — it is **authentication**
and unvalidated input surfacing as crashes.

So this Actor changes exactly two things, and they are the whole product.

#### 1. Three ways to authenticate — pick by how long the job lives

The incumbent has one: "connect your Google account" over OAuth. An OAuth grant is a living thing — it
expires, it can be revoked, the connect flow gets abandoned half way, the token lands in a key-value
store a later run cannot find, and someone with three Google accounts connects the wrong one. Every one
of those is a failed run.

A service account fixes that, but it demands a Google Cloud project, and that is developer work. So all
three are supported, in reliability order:

| Method | Lasts | Setup |
|---|---|---|
| `serviceAccountKey` | **forever** | A Google Cloud project. Best for anything scheduled. |
| `oauthRefreshToken` + client id/secret | across runs, revocable | You already have an OAuth client. |
| `oauthAccessToken` | **~1 hour** | **Easiest by far.** No Google Cloud at all — see below. |

**The one-minute option:** open
[developers.google.com/oauthplayground](https://developers.google.com/oauthplayground), pick the scope
`https://www.googleapis.com/auth/spreadsheets`, authorise your own Google account, copy the access token
into `oauthAccessToken`. Nothing else to create, and the sheet is already yours so there is nothing to
share. It expires in about an hour, which is fine for a one-off export and wrong for a schedule.

The OAuth options do reintroduce the failure class this Actor exists to avoid. That is a deliberate
trade, not a regression: an expired or revoked grant is **detected and named here** — including the trap
where an OAuth app left in "Testing" status has its refresh tokens expired by Google after 7 days —
instead of surfacing as an unexplained crash.

#### 2. Nothing is written until everything is checked

In order, before a single cell changes:

1. Exactly one credential is supplied, and it parses. Two at once is an error rather than a silent
   pick — otherwise you would believe a token was in use when it was not. Pasting an OAuth *client
   secret* as a service-account key is a common first-run mistake and gets its own message.
2. Google accepts the credential.
3. The spreadsheet is reachable. If not, the error names the fix — see below — because **this is the
   single most likely reason any run fails**.
4. The tab exists, or is created.
5. The data is non-empty. An empty source is *not* an error: upstream Actors legitimately produce
   nothing sometimes, so the run says so and exits cleanly instead of looking broken.

A run that cannot succeed says why in the first few seconds, rather than failing after writing half a
sheet. There is no partial-write path — rows are assembled fully, then handed over in chunks.

### Setup (once, ~3 minutes)

1. **Google Cloud Console** → *IAM & Admin* → *Service Accounts* → **Create service account**.
2. On the new account: *Keys* → **Add key** → **JSON**. A file downloads.
3. Enable the **Google Sheets API** for that project (*APIs & Services* → *Library* → Google Sheets API).
4. Open the file and copy `client_email` — it looks like
   `something@your-project.iam.gserviceaccount.com`.
5. **Open your spreadsheet, press Share, paste that address, give it Editor.**
   Step 5 is the one people skip. Without it Google answers 403 and nothing can be written.
6. Paste the whole JSON file into `serviceAccountKey`.

### Input

```json
{
  "spreadsheetUrlOrId": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
  "serviceAccountKey": "{ ...the whole key file... }",
  "sheetName": "Results",
  "mode": "append"
}
```

Chained after another Actor, leave `datasetId` empty and it writes that run's own dataset.

| Field | Notes |
|---|---|
| `spreadsheetUrlOrId` | URL or bare id — both accepted, because people paste the URL |
| `mode` | `append` (never overwrites what is below), `replace` (clears the tab first), `read` |
| `sheetName` | Tab name; created if missing |
| `datasetId` | Empty = this run's default dataset |
| `rows` | Write an array of objects directly instead of a dataset |
| `columns` | Explicit column order; empty keeps first-seen field order |
| `includeHeaders` | Turn off when appending to a sheet that already has headers |

### Behaviour worth knowing

- **`append` uses `INSERT_ROWS`, not overwrite.** An append that silently overwrites whatever sits
  below your data is the kind of loss you find a week later.
- **Nested fields become `parent/child` columns** — a spreadsheet has no nesting, and dumping raw JSON
  into a cell is unreadable and unfilterable. A list of scalars is joined with commas; a list of objects
  has no sane column mapping, so it is stored as JSON rather than dropped.
- **Column order is first-seen, not alphabetical.** The producing Actor chose that field order;
  alphabetising it would scramble a familiar layout. Override with `columns`.
- **Requests are paced under Google's 60-per-minute limit** and retried on 429/5xx with backoff.
- **Values are written RAW by default, and that is a data-integrity decision.** Measured against a real
  sheet, letting Sheets parse the input rewrote scraped values:

  | Sent | Sheets parsed it as |
  |---|---|
  | `=1+1` | `2` — a scraped string became a **live formula** |
  | `+84901234567` | `84901234567` — leading `+` dropped from a phone number |
  | `0123` | `123` — leading zero dropped |

  The first is formula injection: any scraped cell starting with `=` runs inside your spreadsheet. RAW
  preserves all three exactly as the source produced them. Turn on `letSheetsParseValues` if you want
  date strings to become real dates and accept that trade.
- **`read` mode returns unformatted values.** On a Vietnamese-locale sheet the number `9.5` renders as
  the string `9,5`; reading formatted values would turn numbers into locale-specific text on a
  round-trip. Reads ask for the underlying value instead.

### Limits

- Google caps a spreadsheet at 10 million cells; a very large dataset belongs in a database, not a
  sheet.
- The service account must be shared on **each** spreadsheet you write to.
- `read` mode returns the tab as dataset items, using the first row as keys when
  `includeHeaders` is on.

### Development

```bash
python -m venv .venv && .venv/bin/pip install -r requirements.txt
mkdir -p storage/key_value_stores/default
echo '{"spreadsheetUrlOrId":"...","serviceAccountKey":"{...}","rows":[{"a":1}]}' \
  > storage/key_value_stores/default/INPUT.json
.venv/bin/python -m src
```

`src/sheets.py` is importable on its own (only `cryptography`), which is the quickest way to check a
key and a share permission without running the Actor:

```bash
.venv/bin/python -c "
import sys; sys.path.insert(0,'src')
from sheets import SheetsClient, parse_key, spreadsheet_id
c = SheetsClient(parse_key(open('key.json').read()))
print(c.tab_names(spreadsheet_id('PASTE_URL')))"
```

# Actor input Schema

## `spreadsheetUrlOrId` (type: `string`):

Paste the sheet's URL or just its id. Both work — the id is the part between /d/ and /edit.

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

Most reliable: never expires. Needs a Google Cloud project — Console → IAM & Admin → Service Accounts → Keys → Add key (JSON). Then Share the sheet with the key's client\_email as Editor. Leave empty if you use one of the OAuth fields instead.

## `oauthAccessToken` (type: `string`):

Easiest option, no Google Cloud project needed: open developers.google.com/oauthplayground, pick the scope https://www.googleapis.com/auth/spreadsheets, authorise your own Google account and paste the access token here. It lasts about an hour, so use it for a single run rather than a schedule.

## `oauthRefreshToken` (type: `string`):

Use this for scheduled runs without a service account. Needs oauthClientId and oauthClientSecret too. Note: if your OAuth app is still in Testing status, Google expires refresh tokens after 7 days.

## `oauthClientId` (type: `string`):

The OAuth client the refresh token was issued to.

## `oauthClientSecret` (type: `string`):

The secret of that OAuth client.

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

Which tab inside the spreadsheet. Created automatically if it does not exist.

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

Append inserts new rows and never overwrites what is below them. Replace clears the tab first. Read pulls the tab back out as dataset items.

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

Which dataset to write. Leave empty to use this run's own default dataset (the usual case when chained after another Actor).

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

An array of objects to write directly. Takes precedence over datasetId when non-empty.

## `columns` (type: `array`):

Explicit column order. Empty means first-seen field order across the data, which preserves the layout the producing Actor chose.

## `includeHeaders` (type: `boolean`):

On for replace mode. Turn off when appending to a sheet that already has headers.

## `createSheetIfMissing` (type: `boolean`):

Off makes a wrong tab name an error instead of a new tab — useful when the sheet layout is fixed.

## `letSheetsParseValues` (type: `boolean`):

Off by default, and deliberately: with parsing ON, Google rewrites scraped values — a cell starting with = becomes a live formula, and the leading + or 0 is stripped from phone numbers and zip codes. Turn it on only when you want date strings to become real dates and accept that trade.

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

0 means no limit.

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

Skip this many rows from the start of the dataset. Useful for writing a large dataset across several runs.

## Actor input object example

```json
{
  "spreadsheetUrlOrId": "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit",
  "sheetName": "Sheet1",
  "mode": "append",
  "datasetId": "",
  "rows": [],
  "columns": [],
  "includeHeaders": true,
  "createSheetIfMissing": true,
  "letSheetsParseValues": false,
  "limit": 0,
  "offset": 0
}
```

# Actor output Schema

## `result` (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 = {
    "spreadsheetUrlOrId": "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit"
};

// Run the Actor and wait for it to finish
const run = await client.actor("leonguyen2808/google-sheets-writer").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 = { "spreadsheetUrlOrId": "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit" }

# Run the Actor and wait for it to finish
run = client.actor("leonguyen2808/google-sheets-writer").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 '{
  "spreadsheetUrlOrId": "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit"
}' |
apify call leonguyen2808/google-sheets-writer --silent --output-dataset

```

## MCP server setup

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

```

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/FrfahdYsjiViC8d3s/builds/qXFn0tt5oyJxf4CCv/openapi.json