Google Sheets Import & Export — Append, Upsert, Read

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:

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

// 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

Output

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

{
  "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: merge, clean, dedupe and join datasets, CSV and Excel before writing them to a sheet.
• Bulk HTTP Requests: call any API once for every row of a CSV, JSON list or Apify dataset.
• Bulk File & Image 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.

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.