# Dataset to Google Sheets Sync (append, replace, upsert) (`osel_house/dataset-to-google-sheets`) Actor Send any Apify dataset to a Google Sheet without OAuth: share the sheet with a service account, then append, replace or upsert by key. Retries Google rate limits, dedupes, never fails on an empty dataset. - **URL**: https://apify.com/osel\_house/dataset-to-google-sheets.md - **Developed by:** [Tenzin Phuntsok](https://apify.com/osel_house) (community) - **Categories:** Integrations, Developer tools - **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $0.002 / sync run 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/actors/running/actors-in-store.md#pay-per-event ## What's an Apify Actor? An Actor is a serverless cloud program that runs on the Apify platform. It has two run modes. 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. Apify vocabulary and the platform model are defined once, in the agent quickstart at https://apify.com/agents.md. ## 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. Do not guess an integration path. Every one of them is in the agent quickstart at https://apify.com/agents.md: the Apify MCP server, Agent Skills with the Apify CLI, the JavaScript and Python clients, the REST API, and the account-free path for an agent with no human to sign in. It also carries the rule on stating cost before the first paid run. For examples already wired to this Actor's own input schema, see the [API](#api) section below. Each client library has reference documentation the quickstart does not restate: [JavaScript/TypeScript](https://docs.apify.com/api/client/js/docs.md) (`npm install apify-client`) and [Python](https://docs.apify.com/api/client/python/docs.md) (`pip install apify-client`). # README Send the results of any Apify Actor to a **Google Sheet**, on every run, without connecting your Google account. Share your sheet with one email address, paste the sheet link, and choose **append**, **replace**, or **upsert by key**. Built for scheduled and webhook-triggered runs: it retries Google's rate limits, removes duplicates, adds new columns on its own, and never fails just because a scrape came back empty. ### What does Dataset to Google Sheets Sync do? It reads an Apify [dataset](https://docs.apify.com/platform/storage/dataset) (the output of any Actor run) and writes the items as rows into one tab of your Google Sheet. - **Append**: add the new rows under the existing ones. - **Replace**: clear the tab and write everything fresh. - **Upsert**: match rows on a key column such as `url` or `id`, update the ones that exist, add the rest. Optionally delete sheet rows that are no longer in the data, so the tab mirrors the dataset. Nested fields become dotted columns (`seller.name`), arrays are stored as JSON text in one cell, and new fields are added as new columns on the right. Columns you keep by hand (a Notes or Status column, say) are never touched by an upsert. ### Why use it instead of the free Google Sheets integration? The free integration connects through a Google login that expires and then silently breaks scheduled runs, and it stops with an error when a run produces no items. This Actor uses a **service account** instead. Nothing expires, nobody has to log in, and it works the same from a schedule, a webhook, the API, or an AI agent. - No Google login, no consent screen, no token that dies after a week. - Google rate limits (HTTP 429) and outages are retried with back-off instead of failing the run. - Zero items is a success, not a failure. Nothing is written, an existing header stays, and the run reports `rowsWritten: 0`. An empty scrape never deletes rows, even with `deleteMissing` on. - Upsert by one or more key columns, with duplicate removal (last occurrence wins). - Big datasets are written in chunks under Google's request size limits; the run summary tells you exactly what happened. ### How to send Apify data to Google Sheets 1. **Share your sheet.** Open your Google Sheet, click **Share** (top right), paste this email and give it **Editor** access: `apify-sync@apify-sheets-sync-509921.iam.gserviceaccount.com` (Or use your own service account: see "Bring your own Google service account" below.) 2. **Paste the sheet link** into the **Google Sheet link** field. Copy it straight from your browser's address bar. 3. **Pick the data.** Choose the **Dataset** to send, or leave it empty when you run this Actor from an integration (see step 5). 4. **Pick a mode.** Append for logs, replace for snapshots, upsert for lists that change (set **Key fields**, e.g. `url`). 5. **Automate it.** On the Actor that produces your data, open the **Integrations** tab, add this Actor, and it will run after every successful run. The **Dataset** field is filled in with `{{resource.defaultDatasetId}}`, which means "the dataset of the run that just finished"; leave it that way. Or create a schedule with a fixed dataset. ### Input | Field | What it does | | -------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `spreadsheetUrl` | The sheet's URL (or bare id). A `#gid=` in the link selects that tab. | | `sheetName` | Tab to write. Empty = first tab. Created if missing (see `createSheet`). | | `mode` | `append` (default), `replace`, or `upsert`. | | `keyFields` | Column(s) that identify a row, for upsert and deduplication. Nested: `seller.id`. | | `deleteMissing` | Upsert only: remove sheet rows whose key is absent from the data. Skipped when the source is empty or cut off by `limit`. | | `dedupe` | Remove duplicates before writing. Default on for upsert, off otherwise. | | `datasetId` | Dataset to send. Filled in automatically when triggered by an integration or webhook. | | `rows` | Rows to write instead of a dataset (JSON array). Good for testing and API calls. | | `columns` | Write only these data columns. Their order applies to new tabs and replace mode; an existing tab keeps its layout. | | `limit`, `offset` | Read at most `limit` items, skipping `offset`. | | `flattenDepth` | Nested objects become dotted columns to this depth (default 2); deeper values and arrays become JSON text. | | `maxColumns` | Safety cap on the number of columns (default 200). | | `parseValues` | Off: values are written as-is. On: Google interprets them as typed input (dates, formulas). | | `createSheet` | Create the named tab when missing (default on). | | `serviceAccountJson` | Your own service account key (stored encrypted). | ### Output The Google Sheet is the real output. The Actor's own dataset holds one summary row per run so you can monitor syncs: ```json { "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1BxiM.../edit#gid=0", "spreadsheetTitle": "Competitor prices", "sheetName": "Sheet1", "mode": "upsert", "rowsRead": 1240, "rowsDeduplicated": 12, "rowsWritten": 1228, "rowsAppended": 31, "rowsUpdated": 1197, "rowsDeleted": 0, "rowsSkipped": 0, "columns": 14, "columnsAdded": ["rating"], "columnsDropped": [], "apiReads": 2, "apiWrites": 3, "apiRetries": 0, "stoppedEarly": false, "warnings": [], "durationSeconds": 6.4, "finishedAt": "2026-09-27T20:11:04.000Z" } ``` ### Pricing Pay per event: a small fee per sync plus a fraction of a cent per row written or updated. Rows that are not written (for example when your maximum cost per run is reached) are never charged; the run summary and status say when it stopped early. Exact prices are on this page's pricing box. ### Tips - **Upsert keys**: use a column that never changes for the same item, such as `url`, `id`, or `sku`. Two key fields work too (`["store", "sku"]`). - **Dates**: keep `parseValues` off if you want text to stay text. Turn it on to let Google turn ISO dates into real dates. - **Big sheets**: a Google Sheet holds at most 10 million cells. The Actor checks before writing and stops with a clear message instead of a Google error. Use `replace` mode or a new sheet for rolling snapshots. - **Column order**: existing columns keep their positions; new fields are added on the right. `columns` fixes the order for a new tab or in replace mode. - **Replace mode** rewrites the tab in place from row 1 and trims the grid to the data, so formulas or charts in other tabs that point at it keep pointing at the same rows. Anything you typed into that tab is gone after a replace; keep notes in another tab. - **One sync at a time per tab**: two runs writing the same tab at the same moment (a webhook that fired twice, a schedule overlapping a manual run) can duplicate rows in upsert mode. Let one finish first. - **Big syncs**: over about 100,000 rows, give the run 2 GB of memory in the run options. - **Heavy use**: the shared service account has one Google write quota for everyone. If you sync many times a minute, bring your own service account (below) for a dedicated quota. ### One Apify account per sheet The first Apify account that syncs a sheet becomes its only writer through this Actor: the Actor leaves an invisible marker in the spreadsheet (Google "developer metadata" that only this Actor's Google project can read), and runs from any other Apify account are refused. So even if someone learns your sheet's link, they cannot write to it through this Actor. Sync your sheet once right after sharing it, so the link is yours. A copy of the sheet (File > Make a copy) starts unlinked. If you move to a new Apify account, either use a copy, or open an issue in the Issues tab with the sheet link and the link will be reset. The lock does not apply to runs that bring their own service account key, and it does not limit people you share the sheet with in Google. ### Bring your own Google service account 1. In [Google Cloud Console](https://console.cloud.google.com/), create a project and enable the **Google Sheets API**. 2. **IAM & Admin > Service Accounts > Create service account**. No roles needed. 3. Open it, **Keys > Add key > Create new key > JSON**. Download the file. 4. Paste the file's contents into **Your own Google service account key**. Share your sheet with that account's `client_email`. ### Privacy The Actor reads the spreadsheet's title, tab names and sizes, the link marker, and the tab it writes (the header row in append and replace mode, the whole tab in upsert mode). It keeps nothing after the run: no copy of your sheet, no credentials, no rows. Its run summary (in your own Apify account) stores the spreadsheet id, link and title, tab name, mode, counts, names of columns added or dropped, warnings and timing, never cell values. Run logs show sheet, tab and column names, never cell values. It uses the Google Sheets API scope only and cannot list or open anything in your Drive that you did not share with it. Full policy: [Privacy Policy](https://docs.google.com/document/d/e/2PACX-1vS2NThuSLZ_Y9GAYgNsEUKQ0stxcPMVIHX1S3agXxCsuHh6cIdcJa_oMpUCMeQ2t5AA6DeOwsMY1v9n/pub) ### FAQ **The run says "Share the sheet with ..."** Google refused access. Open the sheet, click Share, add the email shown as an Editor, and run again. **Numbers came in as text.** Numbers from the dataset stay numbers. Only text that looks like a number stays text unless `parseValues` is on. **The run says the dataset could not be opened (insufficient permissions).** This Actor runs with limited permissions, so it can only read the dataset named in the **Dataset** field. In an integration, set that field to `{{resource.defaultDatasetId}}`. **Upsert keeps adding the same rows.** With `parseValues` on, Google may reformat key values (leading zeros, dates), so they no longer match. Use plain ids as keys or turn `parseValues` off. **Can it write to Excel or CSV?** Every Apify dataset already exports to CSV and Excel from the dataset page. This Actor is for live sheets that update on a schedule. **"already linked to a different Apify account".** Another Apify account (or an organization account) synced this sheet first. Use a copy of the sheet (File > Make a copy, share the copy with the service account), paste your own service account key, or open an issue with the sheet link to have it reset. **Something else?** Open an issue in the Issues tab. # Actor input Schema ## `spreadsheetUrl` (type: `string`): Paste the sheet's URL from your browser (or just its id). First share the sheet with the service account email shown in the README as an Editor. A #gid=... in the link selects that tab unless "Tab name" is set. ## `sheetName` (type: `string`): Which tab (worksheet) to write. Leave empty for the first tab. A missing tab is created when "Create tab if missing" is on. ## `mode` (type: `string`): append = add rows under the existing ones. replace = clear the tab and write everything fresh. upsert = update rows whose key columns match, append the rest (needs Key fields). ## `keyFields` (type: `array`): Column(s) that identify a row, e.g. url or id. Required for upsert; also used for deduplication. Nested fields use dots: seller.id. ## `deleteMissing` (type: `boolean`): Upsert only: remove sheet rows whose key is not in this dataset, so the tab mirrors the dataset exactly. Off by default. Skipped automatically when the source has no items or was cut off by Max items, so one empty scrape cannot wipe the tab. ## `dedupe` (type: `boolean`): Drop repeated rows before writing (by Key fields, or whole-row identical when no keys are set); the last occurrence wins. Defaults to on for upsert, off otherwise. ## `datasetId` (type: `string`): The Apify dataset to send. When this Actor runs from another Actor's Integrations tab this is filled in with the finished run's dataset ({{resource.defaultDatasetId}}). A dataset always wins over the sample Rows below. ## `rows` (type: `array`): Optional JSON array of objects to write instead of a dataset. Handy for testing or for calling this Actor from your own code. ## `columns` (type: `array`): Write only these data columns. On a new tab or in replace mode they appear in this order; an existing tab keeps its layout and new columns are added on the right. Columns your data does not include are never touched. ## `limit` (type: `integer`): Read at most this many dataset items. ## `offset` (type: `integer`): Skip this many dataset items before reading. ## `flattenDepth` (type: `integer`): Nested objects become dotted columns (address.city) down to this depth; anything deeper, and every array, is stored as JSON text in one cell. ## `maxColumns` (type: `integer`): Safety cap on the number of columns written; extra fields are dropped and listed in the run summary. ## `parseValues` (type: `boolean`): Off (default): values are stored exactly as sent (text stays text). On: Google interprets them as if typed, so '2026-01-05' becomes a date and '=SUM(A1)' a formula. ## `createSheet` (type: `boolean`): Create the named tab when the spreadsheet does not have it. ## `resetSheetBinding` (type: `boolean`): Each Google Sheet is linked to the first Apify account that syncs it, so nobody else can write to it through this Actor. The Actor's author runs this to unlink a sheet when a customer moves to a new Apify account. Nothing is written in that run. ## `serviceAccountJson` (type: `string`): Paste the JSON key of your own Google Cloud service account to use your own Google quota instead of the shared one. Stored encrypted. Share the sheet with that account's email instead. ## Actor input object example ```json { "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1hMzcbQkpgNhX9x9hSZp2EAW-yvIbuuhUDt2l745XBKk/edit", "mode": "append", "deleteMissing": false, "rows": [ { "id": 1, "product": "Sample widget", "price": 9.99, "inStock": true, "tags": [ "demo", "sample" ] }, { "id": 2, "product": "Sample gadget", "price": 24.5, "inStock": false, "tags": [ "demo" ] } ], "limit": 250000, "offset": 0, "flattenDepth": 2, "maxColumns": 200, "parseValues": false, "createSheet": true, "resetSheetBinding": false } ``` # Actor output Schema ## `summary` (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 = { "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1hMzcbQkpgNhX9x9hSZp2EAW-yvIbuuhUDt2l745XBKk/edit", "mode": "append", "rows": [ { "id": 1, "product": "Sample widget", "price": 9.99, "inStock": true, "tags": [ "demo", "sample" ] }, { "id": 2, "product": "Sample gadget", "price": 24.5, "inStock": false, "tags": [ "demo" ] } ] }; // Run the Actor and wait for it to finish const run = await client.actor("osel_house/dataset-to-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 = { "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1hMzcbQkpgNhX9x9hSZp2EAW-yvIbuuhUDt2l745XBKk/edit", "mode": "append", "rows": [ { "id": 1, "product": "Sample widget", "price": 9.99, "inStock": True, "tags": [ "demo", "sample", ], }, { "id": 2, "product": "Sample gadget", "price": 24.5, "inStock": False, "tags": ["demo"], }, ], } # Run the Actor and wait for it to finish run = client.actor("osel_house/dataset-to-google-sheets").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 '{ "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1hMzcbQkpgNhX9x9hSZp2EAW-yvIbuuhUDt2l745XBKk/edit", "mode": "append", "rows": [ { "id": 1, "product": "Sample widget", "price": 9.99, "inStock": true, "tags": [ "demo", "sample" ] }, { "id": 2, "product": "Sample gadget", "price": 24.5, "inStock": false, "tags": [ "demo" ] } ] }' | apify call osel_house/dataset-to-google-sheets --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "type": "http", "url": "https://mcp.apify.com/?tools=fetch-actor-details,osel_house/dataset-to-google-sheets" } } } ``` 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 ` 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/qqihOJH9Y5X5uWRwN/builds/RcLlsZiLJeQ1YatnF/openapi.json