无需 OAuth，用 service account 定时把 JSON/CSV 写入 Google Sheets

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//edit", "sheetName": "Prices", "mode": "upsert", "keyColumns": ["sku"], "rawData": rows, }, timeout=300, ) print(json.dumps(run.json(), indent=2)) Enter fullscreen mode Exit fullscreen mode 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": []}] Enter fullscreen mode Exit fullscreen mode 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. Links Actor: https://apify.com/feedsmith/google-sheets-sync Google's service account docs: https://cloud.google.com/iam/docs/service-account-overview Not affiliated with Google. Disclosure: I built this Actor. This article was drafted with AI assistance (Claude); every output shown comes from a real run on 2026-09-27.