THE BRIEF
A former employee wrote a Google Apps Script that emailed clients when invoices were overdue. It stopped working two months ago and nobody knows why. Can you fix it?
Scenario: Office manager at a small architecture studio
Package: Debug or fix an existing script or formula · $40 · 2 hrs
This script emails clients when an invoice is 7 days overdue. It's fixed and running again, which saves you about 3 hours a month of chasing invoices by hand.
WHAT WAS WRONG
Three things, in the order I found them.
- The sheet tab was renamed. The script looks for a tab called "Invoices." Someone renamed it "Invoices 2026" in August. The script found nothing, so it sent nothing, and didn't tell anyone.
- Dates were being compared as text. In plain words: the script was checking whether "9/5/2026" comes before "10/1/2026" alphabetically, not by date. It got some right by luck.
- No error alerts. When it failed, it failed silently.
WHAT I CHANGED
- The script now finds the tab by a setting at the top of the file, so a rename only needs one line changed.
- Dates are read as real dates. The overdue check is now: today minus due date is 7 days or more.
- If the script can't find the tab or a column, it emails you instead of doing nothing.
- It writes the date it sent a reminder into a "Reminded on" column, so no client gets two reminders in one week.
HOW I TESTED IT
- Made a copy of the sheet with 12 fake invoices: 4 overdue, 4 not due, 4 already paid.
- Pointed the script at a test email address.
- Result: exactly 4 emails, to the 4 overdue invoices. Ran it again the next minute: 0 emails, because they'd already been reminded.
THE ONE SETTING YOU MIGHT CHANGE
Near the top: OVERDUE_DAYS = 7. Change it to 14 if 7 feels pushy.
The trigger still runs every weekday at 9 a.m. I left that as it was.
README's in the file. Ping me if it breaks. — Malik
