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.
Top comments (4)
Splitting the work into a push for edits and a nightly reconcile that reads only the key column is a sound design. The note that deleting a row fires onChange and not onEdit is something many tutorials miss. Two parts of the code could be tighter. First, onEditPush only sends e.range.getRow(), which is the top row of the edited range. When someone pastes a block or drags the fill handle down ten rows, one edit event fires and only the first row gets pushed. The other nine wait for the reconcile and show up in never_pushed, which looks like a trigger problem when it isn't one. Looping from e.range.getRow() to e.range.getLastRow() and reading that block with a single getValues call fixes it. Second, the reconcile calls sheetKeys.includes(k) inside a filter. At the 50,000-row scale you mention that is quadratic, on the order of billions of comparisons when both sides are full. Building a Set from sheetKeys first makes it linear. I would also filter empty keys out of the gviz rows, so blank lines at the bottom of the sheet don't show up as permanent drift.
All three taken, and the article now ships them. Thanks for the multi-row catch in particular: onEditPush reads the full block from getRow() to getLastRow() with one getValues call and pushes every row, so a pasted block or a fill-handle drag no longer parks nine rows in never_pushed until the nightly pass.
The reconcile got the Set and the empty-key filter. On the Set: at the 50,000-row scale the includes-inside-filter version was doing store-size times sheet-size string comparisons every night, and Set.has is the honest fix. On empty keys: trailing blank rows in the gviz column were counting as never_pushed forever, which read as drift when it was just whitespace, so keys are filtered to non-empty before anything else.
Reading the whole block with one getValues call is the version I had in mind, and it keeps the push to a single sheet read no matter how many rows were pasted. Filtering empty keys before building the Set is also the right order, since a blank string would otherwise sit in the Set and quietly hide any store record with an empty key. The never_pushed column should now stay much quieter, so anything that does show up there is more likely to be a real trigger problem.
Agreed, and that last point is worth pushing one step further: the reconcile already computes never_pushed, so it can page only when the same key sits there two nights in a row. A row added after the push ran shows up once and clears the next night; the same key twice means the onEdit path itself is broken, most often a trigger that was not reinstalled after a redeploy.
Writing one health row per night (date, never_pushed count) makes the trend visible for free, and a flat zero is the cheapest proof the trigger path is alive.