# Google Sheets Import & Export (Append, Upsert, Replace) (`pluginlint-tools/google-sheets-sync`) Actor Send any Apify dataset or JSON to Google Sheets (append, upsert by key, replace) or export a sheet to a dataset. Service-account auth with no OAuth popups, automatic header mapping, typed values, batching and quota-aware retries. - **URL**: https://apify.com/pluginlint-tools/google-sheets-sync.md - **Developed by:** [PluginLint Tools](https://apify.com/pluginlint-tools) (community) - **Security:** Limited permissions - **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $0.10 / 1,000 row written to google sheets 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 ## Google Sheets Import & Export – Append, Upsert or Replace rows (Service Account, no OAuth popups) Send any **Apify dataset or JSON to Google Sheets** – append new rows, **upsert (update by key)**, or replace a whole tab – and **export a Google Sheet to an Apify dataset**. Built for scheduled scraper pipelines: it authenticates with a **Google service account** (no OAuth tokens that expire), maps your fields to the existing header row automatically, keeps numbers as numbers, retries Google's rate limits, and when something is wrong it tells you exactly what to fix. > **Beta:** This Actor is new. Its Google Sheets integration has been tested with mocked Google API responses and with dry runs. If something doesn't work, please report it on the Actor's **Issues** tab. You're only billed for rows in batches that were written or read successfully. > Free demo: click **Start** with empty input. The Actor runs a **dry run** on sample data against an in-memory sheet and shows you the exact rows it would write. No Google account needed, nothing is charged. ### What does Google Sheets Import & Export do? | Mode | What happens | |---|---| | **append** | Adds rows under the existing data. With *Key columns* set, rows whose key already exists are skipped, so you can re-run a scraper every hour and only add the new items. | | **upsert** | Updates rows whose *Key columns* match, adds the rest. Only the fields you send are overwritten; other columns, such as your own notes, are kept. Rows that didn't change aren't rewritten (or charged). | | **replace** | Clears the tab and writes the new data. The old content is saved to the run's key-value store first (`BACKUP-`). | | **export** | Reads a tab (or an A1 range) and saves every row to the run's dataset as JSON objects keyed by the header, with real numbers and booleans. Download as CSV, Excel or JSON, or feed another Actor. | #### Why this Actor? The most-used Google Sheets Actor in the Apify Store fails on about half of its runs (Apify Store public run statistics, last 30 days). Its public issues are mostly about authorization, spreadsheet-ID validation, writing to a different tab, the cell limit, and column order. This Actor is designed around those problems: - **Service-account auth.** Paste the JSON key and share the sheet with one e-mail address. There's no browser popup or token store, and nothing expires on a schedule. OAuth refresh tokens are supported too. - **Paste the URL.** It accepts any Google Sheets URL or ID of any length, and picks the tab from `#gid=` in the URL. - **Any tab.** Set *Sheet (tab) name* and the tab is created if it doesn't exist. - **Header auto-mapping.** `product_name` matches the column `Product Name`. Existing columns are never reordered, and new fields become new columns on the right. You can turn this off. - **Types preserved.** RAW mode by default: `12.5` stays a number, `true` a checkbox-style boolean, and `00123` or `+1 555…` stay text. `=formulas` from scraped text are *not* executed (no formula injection), and there are no locale surprises with `1,5`. - **Large datasets.** Data is chunked by rows and by about 2 MB of payload, and the grid grows automatically past 1,000 rows and 26 columns. The Actor checks the 10,000,000-cell spreadsheet limit *before* writing and truncates cells over 50,000 characters (it reports how many). - **Quota-aware.** It paces requests for the 60 writes/min/user quota and retries 429, 5xx and network errors with exponential backoff, honoring Retry-After. - **Clear errors.** For example, `Permission denied … share it with "my-bot@project.iam.gserviceaccount.com" as Editor`, or `Sheets API is not enabled – open this link and click Enable`. ### Setup in 3 steps (about 5 minutes, once) 1. **Create a service account key.** In [Google Cloud Console](https://console.cloud.google.com/), open or create a project. Go to **APIs & Services → Library → Google Sheets API → Enable**. Then go to **IAM & Admin → Service Accounts → Create service account** (no roles needed), open it, and choose **Keys → Add key → Create new key → JSON**. A `.json` file downloads. 2. **Share your sheet** with the service account's e-mail (the `client_email` in the JSON, e.g. `sheets-bot@my-project.iam.gserviceaccount.com`). Click **Share** in the sheet, paste the address, choose **Editor** (Viewer is enough for export), untick *Notify*, and click **Share**. 3. **Run the Actor.** Paste the whole JSON file into *Service account JSON key* (stored encrypted as a secret input and never logged), paste the sheet URL, and pick the source: a *Dataset ID*, *Data (JSON)*, or attach this Actor as an **integration** to your scraper so every finished run is pushed to the sheet. > Prefer OAuth? Fill in *OAuth client ID*, *OAuth client secret* and *OAuth refresh token* (scope `https://www.googleapis.com/auth/spreadsheets`, for example from the Google OAuth Playground with your own client). Note that Google expires refresh tokens after 7 days while your consent screen is in *Testing* mode. ### Input examples **Append scraper results, skipping URLs already in the sheet** ```json { "mode": "append", "spreadsheetId": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0", "sheetName": "Leads", "datasetId": "YOUR_DATASET_ID", "keyColumns": ["url"], "serviceAccountJson": "{ \"type\": \"service_account\", ... }" } ``` **Keep a price list up to date (upsert by SKU)** ```json { "mode": "upsert", "spreadsheetId": "1AbC...", "keyColumns": ["sku"], "fields": ["sku", "title", "price", "inStock"], "datasetId": "YOUR_DATASET_ID", "serviceAccountJson": "..." } ``` **Replace a tab with fresh data, nested objects stored as JSON text** ```json { "mode": "replace", "spreadsheetId": "1AbC...", "sheetName": "Daily", "nestedHandling": "json", "data": [{ "name": "A", "meta": { "x": 1 } }], "serviceAccountJson": "..." } ``` **Export a sheet to a dataset** ```json { "mode": "export", "spreadsheetId": "1AbC...", "sheetName": "Input URLs", "range": "A1:D500", "serviceAccountJson": "..." } ``` **Try it without credentials (dry run)** ```json { "dryRun": true, "mode": "upsert", "keyColumns": ["sku"], "dryRunExistingValues": [["SKU", "Price"], ["a", 1]], "data": [{ "sku": "a", "price": 5 }, { "sku": "b", "price": 7 }] } ``` #### Use it from code or as an integration - **Integration:** on your scraper's page, open **Integrations → Connect Actor → Google Sheets Import & Export**. The source run's default dataset is used automatically (`payload.resource.defaultDatasetId`). - **API:** `POST https://api.apify.com/v2/acts/~google-sheets-sync/runs?token=…` with the input JSON. ### Output - **Write modes:** one summary record in the dataset and in the `OUTPUT` key-value record. It contains the spreadsheet URL, tab, rows written, appended, updated, unchanged and skipped, new columns, truncated cells, and API retries. - **Export mode:** one dataset item per sheet row, e.g. `{"sku": "a", "price": 12.5, "inStock": true}`. Turn on *Unflatten* to rebuild `seller/name` columns into nested objects. ### Pricing This Actor uses Apify's **pay-per-event** pricing. You pay for the rows the Actor actually moves: | Event | What it is | Price | |---|---|---| | `rows-written` | One row successfully written to your Google Sheet (appended, updated or replaced) | $0.0001 per row ($0.10 per 1,000 rows) | | `rows-read` | One row successfully exported from your Google Sheet to the dataset | $0.00005 per row ($0.05 per 1,000 rows) | - **You pay only for rows written or read successfully.** Rows are charged after each batch is accepted by the Google Sheets API (or, for export, saved to the dataset). If a batch fails, its rows aren't charged. - **Free:** rows skipped as duplicates, unchanged rows in upsert mode, and dry runs (including the empty-input demo) don't trigger `rows-written` or `rows-read`. - **Platform usage is included.** With pay-per-event, the Actor's developer pays for the platform compute the run uses; you pay for the events above. Any other events Apify adds by default, such as its standard Actor start event, are listed on the Actor's **Pricing** tab. That tab always shows the current prices. - **Spending limit:** the Actor stops cleanly at your run's **Max cost per run** limit and reports how many rows were left out. ### FAQ **Is my service account key safe?** It's a *secret* input, so Apify stores it encrypted, and the Actor never logs it. Use a dedicated service account that only has access to the sheets you share with it, and delete the key in Google Cloud to revoke access at any time. **Do I need a paid Google account?** No. A free Google Cloud project with the Sheets API enabled is enough. Google doesn't charge for the Sheets API. **How many rows can I write?** Up to Google's limit of 10,000,000 cells per spreadsheet (rows × columns, all tabs combined). The Actor checks this before writing. **Will it mess up my column order or formulas in other columns?** No. Existing columns stay where they are. In upsert mode, only the fields you send are overwritten. **Why are my numbers text, or my dates not dates?** In RAW mode, values keep their JSON type. If your scraper outputs `"12.5"` as a string, it stays text. Switch *Value handling* to `USER_ENTERED` to let Google parse dates and numbers in strings, and to run formulas. **Can it read public sheets without credentials?** Not in this version. Export always uses your service account or OAuth credentials. Share the sheet with the service-account e-mail as Viewer. **Can it write to Excel/.xlsx files in Drive?** No. Open the file and use **File → Save as Google Sheets**, then use the new URL. ### Troubleshooting | Message | Fix | |---|---| | `Permission denied … not shared with "…@….iam.gserviceaccount.com"` | Share the sheet with that exact e-mail as **Editor** (step 2). In Google Workspace, your admin may block sharing outside the domain. Ask them, or use OAuth. | | `Google Sheets API is not enabled in your project` | Open the link in the message, click **Enable**, wait a minute. | | `Spreadsheet not found` | Copy the URL again from the address bar. For .xlsx files, use *Save as Google Sheets*. | | `invalid_grant` (OAuth) | The refresh token expired or was revoked (7-day expiry in Testing mode). Create a new one or switch to a service account. | | `invalid_grant` (service account) | The key was deleted or rotated. Create a new JSON key. | | `Tab "X" not found` (export) | The message lists the available tabs. Names must match (case-insensitive). | | `10,000,000 cells` | Use *Fields to include* or *Limit*, delete empty columns or tabs, or write to a new spreadsheet. | | Quota (429) still failing after retries | Increase *Delay between batches*, run less often, or use one service account per workflow. | ### Limitations (honest list) - Writes values only. It doesn't change formatting, charts, filters or data validation. - Upsert reads the whole tab to find keys, so very large tabs (millions of cells) take longer and use more memory. Plain append on very large sheets uses Google's append API and reads only the header. - One tab per run. Use several tasks for several tabs. ### Local development ```bash npm install npm test # unit tests with a mocked googleapis client + end-to-end dry runs node src/main.js --dry-run # or: apify run (input in storage/key_value_stores/default/INPUT.json) ``` # Actor input Schema ## `mode` (type: `string`): append – add rows below existing data (optionally skip rows whose key already exists).
upsert – update rows that match Key columns, append the rest.
replace – clear the tab and write the data (old content is backed up to the key-value store).
export – read the tab and save every row to this run's dataset. ## `spreadsheetId` (type: `string`): Paste the full URL from your browser (e.g. https://docs.google.com/spreadsheets/d/1AbCdEf.../edit#gid=0) or just the ID. If the URL contains #gid=..., that tab is used unless you set Sheet (tab) name. ## `sheetName` (type: `string`): Name of the tab to write to / read from. Empty = the tab from the URL's gid, otherwise the first tab. For writes, a missing tab is created automatically. ## `serviceAccountJson` (type: `string`): Paste the whole JSON key file of a Google Cloud service account (Google Cloud Console → IAM & Admin → Service Accounts → Keys → Add key → JSON). Then share your sheet with the service account's e-mail (the client_email in the JSON) as Editor. Stored encrypted; never logged. No expiring tokens, works for scheduled runs. ## `oauthClientId` (type: `string`): Only if you prefer OAuth instead of a service account: client ID of your own Google OAuth client (type Desktop or Web). ## `oauthClientSecret` (type: `string`): Client secret of the same OAuth client. ## `oauthRefreshToken` (type: `string`): Refresh token with the https://www.googleapis.com/auth/spreadsheets scope (e.g. from the Google OAuth Playground using your own client). Note: if your OAuth consent screen is in 'Testing' mode, Google expires refresh tokens after 7 days. ## `datasetId` (type: `string`): ID of the Apify dataset to send to the sheet, e.g. the default dataset of your scraper run. When this actor is attached as an integration to another actor, the source run's dataset is used automatically. ## `data` (type: `array`): Alternatively, paste an array of objects to write. Used instead of Dataset ID when not empty. Max ~9 MB (Apify input limit). ## `limit` (type: `integer`): Maximum number of items to take from the dataset (or rows to export). Empty = all. ## `offset` (type: `integer`): Number of items to skip from the start of the dataset (or of the exported rows). ## `fields` (type: `array`): Only these top-level fields are written (applied before flattening). Empty = all fields. ## `omitFields` (type: `array`): Top-level fields to leave out, e.g. large HTML fields. ## `keyColumns` (type: `array`): Column names that identify a row, e.g. url or sku. Required for upsert. In append mode, rows whose key already exists in the sheet are skipped (dedupe). Duplicate keys inside the input are merged. ## `autoMapHeaders` (type: `boolean`): Match data fields to existing sheet columns even when spelling differs in case, spaces, underscores, dots or slashes ("Product Name" ⇄ product_name). Existing columns are never reordered. ## `addNewColumns` (type: `boolean`): Add data fields that have no column yet as new columns on the right. If off, such fields are skipped (and listed in the log). ## `columnsOrder` (type: `array`): Fields listed here come first (in this order) when columns are created. Existing sheet columns keep their position. ## `nestedHandling` (type: `string`): flatten – {seller:{name}} becomes column seller/name, arrays become tags/0, tags/1 (same as Apify CSV export).
json – keep one column per top-level field and store nested values as JSON text. ## `flattenSeparator` (type: `string`): Separator for flattened column names. ## `valueInputOption` (type: `string`): RAW (default) – exact types: numbers stay numbers, booleans stay TRUE/FALSE, text stays text (leading zeros like 00123 and '+1 555…' are preserved, '=' formulas are NOT executed, no locale surprises with '1,5').
USER_ENTERED – Google parses values as if typed by a user (formulas run, '2024-01-31' becomes a date). ## `createSheetIfMissing` (type: `boolean`): Create the tab named in Sheet (tab) name if it doesn't exist. ## `createBackup` (type: `boolean`): In replace mode, save the old tab content to this run's key-value store (record BACKUP-) before clearing it. ## `range` (type: `string`): Optional A1 range inside the tab for export, e.g. A1:F500. First row of the range is used as the header. Empty = whole tab. ## `valueRenderOption` (type: `string`): UNFORMATTED_VALUE – real numbers/booleans (12.5, true). FORMATTED_VALUE – exactly what you see in the sheet ('$12.50'). FORMULA – formulas as text. ## `unflattenOutput` (type: `boolean`): Turn columns like seller/name back into nested objects in the dataset. ## `skipEmptyRows` (type: `boolean`): Ignore completely empty rows when exporting. ## `batchSize` (type: `integer`): Rows sent per Google API request (payloads are also capped at ~2 MB automatically). ## `delayBetweenBatchesMs` (type: `integer`): Pause between write requests to stay under Google's 60 writes/minute/user quota. Rate-limit errors (429) are retried with exponential backoff anyway. ## `maxRetries` (type: `integer`): Retries for quota (429) and transient (5xx, network) errors, with exponential backoff up to 64 s. ## `dryRun` (type: `boolean`): Run everything against an in-memory spreadsheet: shows exactly which header/rows would be written, without credentials and without any charge. Runs with no spreadsheet and no credentials are dry runs automatically. ## `dryRunExistingValues` (type: `array`): Optional rows (array of arrays, first row = header) to pre-fill the in-memory sheet with, to test upsert/append mapping. ## Actor input object example ```json { "mode": "append", "data": [ { "url": "https://example.com/p/1", "title": "Blue mug", "price": 12.5, "inStock": true, "sku": "00123" }, { "url": "https://example.com/p/2", "title": "Red mug", "price": 9, "inStock": false, "sku": "00456" } ], "offset": 0, "autoMapHeaders": true, "addNewColumns": true, "nestedHandling": "flatten", "flattenSeparator": "/", "valueInputOption": "RAW", "createSheetIfMissing": true, "createBackup": true, "valueRenderOption": "UNFORMATTED_VALUE", "unflattenOutput": false, "skipEmptyRows": true, "batchSize": 5000, "delayBetweenBatchesMs": 1000, "maxRetries": 6, "dryRun": false } ``` # Actor output Schema ## `results` (type: `string`): Default dataset items: a run summary for write modes, or the exported sheet rows for export mode. ## `summary` (type: `string`): JSON summary of the run (rows written/updated/skipped, retries, duration) or an error object, stored in the default key-value store under OUTPUT. # 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 = { "mode": "append", "data": [ { "url": "https://example.com/p/1", "title": "Blue mug", "price": 12.5, "inStock": true, "sku": "00123" }, { "url": "https://example.com/p/2", "title": "Red mug", "price": 9, "inStock": false, "sku": "00456" } ] }; // Run the Actor and wait for it to finish const run = await client.actor("pluginlint-tools/google-sheets-sync").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 = { "mode": "append", "data": [ { "url": "https://example.com/p/1", "title": "Blue mug", "price": 12.5, "inStock": True, "sku": "00123", }, { "url": "https://example.com/p/2", "title": "Red mug", "price": 9, "inStock": False, "sku": "00456", }, ], } # Run the Actor and wait for it to finish run = client.actor("pluginlint-tools/google-sheets-sync").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 '{ "mode": "append", "data": [ { "url": "https://example.com/p/1", "title": "Blue mug", "price": 12.5, "inStock": true, "sku": "00123" }, { "url": "https://example.com/p/2", "title": "Red mug", "price": 9, "inStock": false, "sku": "00456" } ] }' | apify call pluginlint-tools/google-sheets-sync --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "type": "http", "url": "https://mcp.apify.com/?tools=fetch-actor-details,pluginlint-tools/google-sheets-sync" } } } ``` 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/6kHfs7uWbZTw0vcKc/builds/Qbkf4anUXKCpeCI57/openapi.json