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, 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

{
  "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

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:

.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')))"