A cron workflow that reads a Google Sheet on every run pays the same tax every time: the full download. The Sheets node reads a range, and the v4 read behind it (spreadsheets.values.get) takes render options, not query conditions, so narrowing columns in the node narrows the result after the transfer, not the transfer itself. At 50,000 rows that read is slow and heavy, and every strategy that starts with "read the sheet, then decide" repeats it on every run.
There is a server-side exception worth knowing. The Visualization query endpoint (gviz/tq) accepts SQL-ish SELECT ... WHERE and returns JSON, documented in Google's query language reference, and you can hit it from an HTTP Request node. It answers queries, which makes it the right tool for one job in this design: reading a single column cheaply. It is not a sync mechanism.
The design that survives a sheet edited by humans all day inverts the flow: the sheet pushes, n8n keeps a projection, and a nightly reconcile heals drift. Three pieces.
Piece 1: the sheet pushes edited rows
In the Sheet, open Extensions > Apps Script and write the function below. Then add an installable trigger for it (Triggers > Add trigger, event source "From spreadsheet", type "On edit"). Installable, not a simple trigger: simple triggers cannot call services that need authorization, and UrlFetchApp does. The edit event carries a range pointing at the edited cells (trigger guide). The function reads the edited row and POSTs it:
function onEditPush(e) {
if (!e.range) return;
const sheet = e.range.getSheet();
if (sheet.getName() !== 'Appointments') return;
const first = e.range.getRow();
const last = e.range.getLastRow();
const values = sheet.getRange(first, 1, last - first + 1, sheet.getLastColumn()).getValues();
for (let i = 0; i < values.length; i++) {
UrlFetchApp.fetch('https://YOUR-N8N/webhook/sheet-row', {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({ sheet: sheet.getName(), row_number: first + i, values: values[i] }),
muteHttpExceptions: true
});
}
}
One edit event can cover many rows: pasting a block or dragging the fill handle fires a single event whose range spans every touched row, and reading only getRow() would push just the top one while the rest wait for the reconcile. So the function reads the whole block with one getValues call and pushes every row in it.
The installable trigger needs a one-time authorization by someone who can own the sheet. For a shared scheduling sheet that is a feature: the manager authorizes their own sheet, and no service account of yours ever holds it.
Note what the payload carries for diagnostics only: the row number. Row numbers shift the moment anyone sorts, inserts, or deletes. Identity lives elsewhere.
Piece 2: n8n upserts keyed on a stable ID
On the n8n side: a Webhook node (POST, response mode "Using Respond to Webhook node"), a code node that zips the values array against a known header and upserts into workflow static data, keyed on the ID column:
const HEADER = ['appointment_id', 'starts_at', 'status', 'revenue_eur', 'source'];
const payload = $json.body || $json;
if (!Array.isArray(payload.values)) {
return [{ json: { invalid: true, reason: 'expected values array' } }];
}
const row = {};
HEADER.forEach((h, i) => { row[h] = payload.values[i]; });
if (!row.appointment_id) {
return [{ json: { invalid: true, reason: 'appointment_id missing, sheet row ' + payload.row_number } }];
}
const store = $getWorkflowStaticData('global');
store.rows = store.rows || {};
store.rows[row.appointment_id] = { ...row, pushed_at: new Date().toISOString() };
return [{ json: { upserted: row.appointment_id, store_size: Object.keys(store.rows).length } }];
An IF on invalid routes to the pattern every workflow in my pack uses: write the row to an error log with the reason, answer 422 through a Respond to Webhook node. The caller here is a script with muteHttpExceptions, so its failures are silent by construction, which is exactly why the rejected-request log on the n8n side matters.
The upsert is idempotent. Two rapid edits to the same row fire two POSTs, the second overwrites the first, and that matches the sheet's own last-write-wins editing behavior. No conflict machinery needed.
Piece 3: a nightly reconcile heals drift
Pushes get lost. Someone edits while the webhook is down. A row is deleted, and deleting a row fires no onEdit at all; structural changes fire the onChange trigger instead. So once a night, read only the key column through the gviz endpoint with SELECT A, flatten the gviz envelope (its rows sit under table.rows, not at the top level) into {key} items, and compare:
const sheetKeys = $input.all()
.map((i) => String(i.json.key))
.filter((k) => k && k !== 'undefined');
const sheetSet = new Set(sheetKeys);
const store = $getWorkflowStaticData('global');
const storeKeys = Object.keys(store.rows || {});
const removed = storeKeys.filter((k) => !sheetSet.has(k));
for (const k of removed) delete store.rows[k];
const neverPushed = sheetKeys.filter((k) => !store.rows[k]);
return [{ json: {
sheet_rows: sheetKeys.length, store_rows: Object.keys(store.rows).length,
removed_missing_from_sheet: removed.length, never_pushed: neverPushed.length
} }];
Two details keep the compare honest at the 50,000-row scale. Membership checks go through a Set built once, because includes inside a filter is quadratic and both sides can be large. And empty keys are filtered out before anything else: a sheet with trailing blank rows returns them as empty strings, and without the filter every blank line counts as never_pushed forever, which reads as drift when it is just whitespace.
never_pushed is the drift signal. If it sits above zero night after night, the push trigger is missing edits, and you want to know that instead of absorbing it.
onChange tells you what, not where
Google also offers an installable onChange trigger whose event carries a changeType like INSERT_ROW, REMOVE_ROW or INSERT_COLUMN (event reference). It tells you what kind of change happened, not where it happened, so it cannot drive an upsert. Its correct job is firing the reconcile early: someone inserting or deleting rows is exactly when the store is most likely to drift.
Why not two-way sync
Two-way sync needs conflict resolution: two writers, one row, no natural winner without timestamps you trust and rules for who breaks ties. Last-write-wins on both sides loses data quietly. The push design keeps the sheet as the single source of truth and n8n as a projection you can rebuild at any time: delete the static data, run the reconcile, and the store comes back from the sheet. A projection you can rebuild beats a replica you have to repair.
Where this ships
The appointment pack I sell (€29, Afspraakpack for n8n) reads appointment rows on schedules and webhooks, and treats its source with the same discipline: validation with field-level reasons, keyed writes into static data, error logs, 422 responders. All seven workflows run on demo data out of the box, and the README names the Sheet nodes you replace with your own source. The reminder flagship is here, the silent-failure patterns that pair with this design are here, and the weekly digest that reads a week of rows into one report is here. The free calculators for the scheduling side live at no-show cost, waitlist fill-rate and AI workflow cost.
The sheet is edited by humans all day. Let it tell you what changed instead of asking it everything on every run.
