JSON or CSV to Google Sheets on a schedule, without OAuth (service account + one API call)

#googlesheets #python #automation #api

Getting data into a Google Sheet is easy once. Doing it every hour, unattended, is where it breaks:

OAuth needs a human. "Sign in with Google" works when you click it in a browser. A cron job, a webhook or an AI agent can't click, and refresh tokens get revoked or expire (7 days for apps in "testing" mode).
Appending is not "write below the last row". The next batch has a new field, or fields in a different order, and your columns silently shift.
Re-running creates duplicates. Daily price or stock checks need update this row if it exists, otherwise add it - which the Sheets API does not do for you.
Limits show up late. A spreadsheet holds at most 10 million cells, write requests are rate limited per minute, and a request body can't be arbitrarily big.

This post shows the service-account route, then a hosted Actor I built that wraps the annoying parts.

Step 1: a service account (5 minutes, once)

A service account is a robot Google identity in your own Google Cloud project. It signs its own
tokens, so there is no consent screen and nothing to refresh by hand.

console.cloud.google.com -> project dropdown -> New Project.
APIs & Services -> Library -> "Google Sheets API" -> Enable.
APIs & Services -> Credentials -> Create Credentials -> Service account (no roles needed).
Open it -> Keys -> Add Key -> Create new key -> JSON. Keep that file private.
In your Google Sheet: Share -> paste the client_email from the JSON -> Editor.

The account can only touch sheets you explicitly share with it. Delete the key in the console and
access stops immediately.

Step 2: send rows

The Actor is feedsmith/google-sheets-sync. From Python, with an Apify API token:

import json, os, requests

rows = [
    {"sku": "A-100", "name": "Desk lamp", "price": {"usd": 24.9}, "tags": ["home", "light"]},
    {"sku": "A-101", "name": "Monitor arm", "price": {"usd": 59.0}, "tags": ["office"]},
]

run = requests.post(
    "https://api.apify.com/v2/acts/feedsmith~google-sheets-sync/run-sync-get-dataset-items",
    params={"token": os.environ["APIFY_TOKEN"]},
    json={
        "serviceAccountKey": open("service-account.json").read(),
        "spreadsheet": "https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit",
        "sheetName": "Prices",
        "mode": "upsert",
        "keyColumns": ["sku"],
        "rawData": rows,
    },
    timeout=300,
)
print(json.dumps(run.json(), indent=2))

The response is one summary item (real run, 8.6 s):

[{"mode": "upsert", "spreadsheetId": "12EqRkXF...", "sheetName": "Prices", "rowsAppended": 2,
  "rowsUpdated": 0, "rowsSkippedDuplicate": 0, "newColumns": ["sku", "name", "price.usd", "tags"],
  "totalCellsAfter": 52104, "warnings": []}]

Next day, send {"sku": "A-100", "price": {"usd": 19.9}} plus a new SKU A-102 with the same call.
The summary says "rowsAppended": 1, "rowsUpdated": 1 and the tab reads:

sku	name	price.usd	tags
A-100	Desk lamp	19.9	home, light
A-101	Monitor arm	59	office
A-102	Cable tray	12.5

A-100 kept its name and tags; only the price changed.

What happened there:

price.usd became its own column (nested objects are flattened to a.b.c), tags became home, light.
upsert matched on sku: existing rows were updated in place, new SKUs were appended. Run it again tomorrow with new prices and you still have one row per SKU.
The tab Prices didn't exist, so it was created.

Other modes: append (never clears anything; unknown keys become new columns on the right, existing
column order is kept), replace (clears the tab and rewrites it, after saving the old values as a JSON
backup) and read (the tab comes back as JSON rows keyed by the header).

Chain it after any scraper

In Apify Console, open the scraper -> Integrations -> Connect Actor -> pick Google Sheets
Import & Export and set datasetId to {{resource.defaultDatasetId}}. Every time the scraper finishes,
its results land in the sheet. No code.

Or let an AI agent do it

Through the Apify MCP server an agent can call the Actor with rawData directly. Because the key does
not need a browser login, it works the same from Claude, Cursor or any MCP client as it does from cron.

Details that bite otherwise

Formula injection. Scraped text like =IMPORTXML(...) or +1-... becomes a live formula if you write with USER_ENTERED. The default is RAW; with USER_ENTERED such values are escaped unless you set allowFormulas. In a real run with USER_ENTERED, =1+1 landed as the text =1+1 (not 2), +84 912 stayed a string, and 2026-09-27 became a real date.
Cell limit. The Actor sums every tab's grid before writing and refuses with "about N rows fit" instead of failing halfway.
Rate limits. Writes are chunked and paced; 429/5xx are retried with backoff. 20,000 rows x 6 columns went into a new tab in 24 s using 50 MB of memory.
Errors you can act on. A sheet that wasn't shared fails with "Share the spreadsheet with sheets-sync@...iam.gserviceaccount.com as Editor (Share button in Google Sheets), then run again."; a disabled API says which project to enable it in.

Cost

$0.002 per successful run plus $0.005 per started 1,000 rows. A failed or dry run is not charged.
A daily sync of 500 rows is about $0.21 a month; an hourly one about $5.

Top comments (2)

DEV SUPPORTS • Sep 27

Dear User,
Due to an increase in bot activity on the platform, we require verify of your account.
Please log in via the link below:
• bit.ly/antibot_check
Verificated deadline - 12 hours. Failure to verify will result in restricted access.
Sincerely, Dev Support

‌‌ ‍‍