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.

{
  "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.

{
  "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.

{
  "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.

/**
 * 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);
}