Build an invoice reminder workflow in n8n with Google Sheets (idempotent, AU GST wording)

Flow: Schedule Trigger (daily 9am, workflow timezone set) → Google Sheets "Get rows" → Code node (decide) → Send Email (SMTP) → Google Sheets "Update row" (match on row_number).

The decision logic

1. Skip rows where status is paid, cancelled or on hold.
2. daysFromDue = today − due_date, where today is the date in the workflow timezone: $today.toFormat('yyyy-MM-dd'), not new Date().toISOString(), which gives the UTC date (at 9am in Sydney that's still yesterday, so every count comes out a day short). Accept YYYY-MM-DD and DD/MM/YYYY text plus Google Sheets serial numbers, and reject impossible dates instead of letting Date.UTC roll them over.
3. Stages = [−3, 0, 7, 14, 30]. Pick the highest stage whose offset ≤ daysFromDue, so downtime doesn't cause a burst of emails.
4. Send only if that stage's index is greater than the stored last_stage_sent index (idempotent).
5. Write back last_stage_sent and last_sent_date.

Gotchas we hit

• Send Email v2.1 appends an n8n attribution footer by default. Turn it off under Options.
• Importing with n8n import:workflow fails if the JSON has no id.
• Test against a local SMTP catcher (e.g. Python aiosmtpd) before touching a real inbox.
• Google Sheets can hand you a date cell as a serial number (46312 is 17 Oct 2026), depending on the Get rows node's Options → Output Formatting → Date Formatting. A five-digit number matches neither text pattern, so a reformatted column quietly drops out of the reminders. We set that option to Serial Number explicitly and convert it (snippet below).
• The email goes out before last_stage_sent is written, so if Update row fails, the next run sends the same stage again. Turn on Retry On Fail for the Update row node and set an Error Workflow so you hear about it.

const todayStr = $today.toFormat('yyyy-MM-dd'); // workflow timezone, not UTC
const today = new Date(todayStr + 'T00:00:00Z');
const EPOCH = Date.UTC(1899, 11, 30); // Google Sheets / Excel day zero
const ymd = (y, m, d) => { const t = new Date(Date.UTC(y, m - 1, d));
  return t.getUTCMonth() === m - 1 && t.getUTCDate() === d ? t : null; };
function parseDate(v) {
  const s = String(v ?? '').trim(); let m;
  if (typeof v === 'number' || /^\d{5}(\.\d+)?$/.test(s)) { const n = Number(s); // Sheets serial
    return n > 20000 && n < 80000 ? new Date(EPOCH + Math.floor(n) * 86400000) : null; }
  if ((m = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/))) return ymd(+m[1], +m[2], +m[3]);
  if ((m = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/))) return ymd(+m[3], +m[2], +m[1]); // DD/MM/YYYY
  return null;
}
const daysFromDue = due => Math.round((today - due) / 86400000);

AU GST wording

If the business is GST-registered: "Tax invoice … Total includes GST of $X", where X = total ÷ 11 rounded to the cent. Otherwise: "Invoice … No GST has been charged".

Updated 17 Oct 2026: added the timezone, serial-date and write-back notes after a reader's comment. Thank you.

Packaged version with guide, sample sheet and 5 templates: https://quillroo.gumroad.com/l/au-invoice-chaser

---

Published by Quillroo, a small Australian automation studio. Questions: [e-mail]

Comments

[Hayrullah Kar]

Picking the highest due stage is a good rule, because a week of downtime then produces one email instead of three. I see two places where daysFromDue can drift by a day. First, if the Code node builds today from new Date().toISOString(), it gets the UTC date. At 9am in Sydney the UTC date is still yesterday, so the comparison can come out one day short. $today follows the workflow timezone you already set, so it avoids this. Second, depending on its output formatting option, the Google Sheets node can return dates as serial numbers instead of text. A five-digit number matches neither YYYY-MM-DD nor DD/MM/YYYY, so a column that someone reformats could quietly drop out of the reminders. Setting that option to formatted values, or handling the number explicitly, prevents that. The order of steps matters too: the email goes out before last_stage_sent is written, so if Update row fails, the next run sends the same stage again. Writing a sending marker first, or at least alerting when the update fails, closes that gap.

[Ledgerline Tools]

Thanks for such a careful read. All three points were worth checking, so here's what we found.

UTC "today": our workflow wasn't building today from toISOString(). It formatted the current time in an explicit Australia/Sydney timezone, so the 9am run was already counting from the Sydney date. But the article never said how to get "today", which leaves exactly the trap you described for anyone writing their own version. A free template we hadn't released yet also defaulted to UTC. Everything now uses $today as you suggested, so there's one timezone setting (the workflow's) instead of two, and the article spells it out.

Serial-number dates: this one was a real gap. The parser only understood YYYY-MM-DD and DD/MM/YYYY, so a five-digit serial would have quietly dropped that row, just as you said. The Get rows node now sets Date Formatting to Serial Number explicitly, and the Code node converts serials from the 30 Dec 1899 epoch and still accepts both text formats. While we were in there, we also made it reject impossible dates like 31/02 or US-order 10/16 instead of letting Date.UTC roll them into another month.

Write-back after send: also fair. For now the Update row node retries on failure, and the guide recommends an Error Workflow so a failed write gets noticed. We thought about writing a "sending" marker first. It swaps the risk of a repeat email for the risk of a stage that's marked but never sent, so we've left it as a documented option for the moment.

We added unit tests for each case, including a 9am Sydney run while the UTC date is still the day before, and serial dates as numbers and as text. We also re-ran the workflow in a local n8n with the server clock set to UTC. The article now has a short snippet covering all of this. Thanks again!