# Google Sheets Import & Export — No OAuth, No Token Expiry (`kaz_kakyo/google-sheets`) Actor Export any Apify dataset to Google Sheets, or read a sheet into a dataset. Authenticates with a Google service account — a 2-minute one-time setup that never expires, never needs re-consent, and never breaks mid-schedule. Built for scheduled scraper-to-sheet pipelines. - **URL**: https://apify.com/kaz\_kakyo/google-sheets.md - **Developed by:** [Heim AI](https://apify.com/kaz_kakyo) (community) - **Categories:** Integrations, Automation, AI - **Stats:** 1 total users, 0 monthly users, 66.7% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $0.50 / 1,000 row written (zero setup)s This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events. Learn more: https://docs.apify.com/platform/actors/running/actors-in-store#pay-per-event ## 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 — No OAuth, No Token Expiry **The reliable way to get scraper results into Google Sheets.** Authenticates with a Google **service account** — a one-time 2-minute setup that never expires, never asks for re-consent, and never breaks a scheduled pipeline. No OAuth pop-ups, no "token revoked" failures at 3 a.m. Export any Apify dataset to a sheet, write JSON rows directly, or read a sheet back into a dataset. ### Why service-account auth OAuth-based Sheets integrations fail in production for one structural reason: user tokens expire and get revoked, and a headless scheduled run can't re-consent. A service account is Google's mechanism for exactly this case — machine-to-machine access with a key that keeps working. You share the spreadsheet with the service account's email once, like sharing with a colleague. **Zero setup (30 seconds):** open your spreadsheet → **Share** → add `sheets-only@telehealth-mr.iam.gserviceaccount.com` as **Editor**. Done — leave `serviceAccountKey` empty and runs bill at the standard rate. **Bring your own service account (~2 minutes, discounted rate + full custody):** 1. In [Google Cloud Console](https://console.cloud.google.com), create a project (or use any existing one) and enable the **Google Sheets API**. 2. IAM & Admin → Service Accounts → **Create service account** (any name, no roles needed). 3. Open it → Keys → **Add key → JSON**. Download the file. 4. Open your spreadsheet → **Share** → add the service account's email (`...@...iam.gserviceaccount.com`) as **Editor**. 5. Paste the JSON file's contents into this actor's `serviceAccountKey` input (stored encrypted). ### What it does | Mode | Input | Output | |---|---|---| | `dataset-to-sheet` | dataset ID + spreadsheet | Dataset items as rows (auto header, nested fields flattened to `a.b` columns) | | `json-to-sheet` | JSON rows + spreadsheet | Your rows in the sheet | | `sheet-to-dataset` | spreadsheet (+ optional range) | Sheet rows as dataset items (first row = keys) | - **Replace or append**: replace clears the tab first (scheduled snapshots); append adds below existing rows (running logs). Headers are handled automatically. - Missing sheet tabs are **created automatically**. - Accepts the full spreadsheet **URL or bare ID**; nested objects flatten to dot-notation columns; arrays serialize to JSON. - `USER_ENTERED` mode available when you want Sheets to parse dates/numbers/formulas. ### The pipeline this was built for Any scraper → Sheets, on a schedule, unattended: 1. Schedule your scraper (any actor). 2. Add a **webhook/integration on run succeeded** that calls this actor with `{ "datasetId": "{{resource.defaultDatasetId}}", "spreadsheet": "", "append": false }`. 3. Your sheet is a live, always-current view of the latest run. Dashboards, `IMPORTRANGE`, Looker Studio — all downstream of a sheet that updates itself. ### Example input ```json { "mode": "dataset-to-sheet", "serviceAccountKey": "{ ...paste the JSON key file... }", "spreadsheet": "https://docs.google.com/spreadsheets/d/1AbC.../edit", "sheetName": "Leads", "datasetId": "x1y2z3...", "append": false } ```` ### Pricing Pay per event: `row-written` per data row written, `row-read` per row read. Zero-setup runs (built-in service account) bill the `-zero-setup` variants at the standard rate; bring your own service account for the discounted rate. Header rows are free. Empty sources charge nothing. Set `maxRows` to cap spend. ### Scope & fair use Your key and your data go only to Google's official Sheets REST API — nothing is stored beyond the run. The `serviceAccountKey` input is encrypted at rest by the Apify platform. This actor is not affiliated with Google. Sheets limits apply (10M cells per spreadsheet, 50k characters per cell — long values are truncated with `…`). **Formula-injection protection built in:** exported data (especially scraped web content) can contain strings like `=IMPORTXML(...)` that would execute as live formulas and leak your sheet's contents to external URLs. In `USER_ENTERED` mode this actor escapes formula-shaped values to plain text by default; `RAW` mode is inherently safe. Only enable *Allow formulas* for data you generated yourself. *** *Found this useful? A review on the [actor page](https://apify.com/kaz_kakyo/google-sheets) helps other people find it — and tells me what to build next. Hit a problem? [Open an issue](https://apify.com/kaz_kakyo/google-sheets/issues) and I'll fix it fast.* ### Telemetry Each run records one anonymous telemetry event to the developer's own storage: a salted hash of the caller account ID (never the raw ID), the run origin (e.g. web console vs API), and a timestamp. No inputs, results, or personal data are collected - this only measures adoption (distinct and returning users). # Actor input Schema ## `mode` (type: `string`): `dataset-to-sheet` writes an Apify dataset to a sheet; `json-to-sheet` writes rows you pass directly; `sheet-to-dataset` reads a sheet into this run's dataset. ## `serviceAccountKey` (type: `string`): Leave empty for zero setup: just share your spreadsheet (Editor) with sheets-only@telehealth-mr.iam.gserviceaccount.com, and rows are billed at the standard rate. Or bring your own service account for the discounted rate and full custody: console.cloud.google.com → IAM & Admin → Service Accounts → Create → Keys → Add key (JSON), enable the Sheets API, share your sheet with its email, paste the JSON here. Either way there is no OAuth and nothing expires. Stored encrypted, sent only to Google. ## `spreadsheet` (type: `string`): Full URL (https://docs.google.com/spreadsheets/d/...) or the bare spreadsheet ID. ## `sheetName` (type: `string`): Tab to write to / read from. Defaults to the first tab. Created automatically when writing to a tab that doesn't exist. ## `datasetId` (type: `string`): ID of the Apify dataset to export, e.g. the default dataset of a scraper run. Tip: trigger this actor from a webhook/integration on run success and pass `{{resource.defaultDatasetId}}`. ## `datasetView` (type: `string`): Optional dataset view name (from the source actor's output schema) to apply field selection/ordering. ## `rows` (type: `array`): `json-to-sheet`: array of JSON objects to write. ## `columns` (type: `array`): Optional explicit column order (flattened keys, e.g. `address.postcode`). Empty = auto from data, first-seen order. ## `append` (type: `boolean`): ON: add rows below existing data (header written only if the sheet is empty). OFF: clear the tab and write fresh — right for scheduled snapshots. ## `range` (type: `string`): Optional A1 range to read, e.g. `A1:F100`. Defaults to the whole tab. First row is treated as the header. ## `valueInputOption` (type: `string`): `RAW` writes values as-is (safe default); `USER_ENTERED` lets Sheets parse numbers and dates as if typed by hand. In `USER_ENTERED` mode, formula-shaped values (starting with `=`, `+`, `-`, `@`) are escaped to plain text so exported data can never execute as formulas — enable "Allow formulas" only if you trust every value. ## `allowFormulas` (type: `boolean`): DANGEROUS with scraped or third-party data: lets values like `=IMPORTXML(...)` execute as live formulas in your spreadsheet, which can leak sheet contents to external URLs. Leave OFF unless you generated every value yourself. ## `maxRows` (type: `integer`): Cap on rows written or read per run. Keeps cost predictable. ## Actor input object example ```json { "mode": "dataset-to-sheet", "append": false, "valueInputOption": "RAW", "allowFormulas": false, "maxRows": 100000 } ``` # Actor output Schema ## `summary` (type: `string`): No description ## `rows` (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 '' with your token const client = new ApifyClient({ token: '', }); // Prepare Actor input const input = {}; // Run the Actor and wait for it to finish const run = await client.actor("kaz_kakyo/google-sheets").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 '' with your token. client = ApifyClient("") # Prepare the Actor input run_input = {} # Run the Actor and wait for it to finish run = client.actor("kaz_kakyo/google-sheets").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 '{}' | apify call kaz_kakyo/google-sheets --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "command": "npx", "args": [ "mcp-remote", "https://mcp.apify.com/?tools=kaz_kakyo/google-sheets", "--header", "Authorization: Bearer " ] } } } ``` ## OpenAPI specification ```json { "openapi": "3.0.1", "info": { "title": "Google Sheets Import & Export — No OAuth, No Token Expiry", "description": "Export any Apify dataset to Google Sheets, or read a sheet into a dataset. Authenticates with a Google service account — a 2-minute one-time setup that never expires, never needs re-consent, and never breaks mid-schedule. Built for scheduled scraper-to-sheet pipelines.", "version": "0.0", "x-build-id": "HcNVmpffbSHcVmyAx" }, "servers": [ { "url": "https://api.apify.com/v2" } ], "paths": { "/acts/kaz_kakyo~google-sheets/run-sync-get-dataset-items": { "post": { "operationId": "run-sync-get-dataset-items-kaz_kakyo-google-sheets", "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/kaz_kakyo~google-sheets/runs": { "post": { "operationId": "runs-sync-kaz_kakyo-google-sheets", "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/kaz_kakyo~google-sheets/run-sync": { "post": { "operationId": "run-sync-kaz_kakyo-google-sheets", "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": [ "mode", "spreadsheet" ], "properties": { "mode": { "title": "Mode", "enum": [ "dataset-to-sheet", "json-to-sheet", "sheet-to-dataset" ], "type": "string", "description": "`dataset-to-sheet` writes an Apify dataset to a sheet; `json-to-sheet` writes rows you pass directly; `sheet-to-dataset` reads a sheet into this run's dataset.", "default": "dataset-to-sheet" }, "serviceAccountKey": { "title": "Service account key (optional — cheaper)", "type": "string", "description": "Leave empty for zero setup: just share your spreadsheet (Editor) with sheets-only@telehealth-mr.iam.gserviceaccount.com, and rows are billed at the standard rate. Or bring your own service account for the discounted rate and full custody: console.cloud.google.com → IAM & Admin → Service Accounts → Create → Keys → Add key (JSON), enable the Sheets API, share your sheet with its email, paste the JSON here. Either way there is no OAuth and nothing expires. Stored encrypted, sent only to Google." }, "spreadsheet": { "title": "Spreadsheet URL or ID", "type": "string", "description": "Full URL (https://docs.google.com/spreadsheets/d/...) or the bare spreadsheet ID." }, "sheetName": { "title": "Sheet tab name", "type": "string", "description": "Tab to write to / read from. Defaults to the first tab. Created automatically when writing to a tab that doesn't exist." }, "datasetId": { "title": "Dataset ID (dataset → sheet)", "type": "string", "description": "ID of the Apify dataset to export, e.g. the default dataset of a scraper run. Tip: trigger this actor from a webhook/integration on run success and pass `{{resource.defaultDatasetId}}`." }, "datasetView": { "title": "Dataset view", "type": "string", "description": "Optional dataset view name (from the source actor's output schema) to apply field selection/ordering." }, "rows": { "title": "Rows (JSON → sheet)", "type": "array", "description": "`json-to-sheet`: array of JSON objects to write." }, "columns": { "title": "Columns", "type": "array", "description": "Optional explicit column order (flattened keys, e.g. `address.postcode`). Empty = auto from data, first-seen order.", "items": { "type": "string" } }, "append": { "title": "Append instead of replace", "type": "boolean", "description": "ON: add rows below existing data (header written only if the sheet is empty). OFF: clear the tab and write fresh — right for scheduled snapshots.", "default": false }, "range": { "title": "Range (sheet → dataset)", "type": "string", "description": "Optional A1 range to read, e.g. `A1:F100`. Defaults to the whole tab. First row is treated as the header." }, "valueInputOption": { "title": "Value interpretation", "enum": [ "RAW", "USER_ENTERED" ], "type": "string", "description": "`RAW` writes values as-is (safe default); `USER_ENTERED` lets Sheets parse numbers and dates as if typed by hand. In `USER_ENTERED` mode, formula-shaped values (starting with `=`, `+`, `-`, `@`) are escaped to plain text so exported data can never execute as formulas — enable \"Allow formulas\" only if you trust every value.", "default": "RAW" }, "allowFormulas": { "title": "Allow formulas (USER_ENTERED)", "type": "boolean", "description": "DANGEROUS with scraped or third-party data: lets values like `=IMPORTXML(...)` execute as live formulas in your spreadsheet, which can leak sheet contents to external URLs. Leave OFF unless you generated every value yourself.", "default": false }, "maxRows": { "title": "Max rows", "minimum": 1, "maximum": 500000, "type": "integer", "description": "Cap on rows written or read per run. Keeps cost predictable.", "default": 100000 } } }, "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 } } } } } } } } } } ```