# Google Sheets Export & Sync for Apify Datasets (`ottomath/dataset-to-google-sheets`) Actor

Export an Apify dataset to Google Sheets: append, replace or upsert by key. Service account or Apps Script, works as an integration. $0.80 per 1,000 rows.

- **URL**: https://apify.com/ottomath/dataset-to-google-sheets.md
- **Developed by:** [Ottomath](https://apify.com/ottomath) (community)
- **Stats:** 1 total users, 1 monthly users, 90.5% runs succeeded, 0 bookmarks
- **User rating**: No ratings yet

## Pricing

from $0.80 / 1,000 row writtens

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 Export & Sync for Apify Datasets

Export Apify dataset to Google Sheets: append rows, replace a tab, or upsert by key columns so a scheduled run never creates duplicates. Sign in with a Google service account or a small Apps Script web app. Run it by hand, on a schedule or as an integration.

### What it does

This Actor is a Google Sheets integration for Apify datasets. It turns the items of a dataset into rows and writes them into a tab of your own spreadsheet, so you can sync scraper results to Google Sheets without code.

- **Append to Google Sheets (default):** adds the items below the existing rows.
- **Replace:** clears the tab, then writes the dataset.
- **Upsert Google Sheets:** the `keyColumns` identify a row. Matching rows are updated, the others appended, so each item stays in the sheet once.

Nested data becomes columns with dotted names such as `address.city`. Columns are the union of all fields in order of first appearance, or the list you choose. An existing header is kept: data is aligned to it and new columns are added at the end. Text that starts with `=`, `+`, `-` or `@` is stored as text, so exported data never runs as a formula. Cells beyond Google's 50,000-character limit are cut and counted.

### Use cases

- **Sales and marketing operations:** send each night's lead scrape to your team's sheet. Upsert on `url` and your own columns stay untouched.
- **Price and stock monitoring:** replace one tab after each scheduled run and let other tabs read it.
- **Chaining Actors:** add it as an integration to any scraper.

### Preview without credentials

With no service account key and no Apps Script address, or with `dryRun` ("Preview only") on, the Actor does not contact Google and writes nothing. It reads up to 100 items (with no dataset, a built-in sample of 5 products) and works out the columns, the first 5 rows, the row count, the cells that would be cut and, for upsert, whether the key columns exist.

The result is one free `preview` item, also saved as the `OUTPUT` record. The run ends as Succeeded and its status message starts with "Preview only". A preview does not check your Google access, so a scheduled run whose credentials were removed ends as a preview, not as a failure.

```json
{
  "type": "preview",
  "mode": "dry-run",
  "message": "Preview only (no Google credentials): nothing was written. 5 rows in 13 columns would be written from the built-in sample dataset. Enter Google credentials to write them.",
  "reason": "No Google credentials were entered (neither a service account key nor an Apps Script web app address), so the Actor only shows what it would write and sends nothing to Google.",
  "datasetId": null,
  "rowsRead": 5,
  "columns": ["id", "title", "category", "price.amount", "price.currency", "..."],
  "sampleRows": [{ "id": "P-1001", "title": "Ceramic coffee mug", "category": "Kitchen", "price.amount": 12.9, "...": "..." }],
  "rowsThatWouldBeWritten": 5,
  "rowsSkipped": 0,
  "cellsThatWouldBeTruncated": 0,
  "nextSteps": ["No dataset was given, so the built-in sample dataset (5 products) was used. ...", "..."]
}
```

### Set up Google access: service account

1. In Google Cloud Console, create or choose a project.
2. Open **APIs & Services > Library**, search for **Google Sheets API** and click **Enable**.
3. Open **IAM & Admin > Service Accounts > Create service account**, enter a name, click **Create and continue**, skip the optional role steps and click **Done**.
4. Open the new account, then **Keys > Add key > Create new key > JSON > Create**. A key file downloads.
5. Paste the whole content of that file into **Service account key (JSON)** in the Actor input. Apify stores it encrypted.
6. Open your spreadsheet, click **Share**, paste the `client_email` address from the key file (it ends with `.iam.gserviceaccount.com`), choose **Editor** and click **Share**.

### Set up Google access: Apps Script web app

1. Open your spreadsheet and click **Extensions > Apps Script**.
2. Replace the sample code with the script at the end of this page. Change `SECRET_TOKEN` to a long random text of your own (at least 12 characters) and click **Save**.
3. Click **Deploy > New deployment**, choose **Web app**, set **Execute as** to **Me** and **Who has access** to **Anyone**, click **Deploy** and approve the permission request.
4. Copy the **Web app URL** (it ends with `/exec`) into **Apps Script web app URL** and your secret text into **Apps Script secret token**.
5. After every edit of the script, use **Deploy > Manage deployments**, the pencil, **Version: New version**, **Deploy**.

"Anyone" only means that the address needs no Google login. The script rejects every request without your token and writes only into the spreadsheet it belongs to. Keep the address and token secret.

### Input

| Field | Default | Description |
| --- | --- | --- |
| `datasetId` | none | The dataset to export. As an integration, the dataset of the triggering run. Empty in a preview: the built-in sample. |
| `spreadsheetUrl` or `spreadsheetId` | required for a service account | Address or ID of the spreadsheet. With Apps Script both can stay empty. |
| `sheetName` | `Sheet1` | The tab to write to. It is created if missing. |
| `mode` | `append` | `append`, `replace` or `upsert`. |
| `keyColumns` | none | Required for upsert: the case-sensitive column names that identify a row, such as `url`. |
| `columns` | all fields | Columns to write, in this order. |
| `flatten` | true | Nested objects become dotted columns. Lists of text are joined with ", ", lists of objects become JSON. |
| `includeHeader` | true | Write a header row. Upsert needs it. |
| `valueInputOption` | `RAW` | `RAW` stores values as they are. `USER_ENTERED` lets Sheets parse numbers and dates. |
| `maxItems` | all | Stop after this many items. |
| `authMode` | `auto` | Chosen from the credentials you filled in. |
| `serviceAccountJson`, `appsScriptUrl`, `appsScriptToken` | none | Secret fields, stored encrypted. Without them the run is a preview. |
| `dryRun` | false | Preview only: nothing is sent to Google, nothing is charged. |

```json
{
  "datasetId": "WkzbQMuFYuamGv3YF",
  "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1AbC.../edit",
  "sheetName": "Leads",
  "mode": "upsert",
  "keyColumns": ["url"],
  "serviceAccountJson": "{ ...the content of the key file... }"
}
```

### Use it as an integration

Open the Actor or task whose results you want in Google Sheets, go to **Integrations** and add this Actor. Fill in the Google fields, enter `{{resource.defaultDatasetId}}` in **Dataset ID** and choose the event, for example a successful run. If the field is empty, the Actor falls back to `payload.resource.defaultDatasetId`.

### Output example

Each run adds one free item to its dataset (also stored as the `OUTPUT` record): a summary, or a `preview` as above. On failure you get an `error` item with a `code` and a message that says what to do.

```json
{
  "type": "summary",
  "status": "succeeded",
  "message": "Done: 3 rows updated and 2 rows added in \"Leads\" in \"Prospects\".",
  "mode": "upsert",
  "sheetName": "Leads",
  "sheetUrl": "https://docs.google.com/spreadsheets/d/1AbC.../edit#gid=0",
  "rowsRead": 5,
  "rowsAppended": 2,
  "rowsUpdated": 3,
  "rowsSkipped": 0,
  "cellsTruncated": 0,
  "columns": ["url", "title", "price"],
  "durationMs": 4200
}
```

`rowsSkipped` counts items without a value in the key columns and repeated keys within a batch. `status` is `succeeded`, `stopped-early` or `failed`.

### Pricing

This Actor uses Apify's pay-per-event model.

| Event | When it is charged | Price |
| --- | --- | --- |
| Actor start | Once per run. Apify charges it automatically. | $0.005 |
| Row written | For each row Google confirmed as appended or updated. | $0.0008 ($0.80 per 1,000 rows) |

Appending 1,000 rows costs $0.805 and 10,000 rows cost $8.005. Skipped items, errors and the summary are free, and charging happens only after Google confirmed a batch. Set a maximum total charge: the Actor reads only as many items as the budget pays for, then stops cleanly ("Stopped early" in the status message). A preview is free.

### Security, privacy and revoking access

This Actor is not affiliated with, endorsed by or sponsored by Google. Your data goes only to the spreadsheet you named. The Actor talks only to the Apify API and, over HTTPS, to `oauth2.googleapis.com`, `sheets.googleapis.com`, `script.google.com` and `script.googleusercontent.com`. Your key, address and token stay in memory during the run and are never written to the log, the dataset or the key-value store. Error messages hide them.

To revoke access, remove the service account from the spreadsheet's **Share** list or delete its key under **IAM & Admin > Service Accounts > Keys**. For Apps Script, archive the deployment under **Deploy > Manage deployments** or change `SECRET_TOKEN` and deploy a new version. Also delete the saved input of tasks and schedules that hold the old secrets.

### FAQ

**How do I avoid duplicates when I export every day?** Use `upsert` with key columns that are unique per item, such as `url` or `id`. Append adds every item again.

**Why is the spreadsheet not found or access denied?** It is not shared with the service account, or the Google Sheets API is not enabled in its project. The message names the address to share with.

**Can I keep my own columns next to the exported data?** Yes. Upsert changes only the columns it writes and append only adds rows. Replace clears the whole tab.

**What happens if the run is interrupted?** After a migration it continues from the last confirmed batch without charging twice. If a network error leaves it unsure whether a batch arrived, append can repeat that batch. Upsert cannot.

**Which Google limits apply?** About 60 write requests per minute per user, so the Actor paces itself and writes up to 5,000 rows per request. A spreadsheet holds at most 10 million cells.

### Limitations

- Values only: no formatting, formulas, charts or images, and rows that left the dataset are never deleted.
- Append adds rows after the data block of the tab: keep it free of empty rows, or give the export its own tab.
- At most 2,000 columns are written. Extra fields are ignored and logged.
- Upsert keeps the tab's key columns in memory, about 100 bytes per row: give big sheets more memory.
- `USER_ENTERED` can change how a key such as `00123` looks in the sheet. Use `RAW` for upsert.
- Apps Script has its own daily quotas. Use the service account for very large exports.

### Changelog

- **0.1.1 (October 2026):** preview mode (`dryRun`, or no Google credentials).
- **0.1.0 (October 2026):** first release with append, replace and upsert, service account and Apps Script access, integration use and pay-per-event pricing.

### Apps Script code

Paste this into the script editor and change `SECRET_TOKEN`.

```javascript
/**
 * Receiver for the Apify Actor "Google Sheets Export & Sync for Apify Datasets".
 * Not affiliated with Google. Data is written only into the spreadsheet this script is attached to.
 *
 * 1) Change SECRET_TOKEN to a long random text of your own (the same text goes into the Actor input).
 * 2) Deploy > New deployment > Web app > Execute as: Me > Who has access: Anyone > Deploy.
 * 3) Copy the web app URL (it ends with /exec) into the Actor input.
 * After every edit of this file: Deploy > Manage deployments > Edit > Version: New version > Deploy.
 * @OnlyCurrentDoc
 */
const SECRET_TOKEN = 'CHANGE-ME-to-a-long-random-text';
const MAX_ROWS = 20000; // per request (the Actor sends 2,000 or fewer)
const MAX_CELLS = 1000000; // per request
const SEP = '\u001f';

function doGet() {
  return reply_({ ok: true, message: 'Receiver is deployed. It answers only POST requests that carry the secret token.' });
}

function doPost(e) {
  const lock = LockService.getScriptLock();
  let locked = false;
  try {
    const body = parseBody_(e);
    checkToken_(body.token);
    lock.waitLock(30000);
    locked = true;
    const cache = CacheService.getScriptCache();
    const cacheKey = body.requestId && body.action === 'write' && !body.dryRun ? 'req-' + body.requestId : null;
    const earlier = cacheKey && cache.get(cacheKey);
    if (earlier) return reply_(JSON.parse(earlier)); // a retry of a request that already ran: do not write twice
    const result = body.action === 'info' ? info_(body) : write_(body);
    result.ok = true;
    if (cacheKey) {
      try { cache.put(cacheKey, JSON.stringify(result), 21600); } catch (ignored) { /* a full cache must never fail a write that succeeded */ }
    }
    return reply_(result);
  } catch (err) {
    const message = String(err && err.message ? err.message : err);
    const code = err && err.code ? err.code : (/lock/i.test(message) ? 'BUSY' : 'INTERNAL');
    return reply_({ ok: false, error: { code: code, message: message } });
  } finally {
    if (locked) lock.releaseLock();
  }
}

function info_(body) {
  const opened = openSheet_(body, body.createSheet === true);
  const header = opened.sheet && body.includeHeader !== false ? readHeader_(opened.sheet) : [];
  return {
    spreadsheetId: opened.ss.getId(), spreadsheetUrl: opened.ss.getUrl(), spreadsheetTitle: opened.ss.getName(),
    sheetName: opened.name, sheetId: opened.sheet ? opened.sheet.getSheetId() : null,
    exists: !!opened.sheet, created: opened.created, header: header,
  };
}

function write_(body) {
  const rows = body.rows;
  const header = body.header;
  if (!Array.isArray(rows) || !Array.isArray(header) || header.length === 0) throw fail_('BAD_REQUEST', 'rows and header are required.');
  if (rows.length > MAX_ROWS || rows.length * header.length > MAX_CELLS) throw fail_('TOO_LARGE', 'Too many cells in one request.');
  const option = body.valueInputOption === 'USER_ENTERED' ? 'USER_ENTERED' : 'RAW';
  const dry = body.dryRun === true;
  const useHeader = body.includeHeader !== false;
  const opened = openSheet_(body, body.createSheet !== false);
  const sheet = opened.sheet;
  if (!sheet) throw fail_('NO_SHEET', 'The tab "' + opened.name + '" does not exist.');
  if (body.clearFirst && !dry) sheet.clearContents();

  const existing = useHeader ? readHeader_(sheet) : [];
  const finalHeader = useHeader ? mergeHeader_(existing, header) : header.slice();
  const map = header.map(function (name) { return finalHeader.indexOf(name); }); // where each incoming column sits in the tab
  const width = finalHeader.length;
  const aligned = rows.map(function (row) { return alignRow_(row, map, width); });
  if (useHeader && !dry && finalHeader.length > existing.length) writeHeader_(sheet, finalHeader);

  let appended = 0;
  let updated = 0;
  let skipped = 0;
  if (body.mode === 'upsert') {
    const keyIdx = (body.keyColumns || []).map(function (name) { return finalHeader.indexOf(name); });
    if (!keyIdx.length || keyIdx.some(function (i) { return i < 0; })) throw fail_('BAD_REQUEST', 'keyColumns must be columns of the tab header.');
    const managed = (body.managed || header.map(function (n, i) { return i; })).map(function (i) { return map[i]; })
      .filter(function (i) { return i >= 0; }).sort(function (a, b) { return a - b; });
    const keys = aligned.map(function (row) { return keyOf_(row, keyIdx); });
    const plan = planUpsert_(readIndex_(sheet, keyIdx), aligned, keys);
    skipped = plan.skipped;
    updated = plan.updates.length;
    appended = plan.appends.length;
    if (!dry) {
      writeUpdates_(sheet, plan.updates, segments_(managed), option);
      appendRows_(sheet, plan.appends.map(function (a) { return a.cells; }), width, option);
    }
  } else {
    appended = aligned.length;
    if (!dry) appendRows_(sheet, aligned, width, option);
  }
  if (!dry) SpreadsheetApp.flush();
  return { appended: appended, updated: updated, skipped: skipped, sheetName: opened.name, dryRun: dry };
}

// ---- spreadsheet helpers ----------------------------------------------------------------------------

function openSheet_(body, create) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) throw fail_('NO_SPREADSHEET', 'This script is not attached to a spreadsheet. Open the spreadsheet and create the script with Extensions > Apps Script.');
  if (body.spreadsheetId && body.spreadsheetId !== ss.getId()) throw fail_('WRONG_SPREADSHEET', 'This web app belongs to a different spreadsheet than the one in the Actor input.');
  const name = String(body.sheetName || '').trim();
  if (!name || name.length > 100) throw fail_('BAD_REQUEST', 'sheetName must have 1 to 100 characters.');
  const wanted = name.toLowerCase();
  let sheet = ss.getSheets().filter(function (s) { return s.getName().toLowerCase() === wanted; })[0] || null;
  let created = false;
  if (!sheet && create) {
    sheet = ss.insertSheet(name);
    created = true;
  }
  return { ss: ss, sheet: sheet, created: created, name: sheet ? sheet.getName() : name };
}

function readHeader_(sheet) {
  if (sheet.getLastRow() < 1 || sheet.getLastColumn() < 1) return [];
  const row = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues()[0];
  while (row.length && row[row.length - 1] === '') row.pop();
  return row;
}

function mergeHeader_(existing, wanted) {
  const out = existing.slice();
  wanted.forEach(function (name) { if (out.indexOf(name) < 0) out.push(name); });
  return out;
}

function alignRow_(row, map, width) {
  const out = [];
  for (let i = 0; i < width; i++) out.push('');
  for (let i = 0; i < row.length; i++) if (map[i] >= 0) out[map[i]] = row[i];
  return out;
}

function ensureSize_(sheet, rows, cols) {
  if (rows > sheet.getMaxRows()) sheet.insertRowsAfter(sheet.getMaxRows(), rows - sheet.getMaxRows());
  if (cols > sheet.getMaxColumns()) sheet.insertColumnsAfter(sheet.getMaxColumns(), cols - sheet.getMaxColumns());
}

function writeHeader_(sheet, header) {
  ensureSize_(sheet, 1, header.length);
  sheet.getRange(1, 1, 1, header.length).setValues([header.map(function (name) { return cellForWrite_(name, 'RAW'); })]);
}

function appendRows_(sheet, rows, width, option) {
  if (!rows.length) return;
  const start = sheet.getLastRow() + 1;
  ensureSize_(sheet, start + rows.length - 1, width);
  sheet.getRange(start, 1, rows.length, width).setValues(rows.map(function (row) {
    return row.map(function (value) { return cellForWrite_(value, option); });
  }));
}

function writeUpdates_(sheet, updates, segments, option) {
  const sorted = updates.slice().sort(function (a, b) { return a.row - b.row; });
  segments.forEach(function (seg) {
    let i = 0;
    while (i < sorted.length) {
      let j = i;
      while (j + 1 < sorted.length && sorted[j + 1].row === sorted[j].row + 1) j++;
      const block = sorted.slice(i, j + 1).map(function (u) {
        const out = [];
        for (let c = seg[0]; c <= seg[1]; c++) out.push(cellForWrite_(u.cells[c] === undefined ? '' : u.cells[c], option));
        return out;
      });
      sheet.getRange(sorted[i].row, seg[0] + 1, block.length, seg[1] - seg[0] + 1).setValues(block);
      i = j + 1;
    }
  });
}

// ---- upsert logic (the same rules as the Actor's own code; tested in Node) ---------------------------

function keyPart_(v) {
  if (v === null || v === undefined) return '';
  return v instanceof Date ? v.toISOString() : String(v);
}

function keyOf_(cells, positions) {
  let any = false;
  const parts = positions.map(function (p) {
    const part = keyPart_(cells[p]);
    if (part !== '') any = true;
    return part;
  });
  return any ? parts.join(SEP) : null;
}

function readIndex_(sheet, keyIdx) {
  const index = new Map();
  const last = sheet.getLastRow();
  if (last < 2) return index;
  const columns = keyIdx.map(function (c) { return sheet.getRange(2, c + 1, last - 1, 1).getValues(); });
  const positions = columns.map(function (c, j) { return j; });
  for (let i = 0; i < last - 1; i++) {
    const key = keyOf_(columns.map(function (col) { return col[i][0]; }), positions);
    if (key !== null && !index.has(key)) index.set(key, i + 2);
  }
  return index;
}

function planUpsert_(index, rows, keys) {
  const updates = [];
  const appends = [];
  const seen = new Map();
  let skipped = 0;
  rows.forEach(function (cells, i) {
    const key = keys[i];
    if (key === null || key === undefined) { skipped++; return; }
    const earlier = seen.get(key);
    if (earlier) { // the same key twice in one request: the later row wins
      earlier.list[earlier.at] = earlier.row === undefined ? { cells: cells, key: key } : { row: earlier.row, cells: cells, key: key };
      skipped++;
      return;
    }
    const row = index.get(key);
    if (row === undefined) {
      appends.push({ cells: cells, key: key });
      seen.set(key, { list: appends, at: appends.length - 1 });
    } else {
      updates.push({ row: row, cells: cells, key: key });
      seen.set(key, { list: updates, at: updates.length - 1, row: row });
    }
  });
  return { updates: updates, appends: appends, skipped: skipped };
}

function segments_(sorted) {
  const out = [];
  sorted.forEach(function (p) {
    const last = out[out.length - 1];
    if (last && p === last[1] + 1) last[1] = p;
    else out.push([p, p]);
  });
  return out;
}

// ---- cell values ------------------------------------------------------------------------------------

// Text that Sheets would turn into a formula, number, date, time, boolean or error when it is typed in.
const MONTH = '(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[a-z]*\\.?';
const WEEKDAY = '((mon|tue|wed|thu|fri|sat|sun)[a-z]*\\.?,?\\s+)?';
const PARSED_AS_NON_TEXT = [
  /^[=+\-@]/,
  /^\(?[$€£¥]?\s*(\d|\.\d)[\d\s.,]*([eE][+-]?\d+)?\s*%?\)?$/,
  /^\d{1,4}[-\/.]\d{1,2}([-\/.]\d{1,4})?([ T,]+\d{1,2}:\d{2}.*)?$/,
  /^\d{1,2}:\d{2}(:\d{2})?(\s*[ap]m)?$/i,
  /^(true|false)$/i,
  /^#(n\/a|div\/0!|value!|ref!|name\?|num!|null!)$/i,
  new RegExp('^' + WEEKDAY + '\\d{1,2}\\s+' + MONTH + '(\\s+\\d{2,4})?$', 'i'),
  new RegExp('^' + WEEKDAY + MONTH + '\\s+\\d{1,4}(st|nd|rd|th)?(,?\\s+\\d{2,4})?$', 'i'),
];

function isFormula_(text) {
  const c = text.charAt(0);
  if (c === '=' || c === '@') return true;
  return (c === '+' || c === '-') && !/^[+-]\s*\d[\d\s.,]*$/.test(text);
}

// RAW: text stays text (a leading apostrophe is how Sheets marks text; it is not stored).
// USER_ENTERED: Sheets may parse numbers and dates, but data never becomes a formula.
function cellForWrite_(value, option) {
  if (value === null || value === undefined) return '';
  if (typeof value !== 'string' || value === '') return value;
  if (option === 'USER_ENTERED') return isFormula_(value) ? "'" + value : value;
  return PARSED_AS_NON_TEXT.some(function (re) { return re.test(value); }) ? "'" + value : value;
}

// ---- plumbing -----------------------------------------------------------------------------------

function parseBody_(e) {
  if (!e || !e.postData || !e.postData.contents) throw fail_('BAD_REQUEST', 'Empty request.');
  let body;
  try { body = JSON.parse(e.postData.contents); } catch (x) { throw fail_('BAD_REQUEST', 'The request body is not valid JSON.'); }
  if (!body || typeof body !== 'object' || Array.isArray(body)) throw fail_('BAD_REQUEST', 'The request body must be a JSON object.');
  return body;
}

function checkToken_(token) {
  if (SECRET_TOKEN.indexOf('CHANGE-ME') === 0 || SECRET_TOKEN.length < 12) throw fail_('NOT_CONFIGURED', 'SECRET_TOKEN in the script is still the placeholder or has fewer than 12 characters. Choose your own long random secret, save, and deploy a new version.');
  if (typeof token !== 'string' || token.length !== SECRET_TOKEN.length) throw fail_('BAD_TOKEN', 'Wrong token.');
  let diff = 0;
  for (let i = 0; i < token.length; i++) diff |= token.charCodeAt(i) ^ SECRET_TOKEN.charCodeAt(i);
  if (diff !== 0) throw fail_('BAD_TOKEN', 'Wrong token.');
}

function fail_(code, message) {
  const err = new Error(message);
  err.code = code;
  return err;
}

function reply_(object) {
  return ContentService.createTextOutput(JSON.stringify(object)).setMimeType(ContentService.MimeType.JSON);
}
```

# Actor input Schema

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

The dataset to export: its ID (Apify Console > Storage > Datasets) or a name such as username~my-dataset. When this Actor runs as an integration of another Actor or task, enter {{resource.defaultDatasetId}} (or leave the field empty) to export the dataset of the run that triggered it. Without Google credentials, an empty field previews a built-in sample of 5 items.

## `spreadsheetUrl` (type: `string`):

The address of the Google Sheet as shown in the browser, for example https://docs.google.com/spreadsheets/d/1AbC.../edit. Fill in this field or the spreadsheet ID. With the Apps Script method you can leave both empty: the script writes to the spreadsheet it belongs to.

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

Alternative to the address: the long ID between /d/ and /edit in the address of the sheet. An address pasted here also works.

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

The tab (worksheet) to write to. It is created when it does not exist yet.

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

Append adds the items below the existing rows. Replace clears the tab first and writes the dataset. Upsert updates the rows whose key columns match and appends the others, so running the same export again never creates duplicates.

## `keyColumns` (type: `array`):

Required for upsert. One or more column names that together identify a row, for example url or id. Names are case-sensitive; nested fields are written like address.city. Rows without a value in these columns are skipped.

## `authMode` (type: `string`):

How the Actor gets permission to write to your sheet. Automatic uses the method whose fields below are filled in.

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

Service account method: paste the whole content of the JSON key file of a Google Cloud service account, and share the spreadsheet with its client_email address as Editor.

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

Apps Script method: the web app URL that ends with /exec, from Deploy > New deployment in the script of your spreadsheet.

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

Apps Script method: the SECRET_TOKEN you wrote in the script (at least 12 characters). Requests without it are rejected.

## `columns` (type: `array`):

Optional: the columns to write, in this order. Leave empty to write every field of the items (the union of all fields, in order of first appearance). Nested fields are written like address.city.

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

On: nested objects become columns with dotted names (address.city) and lists of text or numbers are joined with ", ". Off: only top-level fields become columns and nested values are written as JSON text. Lists that contain objects are always written as JSON text.

## `includeHeader` (type: `boolean`):

On: row 1 holds the column names. If the tab already has a header, the new data is aligned to it and new columns are added at the end. Off: only data rows are written (not possible with upsert).

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

Raw stores every value exactly as it is (text stays text, nothing becomes a date or a formula). User entered lets Google Sheets interpret numbers and dates as if typed; text that starts with =, +, - or @ is still kept as plain text, so exported data never runs as a formula.

## `maxItems` (type: `integer`):

Optional cap on the number of dataset items to export. Leave empty to export everything.

## `dryRun` (type: `boolean`):

On: read up to 100 items and show in the output the columns and rows that would be written, without contacting Google and without charging for rows. The Actor does the same, whatever this setting says, when no Google credentials are entered. Off: write to the spreadsheet as soon as Google credentials are entered.

## Actor input object example

```json
{
  "sheetName": "Sheet1",
  "mode": "append",
  "authMode": "auto",
  "flatten": true,
  "includeHeader": true,
  "valueInputOption": "RAW",
  "dryRun": false
}
```

# Actor output Schema

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

What was written: rows read, appended, updated and skipped, the columns, the link to the tab and, if something went wrong, the reason. In a preview run: what would be written, and nothing was.

## `report` (type: `string`):

One summary item per run, plus an error item when the run failed, or one preview item when the run only showed what it would write. These items are free.

# 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 = {};

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

# Prepare the Actor input
run_input = {}

# Run the Actor and wait for it to finish
run = client.actor("ottomath/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 '{}' |
apify call ottomath/dataset-to-google-sheets --silent --output-dataset

```

## MCP server setup

```json
{
    "mcpServers": {
        "apify": {
            "type": "http",
            "url": "https://mcp.apify.com/?tools=fetch-actor-details,ottomath/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 <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/59IJ6iWAucWBCBbAO/builds/EN3Q3JiI1lJY4zdNA/openapi.json