# Google Sheets Import & Export — Append, Upsert, Read (`saliga/google-sheets-import-export`) Actor

Write Apify datasets or JSON to Google Sheets: append, replace or upsert rows by key, skip duplicates, add new columns. Or read a sheet into a dataset. Service account or Apps Script access that does not expire. $0.30 per 1,000 rows.

- **URL**: https://apify.com/saliga/google-sheets-import-export.md
- **Developed by:** [Salih Küçükkayış](https://apify.com/saliga) (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.30 / 1,000 rows

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 **Apify datasets or JSON rows to Google Sheets**, or read a sheet into a dataset. **Append** rows below the existing ones, **replace** the whole tab, or **upsert**: update rows whose key (URL, ID…) is already in the sheet and add the new ones. **Skip duplicates**, add new columns on the right without moving the old ones, choose the column order. Use it on its own, from an AI agent, or as the last step after any scraper.

Access that **does not expire**: a Google service account, or a small Apps Script in your spreadsheet (no Google Cloud project). Public sheets can be read with no setup at all.

**$0.30 per 1,000 rows** written or read. Skipped duplicates, the header row and the run summary are free. No subscription.

### Quick start

Press **Start** with the example input: it reads Google's public example sheet (a small class list) into the dataset, no setup needed.

To write, pick one way of giving access (below), then for example:

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit",
  "sheetName": "Leads",
  "mode": "upsert",
  "keyFields": ["url"],
  "datasetId": "DATASET_ID_OF_YOUR_SCRAPER_RUN",
  "serviceAccountKey": "{ \"type\": \"service_account\", ... }"
}
```

Rows already in the tab with the same `url` are updated in place, new URLs are added at the end, and the tab `Leads` is created if it does not exist.

### Modes

| Mode | What happens | Charged rows |
|---|---|---|
| `append` | Rows go below the last non-empty row. New columns are added on the right; existing columns stay where they are. With `dedupeFields` (or `dedupeWholeRow`), rows already in the tab or earlier in the data are skipped. | rows written |
| `replace` | The tab is cleared, then the header and all rows are written. `keepSheetColumnOrder` keeps the current columns in their place. | rows written |
| `upsert` | Rows whose `keyFields` value is already in the tab replace the values of that row (columns that are not in the data keep their value); other rows are added at the end. | rows updated + added |
| `read` | The tab goes to the dataset, the first row being the field names. Empty rows are skipped. | rows read |

Nested fields become columns like `address/city` and `tags/0`, as in Apify's CSV export (turn off `flatten` to write them as JSON text). Values are written as they are (`valueInput: raw`); choose `user_entered` to let Google parse formulas, dates and numbers like "1,234".

### Give access to the sheet

#### Option 1: service account (best for large or scheduled jobs)

1. In [Google Cloud console](https://console.cloud.google.com/), create a project (or pick one), open **APIs & Services → Library**, search **Google Sheets API** and press **Enable**.
2. **IAM & Admin → Service accounts → Create service account**, any name, no roles needed.
3. Open it, **Keys → Add key → Create new key → JSON**. A file downloads.
4. Paste the whole content of that file into **Service account key**. It is stored encrypted.
5. In your spreadsheet press **Share** and add the service account's e-mail (the `client_email` in the file, ending in `iam.gserviceaccount.com`) as **Editor**.

The key works until you delete it; there is no sign-in that expires.

#### Option 2: Apps Script web app (no Google Cloud project)

1. In your spreadsheet: **Extensions → Apps Script**. Delete what is there and paste the script below.
2. Change `TOKEN` to a password of your own and save.
3. **Deploy → New deployment → Select type: Web app**. Execute as: **Me**. Who has access: **Anyone**. Press **Deploy** and allow access when Google asks.
4. Copy the **Web app URL** (ends in `/exec`) into **Apps Script web app URL**, and your password into **Apps Script token**.

The script can only touch the spreadsheet it lives in, and only with the token. After changing the script, deploy a new version (Deploy → Manage deployments → Edit → New version).

```javascript
// Apify Google Sheets Import & Export: paste this into Extensions → Apps Script of your spreadsheet,
// set TOKEN to a password of your own, then Deploy → New deployment → Web app
// (Execute as: Me, Who has access: Anyone) and copy the /exec URL into the Actor input.
const TOKEN = 'change-me';

function doPost(e) {
  try {
    const req = JSON.parse(e.postData.contents);
    if (!TOKEN || TOKEN === 'change-me') return out({ error: 'Set TOKEN in the script to a password of your own and deploy a new version.' });
    if (req.token !== TOKEN) return out({ error: 'Wrong token: "appsScriptToken" in the Actor input must match TOKEN in the script.' });
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    let sh = null;
    if (req.sheet) sh = ss.getSheetByName(req.sheet);
    if (!sh && req.gid !== null && req.gid !== undefined) sh = ss.getSheets().filter(function (s) { return s.getSheetId() === req.gid; })[0] || null;
    if (!sh && req.sheet) {
      if (!req.create) return out({ error: 'The spreadsheet has no tab "' + req.sheet + '".' });
      sh = ss.insertSheet(req.sheet);
    }
    if (!sh) sh = ss.getSheets()[0];
    if (req.action === 'open') return out({ sheet: sh.getName() });
    if (req.action === 'read') {
      const last = sh.getLastRow(), cols = sh.getLastColumn();
      return out({ sheet: sh.getName(), values: last && cols ? sh.getRange(1, 1, last, cols).getValues() : [] });
    }
    if (req.action === 'clear') { sh.clearContents(); return out({ ok: true }); }
    if (req.action === 'write') {
      const v = req.values || [];
      if (!v.length) return out({ ok: true, rows: 0 });
      const rows = req.row + v.length - 1, cols = v[0].length;
      if (rows > sh.getMaxRows()) sh.insertRowsAfter(sh.getMaxRows(), rows - sh.getMaxRows());
      if (cols > sh.getMaxColumns()) sh.insertColumnsAfter(sh.getMaxColumns(), cols - sh.getMaxColumns());
      const range = sh.getRange(req.row, 1, v.length, cols);
      // raw: text that looks like a formula is written as text, as the Sheets API does with RAW.
      const vals = req.raw ? v.map(function (r) { return r.map(function (x) { return typeof x === 'string' && /^[=+\-@]/.test(x) ? "'" + x : x; }); }) : v;
      range.setValues(vals);
      return out({ ok: true, rows: v.length });
    }
    return out({ error: 'Unknown action ' + req.action });
  } catch (err) {
    return out({ error: String(err && err.message || err) });
  }
}

function out(o) {
  return ContentService.createTextOutput(JSON.stringify(o)).setMimeType(ContentService.MimeType.JSON);
}
```

Apps Script runs at most 6 minutes per call; the Actor sends 5,000 rows per call, so very large tabs are better with a service account.

#### Option 3: public sheet, read only

Share the sheet as **Anyone with the link can view** and run in `read` mode. Values come back as text.

### Use it after a scraper

In the scraper's Actor page: **Integrations → Connect Actor or Task → Google Sheets Import & Export**, and map the default dataset ID of the finished run to **Dataset to write** (`datasetId`). Use `upsert` with `keyFields` (for example `url`) for a sheet that is updated every day without duplicates.

From the API or an AI agent, send rows directly in `rawData`.

### Switching from lukaskrivka/google-sheets

Paste the input you use with **Google Sheets Import & Export** (`lukaskrivka/google-sheets`) and add a service account key or the Apps Script URL; the rest is read like this:

| lukaskrivka/google-sheets field | Here |
|---|---|
| `spreadsheetId`, `sheetId.…` | `spreadsheet` |
| `mode`: `append`, `replace`, `read` | the same modes; `modify` → use `upsert` with `keyFields`; `load backup` → `replace` with the backup dataset |
| `datasetId`, `rawData`, `limit`, `offset` | the same |
| `deduplicateByField` | `dedupeFields` |
| `deduplicateByEquality` | `dedupeWholeRow` |
| `range` (e.g. `Sheet1!A1:D`) | the tab name is used (`sheetName`); rows always start in column A |
| `columnsOrder`, `keepSheetColumnOrder` | the same |
| `publicSpreadsheet` | the same (read only) |
| `oAuthAccount.…`, `googleCredentials` (OAuth client) | not used: give a service account key or the Apps Script URL |
| `transformFunction` | not supported: transform the data first, for example with [Dataset Merge & Dedup Datasets](https://apify.com/saliga/dataset-merge-dedupe) |

### Output

Write modes save a summary in the key-value store record `OUTPUT`:

```json
{
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/…",
  "sheet": "Leads",
  "mode": "upsert",
  "access": "service account",
  "rowsWritten": 120,
  "rowsUpdated": 85,
  "rowsAdded": 35,
  "duplicatesSkipped": 0,
  "columnsAdded": ["price"],
  "columns": 14,
  "shortenedCells": 0
}
```

Read mode writes the rows of the tab to the dataset.

### What it costs

**$0.30 per 1,000 rows** written to the sheet or read from it (plus a tiny $0.00005 start fee per run). Skipped duplicates, the header row and the summary are free. Before writing, the run checks that the rows fit within your **Maximum cost per run**; if not, it stops without writing and says how many rows would fit.

### Limits and good to know

- A Google spreadsheet holds at most 10 million cells; the run stops before writing if the data would not fit.
- A cell holds at most 50,000 characters; longer values are shortened (the summary counts them).
- Upsert and append read the whole tab first, so they suit tabs up to a few hundred thousand rows.
- Key values are compared as text: `42` in the data matches `42` in the sheet, trailing spaces ignored. Rows with an empty key are always added.
- Rows always start in column A; the first row of the tab is the header.

### FAQ

**Why not sign in with my Google account?** A sign-in token expires or is revoked, and the run fails until you sign in again. A service account key or the Apps Script keeps working for scheduled runs.

**Can I write to several tabs?** One tab per run. Run it once per tab, or use separate tasks.

**Does it change formatting?** No. Only cell values are written; formatting, filters and other tabs stay as they are. `replace` clears the values of that tab only.

### More from this developer

- [Dataset Merge & Dedup Datasets](https://apify.com/saliga/dataset-merge-dedupe): merge, clean, dedupe and join datasets, CSV and Excel before writing them to a sheet.
- [Bulk HTTP Requests](https://apify.com/saliga/bulk-http-requests): call any API once for every row of a CSV, JSON list or Apify dataset.
- [Bulk File & Image Downloader](https://apify.com/saliga/bulk-file-downloader): download every file or image URL of a list or dataset to a ZIP.

Code examples (curl, Python, Node.js, n8n) for this and the other Actors: [github.com/msalihk/apify-actor-examples](https://github.com/msalihk/apify-actor-examples).

Questions or a missing feature: open an issue on the Actor page.

**Need a custom version?** I also build custom Actors, changes to this one, and n8n or Make integrations around them, for a fixed price. Open an issue on the Actor page starting with "Custom request", or email support@saligasoftware.com, with what you need and an example input.

# Actor input Schema

## `spreadsheet` (type: `string`):

The Google Sheets link (https://docs.google.com/spreadsheets/d/…) or just its ID. When the link points to a tab (#gid=…), that tab is used unless you name one below.

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

Append keeps what is in the tab and adds rows at the end (new columns are added on the right). Replace clears the tab and writes the data. Upsert updates rows whose key is already in the tab and adds the rest. Read copies the tab into this run's dataset. Empty: append when data is given, otherwise read.

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

The tab (sheet) to use, e.g. Leads. Created if it does not exist (except in read mode). Empty: the tab in the link, or the first tab.

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

The Apify dataset to write, e.g. the output of a scraper run. Pick it here so the run may read it. In an integration (run after another Actor), map the default dataset ID of that run here.

## `rawData` (type: `array`):

Rows given directly as a JSON array of objects, instead of or in addition to a dataset. Handy for AI agents and quick tests.

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

Write (or read) at most this many rows. Empty: all.

## `offset` (type: `integer`):

Skip this many rows of the data (or of the tab in read mode) first.

## `keyFields` (type: `array`):

Upsert only: the column(s) that identify a row, e.g. url or id. A row whose key is already in the tab replaces the values of that row; columns not in the data keep their value.

## `dedupeFields` (type: `array`):

Append only: skip rows whose value in these column(s) is already in the tab or earlier in the data. Skipped rows are free.

## `dedupeWholeRow` (type: `boolean`):

Append only: skip a row when all its values equal a row already in the tab or earlier in the data.

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

Put these columns first, in this order. Nested fields use a slash, e.g. address/city.

## `keepOnlyListedColumns` (type: `boolean`):

Write only the columns in "Columns first" (plus columns already in the tab).

## `keepSheetColumnOrder` (type: `boolean`):

Replace only: keep the columns of the current header in their place and add new ones on the right.

## `flatten` (type: `boolean`):

Nested objects and lists become columns like address/city and tags/0, like Apify's CSV export. Off: they are written as JSON text.

## `valueInput` (type: `string`):

Raw writes every value exactly as it is. As if typed lets Google parse "=SUM(A1:A3)", dates and "1,234".

## `serviceAccountKey` (type: `string`):

Option 1: the whole JSON key of a Google service account. Share the spreadsheet with the service account's e-mail (client_email) as Editor. Stored encrypted. See the README for the 3-minute setup.

## `appsScriptUrl` (type: `string`):

Option 2, no Google Cloud project: paste the script from the README into Extensions → Apps Script of the spreadsheet, deploy it as a web app and paste the /exec URL here.

## `appsScriptToken` (type: `string`):

The TOKEN you set in the script, so nobody else can write to your sheet through it.

## `publicSpreadsheet` (type: `boolean`):

Option 3, read mode only: the sheet is shared as "Anyone with the link can view". No setup; values come back as text.

## Actor input object example

```json
{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "mode": "read",
  "offset": 0,
  "dedupeWholeRow": false,
  "keepOnlyListedColumns": false,
  "keepSheetColumnOrder": false,
  "flatten": true,
  "valueInput": "raw",
  "publicSpreadsheet": false
}
```

# Actor output Schema

## `summary` (type: `string`):

Spreadsheet link, tab, mode, rows written, updated, added and skipped as duplicates, new columns.

## `rows` (type: `string`):

The rows of the tab, in read mode.

# 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 = {
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
    "mode": "read"
};

// Run the Actor and wait for it to finish
const run = await client.actor("saliga/google-sheets-import-export").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 = {
    "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
    "mode": "read",
}

# Run the Actor and wait for it to finish
run = client.actor("saliga/google-sheets-import-export").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 '{
  "spreadsheet": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0",
  "mode": "read"
}' |
apify call saliga/google-sheets-import-export --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,saliga/google-sheets-import-export"
        }
    }
}
```

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/afDS17FJ0Vc1l2auM/builds/5csUuj5tTS4NVZfTj/openapi.json