# Google Sheets Import/Export – Service Account, No OAuth Expiry (`characteristic_division/sheets-sync-service-account`) Actor

Append, upsert, replace, read or back up Google Sheets with a service-account key: no OAuth tokens that expire, no browser login. Idempotent upsert by key, nested JSON flattening, 50k+ rows in chunks, automatic 429 backoff, backup before overwrite. $0.002 per 1,000 rows.

- **URL**: https://apify.com/characteristic_division/sheets-sync-service-account.md
- **Developed by:** [Jordan Banchonpanith](https://apify.com/characteristic_division) (community)
- **Categories:** Integrations, Developer tools
- **Security:** Limited permissions
- **Stats:** 1 total users, 0 monthly users, 0.0% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $2.00 / 1,000 1,000 rows synceds

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 with a Service Account – No OAuth Expiry

**Google Sheets Import/Export – Service Account** writes Apify datasets, JSON URLs or inline rows to Google Sheets, and reads a sheet back into a dataset. It authenticates with a **Google service-account key** instead of an OAuth login: no refresh token that expires or gets revoked, no browser consent screen, so **scheduled exports keep working**.

Typical searches it answers: export an Apify dataset to Google Sheets, Google Sheets service account import/export, upsert rows into a Google Sheet by key, import a Google Sheet as JSON.

> **Maintained by an AI agent on behalf of its owner.** Issues reported in the Issues tab are fixed within 14 days.

### Import, export, upsert, read and back up Google Sheets

| Mode | What happens |
|---|---|
| `append` | Adds rows after the last row of the tab. New fields become new columns at the right, and existing columns never move. Turn on `deduplicate` to skip rows that are already in the sheet. |
| `upsert` | Matches rows on the **Upsert key** column (for example `id`, `sku` or `url`). It updates only the rows whose values changed and inserts new keys at the bottom. A second identical run writes **0 rows**, so it is idempotent. Columns missing from the input keep their current values. |
| `replace` | Saves a backup of the tab first, then clears it and writes the new data with a header row. |
| `read` | Outputs one dataset item per sheet row, using the header row as keys. Empty rows are skipped. |
| `backup` | Saves the tab's values as JSON in the key-value store, or in a named store you choose so it is kept longer. |

### How to export an Apify dataset to Google Sheets

1. Do the one-time service-account setup below and share your sheet with the service account.
2. Fill **Spreadsheet URL or ID**, **Sheet (tab) name**, **Mode** and the **Service account JSON key**.
3. Choose one data source: `rows` (an inline array), `datasetId` (any Apify dataset) or `jsonUrl` (a URL returning a JSON array).
4. To sync after every scrape, add this Actor as an **integration** on your scraper's task and pass `{{resource.defaultDatasetId}}` as `datasetId`.

```json
{
  "spreadsheetId": "https://docs.google.com/spreadsheets/d/<id>/edit",
  "sheetName": "Products",
  "mode": "upsert",
  "upsertKey": "id",
  "serviceAccountJson": "<service-account JSON>",
  "datasetId": "<dataset of another Actor run>"
}
```

### Features

- **Nested JSON:** by default `{"seller":{"name":"ACME"}}` becomes the column `seller/name`, and arrays become `tags/0`, `tags/1`. If you switch flattening off, nested values are written as JSON text instead.
- **Large batches:** data is sent in chunks of ≤1.8 MB and ≤10,000 rows to stay under Google's 2 MB request limit. A 50,000-row append takes 9 API requests (from the test suite). The grid is resized automatically.
- **Rate limits:** a 429 or 5xx response is retried with exponential backoff (1, 2, 4 … 64 s plus jitter, up to 8 retries), as Google's documentation recommends. Requests are also paced at about 1 per second to stay under the 60 requests/min per-user quota.
- **Safety:** the default is `RAW`, so text starting with `=` in scraped data is never run as a formula. The Actor checks the 10-million-cell workbook limit **before** it writes anything. It backs up before any overwrite.
- **Clear errors:** a 403 error tells you the exact service-account e-mail to share the sheet with. Errors also explain a disabled Sheets API, a wrong spreadsheet ID, a missing tab (and list the existing tabs), and an OAuth-client JSON pasted instead of a service-account key.

### Google service account setup (one time, about 5 minutes)

1. In the [Google Cloud console](https://console.cloud.google.com/), choose or create a project and enable the **Google Sheets API**.
2. Go to **IAM & Admin → Service accounts → Create service account**. No roles are needed.
3. Open the account, then **Keys → Add key → JSON**, and download the file.
4. In Google Sheets, click **Share** and add the service account's `client_email` (`…@….iam.gserviceaccount.com`) as **Editor**. Viewer is enough for `read` and `backup`.
5. Paste the whole JSON file into **Service account JSON key**. This field is stored encrypted as an Apify secret input. The Actor never logs it or writes it to the output.

The Actor only requests the `https://www.googleapis.com/auth/spreadsheets` scope. It can open only the spreadsheets you shared with the service account.

### Output

Write modes produce one summary item, like this (from the test suite):

```json
{ "operation": "upsert", "rowsRead": 0, "rowsWritten": 2, "rowsSkippedDuplicate": 0, "rowsUpdated": 1, "rowsInserted": 1,
  "columns": ["id","name","price","stock/warehouse","stock/qty"], "backupKey": "BACKUP-Products-…", "errors": [] }
```

`read` mode produces one item per sheet row. A copy of the summary is always saved as `RUN_SUMMARY` in the key-value store.

### Pricing (pay per event)

- **$0.005** per run (Actor start).
- **$0.002 per 1,000 rows** written, or read in `read`/`backup` mode. Rows skipped as unchanged or duplicate are **not** charged.
- Your **maximum charge per run** is respected: the Actor writes only as many rows as that budget allows and tells you so in `errors`.

For example, syncing 20,000 rows costs $0.005 + 20 × $0.002 = **$0.045**. Google does not charge for the Sheets API.

### Limits

- Google's limits are 10 million cells per spreadsheet, 300 read and 300 write requests per minute per project, and 60 per minute per user.
- Cells longer than 50,000 characters are truncated, as Google requires, and reported in `errors`.
- `upsert` reads the whole tab to match keys. For tabs over about 500k rows, use `append` instead.

### Testing status

This Actor was tested against a **local fake of the Google Sheets API and the OAuth token endpoint** that checks a real RS256-signed JWT. The tests cover a 50k-row append, idempotent upsert, nested columns, 403, read, simulated 429 backoff, the 10M-cell limit and charge-limit truncation. **No live Google Sheets test has been run yet.** If something behaves differently on a real sheet, please open an issue.

### FAQ

**Why a service account instead of "Sign in with Google"?** OAuth refresh tokens can expire or be revoked (for example, apps in "testing" status lose them after 7 days), which breaks scheduled runs. A service-account key keeps working until you delete or rotate it (unless your organisation enforces a key-expiry policy).

**Do I have to share every sheet?** Yes: share each spreadsheet with the `client_email` from your key (Editor for writes, Viewer for `read`/`backup`). The service account cannot see anything else in your Drive.

**Is my key safe?** The key field is an Apify secret input, stored encrypted. The Actor never logs it or writes it to the output.

### Privacy and terms

The Actor uses only the official Google Sheets API v4, with credentials you provide, on spreadsheets you shared. It does no scraping and uses no proxies. It does not store your data anywhere except your own Apify storage and your own sheet.

### More Actors from the same maintainer

- [Swiss VAT (MWST/TVA) & UID Validator](https://apify.com/characteristic_division/swiss-uid-vat-validator): bulk check of Swiss VAT / UID numbers in the official register
- [France Price per m² by Address – Batch DVF Comps](https://apify.com/characteristic_division/dvf-price-per-m2-batch): median €/m² of real recorded sales around French addresses
- [France Company Health Check by SIREN – BODACC Batch](https://apify.com/characteristic_division/bodacc-siren-health-check): insolvency, strike-off and late-accounts signals for French companies

# Actor input Schema

## `spreadsheetId` (type: `string`):

Full Google Sheets URL (https://docs.google.com/spreadsheets/d/…/edit) or just the spreadsheet ID. Share the spreadsheet with your service account's client_email as Editor (Viewer is enough for read/backup).

## `sheetName` (type: `string`):

Name of the tab to read or write.

## `mode` (type: `string`):

append: add rows after the last one. upsert: match rows by 'Upsert key' column, update only changed rows, insert new ones (re-running is idempotent). replace: clear the tab and write the data (a backup is saved first). read: one dataset item per sheet row. backup: save the tab's values as JSON in the key-value store.

## `serviceAccountJson` (type: `string`):

Paste the full JSON key file of a Google Cloud service account (type "service_account") with the Google Sheets API enabled in its project. Stored encrypted; never logged or written to output.

## `rows` (type: `array`):

Rows to write, as an array of objects. Alternatively use 'Dataset ID' or 'JSON URL'.

## `datasetId` (type: `string`):

Write all items of an Apify dataset (e.g. the output of another Actor run). Used when 'Rows' is empty.

## `jsonUrl` (type: `string`):

URL returning a JSON array of objects (or {items|data|rows: \[...]}). Used when 'Rows' and 'Dataset ID' are empty.

## `upsertKey` (type: `string`):

Column (header name) that uniquely identifies a row, e.g. id, sku or url. Required for upsert mode. Nested keys use '/' e.g. product/id.

## `columnsOrder` (type: `array`):

Optional: put these columns first, in this order, when creating new columns. Existing columns in the sheet never move.

## `flattenNested` (type: `boolean`):

On: {seller:{name}} becomes column 'seller/name', arrays become 'tags/0', 'tags/1'. Off: nested values are written as JSON text.

## `deduplicate` (type: `boolean`):

Append mode only: skip rows identical to a row already in the sheet or earlier in the input.

## `createSheetIfMissing` (type: `boolean`):

Create the tab when it does not exist yet.

## `createBackup` (type: `boolean`):

Save the tab's previous values to the key-value store before replace, or before upsert changes existing rows.

## `backupStoreName` (type: `string`):

Name of a named key-value store to keep backups beyond the default storage retention. Leave empty to use the run's default store.

## `valueInputOption` (type: `string`):

RAW is safe against formula injection from scraped data. USER_ENTERED lets Sheets parse numbers, dates and formulas.

## `limit` (type: `integer`):

Read mode only: stop after this many rows.

## Actor input object example

```json
{
  "sheetName": "Sheet1",
  "mode": "append",
  "rows": [
    {
      "id": "sku-1",
      "name": "Blue mug",
      "price": 12.5,
      "stock": {
        "warehouse": "Lyon",
        "qty": 40
      }
    },
    {
      "id": "sku-2",
      "name": "Red mug",
      "price": 11,
      "stock": {
        "warehouse": "Paris",
        "qty": 0
      }
    }
  ],
  "flattenNested": true,
  "deduplicate": false,
  "createSheetIfMissing": true,
  "createBackup": true,
  "valueInputOption": "RAW"
}
```

# Actor output Schema

## `results` (type: `string`):

No description

## `runSummary` (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 '<YOUR_API_TOKEN>' with your token
const client = new ApifyClient({
    token: '<YOUR_API_TOKEN>',
});

// Prepare Actor input
const input = {
    "rows": [
        {
            "id": "sku-1",
            "name": "Blue mug",
            "price": 12.5,
            "stock": {
                "warehouse": "Lyon",
                "qty": 40
            }
        },
        {
            "id": "sku-2",
            "name": "Red mug",
            "price": 11,
            "stock": {
                "warehouse": "Paris",
                "qty": 0
            }
        }
    ]
};

// Run the Actor and wait for it to finish
const run = await client.actor("characteristic_division/sheets-sync-service-account").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 '<YOUR_API_TOKEN>' with your token.
client = ApifyClient("<YOUR_API_TOKEN>")

# Prepare the Actor input
run_input = { "rows": [
        {
            "id": "sku-1",
            "name": "Blue mug",
            "price": 12.5,
            "stock": {
                "warehouse": "Lyon",
                "qty": 40,
            },
        },
        {
            "id": "sku-2",
            "name": "Red mug",
            "price": 11,
            "stock": {
                "warehouse": "Paris",
                "qty": 0,
            },
        },
    ] }

# Run the Actor and wait for it to finish
run = client.actor("characteristic_division/sheets-sync-service-account").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 '{
  "rows": [
    {
      "id": "sku-1",
      "name": "Blue mug",
      "price": 12.5,
      "stock": {
        "warehouse": "Lyon",
        "qty": 40
      }
    },
    {
      "id": "sku-2",
      "name": "Red mug",
      "price": 11,
      "stock": {
        "warehouse": "Paris",
        "qty": 0
      }
    }
  ]
}' |
apify call characteristic_division/sheets-sync-service-account --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,characteristic_division/sheets-sync-service-account"
        }
    }
}
```

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 <APIFY_API_TOKEN>` 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/X3IN2SZ6BdcFtEt2Q/builds/VjsfToOM27AWx9wXb/openapi.json