Stop re-reading the whole sheet: a push‑sync design for Google Sheets and n8n

说明与用途

设计中，Google Sheets 通过 webhook 推送编辑行到 n8n，n8n 更新数据，消除完整表格下载，并通过夜间对账确保同步。

问题

A cron workflow that reads a Google Sheet on every run pays the same tax every time: the full download.

结果

The design eliminates full‑sheet downloads, reduces latency, and maintains data consistency between Google Sheets and n8n.

工作流

• 1. In Google Sheets, add an Apps Script function onEditPush that POSTs edited row data to an n8n webhook.
• 2. Create an installable trigger (From spreadsheet → On edit) to run the script.
• 3. In n8n, set up a Webhook node to receive POSTs and a Code node to zip values with headers and upsert into workflow static data keyed on appointment_id.
• 4. Handle invalid payloads by logging errors and responding 422.
• 5. Run a nightly job that queries the key column via the gviz/tq endpoint, flattens the result, and reconciles drift with the stored data.

证据

A cron workflow that reads a Google Sheet on every run pays the same tax every time: the full download.

来源

The sheet pushes edited rows

来源

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.

来源