This is a guest post by Tahir Abbas.
If you freelance or run a small service business, there is a good chance your invoices live in a Google Sheet and your "invoice system" is you, copying a client's email address into Gmail at 11 pm. This post shows the setup I use for clients: one sheet, one short Apps Script, and a daily trigger that sends invoices and nudges overdue ones. No add-ons, no paid tools.
1. The sheet layout
Create a tab named Invoices with these headers in row 1:
A: Invoice # | B: Client | C: Email | D: Amount | E: Due date | F: Status | G: Last emailed- Status holds one of: Ready, Sent, Paid. The script also writes "Check amount/date" when a row has a problem (see below).
- Amount must be a plain number (500, not "$500"). Format the column as a number or currency.
- Due date must be a real date, not text. Format the column as Date.
- You set a row to Ready when the invoice is final. The script does the rest.
Add one test row with your own email address in column C before you run anything. Two sample rows look like this:
Invoice # | Client | Email | Amount | Due date | Status | Last emailed
INV-001 | Sample Client A | [email protected] | 450 | 20 Nov 2026 | Ready |
INV-002 | Sample Client B | [email protected] | 1200.50 | 15 Oct 2026 | Sent | 1 Oct 20262. The script that sends the invoices
Open Extensions > Apps Script, delete the default code and paste this:
const SHEET_NAME = 'Invoices';
const YOUR_NAME = 'Your Name';
function sendReadyInvoices() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(30000)) return; // another run is in progress
try {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName(SHEET_NAME);
const tz = ss.getSpreadsheetTimeZone();
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const [num, client, email, amount, due, status] = rows[i];
if (status !== 'Ready' || !email) continue;
// Guard: amount must be a real number, due date a real date
const amt = Number(amount);
if (amount === '' || isNaN(amt) || !(due instanceof Date)) {
sheet.getRange(i + 1, 6).setValue('Check amount/date');
continue;
}
const dueText = Utilities.formatDate(due, tz, 'd MMM yyyy');
const subject = 'Invoice ' + num + ' from ' + YOUR_NAME;
const body =
'Hi ' + client + ',\n\n' +
'Please find invoice ' + num + ' for ' + amt.toFixed(2) +
', due on ' + dueText + '.\n\n' +
'Thank you,\n' + YOUR_NAME;
MailApp.sendEmail(email, subject, body);
sheet.getRange(i + 1, 6).setValue('Sent');
sheet.getRange(i + 1, 7).setValue(new Date());
SpreadsheetApp.flush();
}
} finally {
lock.releaseLock();
}
}What it does: it reads every row, skips anything that is not marked Ready, emails the client, then flips the status to Sent and stamps the time. If the amount is blank or text, or the due date is not a real date, it marks the row "Check amount/date" and moves on, so one bad row never stops the rest. Fix the row, set its status back to Ready, and the next run picks it up.
The script lock means a manual run that overlaps the daily trigger waits its turn instead of sending the same invoice twice, and because the status changes right after each send, a second run skips rows already marked Sent.
Dates are formatted with the spreadsheet's own time zone (File > Settings), so a due date does not come out a day off.
Want a PDF attached? Keep your invoice as a Google Doc template, copy it per client, export it as a PDF with DriveApp and pass it as an attachment using the object form of MailApp.sendEmail. I leave that out here to keep the script short. Note that DriveApp asks for an extra Drive permission.
3. A reminder for overdue invoices
Add a second function in the same file:
function remindOverdue() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(30000)) return;
try {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName(SHEET_NAME);
const tz = ss.getSpreadsheetTimeZone();
const rows = sheet.getDataRange().getValues();
const today = new Date();
today.setHours(0, 0, 0, 0);
for (let i = 1; i < rows.length; i++) {
const [num, client, email, amount, due, status, last] = rows[i];
if (status !== 'Sent' || !email) continue;
const amt = Number(amount);
if (amount === '' || isNaN(amt) || !(due instanceof Date)) continue;
const overdue = due < today;
const daysSince = last instanceof Date ? (today - last) / 86400000 : 999;
if (!overdue || daysSince < 7) continue;
MailApp.sendEmail(
email,
'Reminder: invoice ' + num + ' is overdue',
'Hi ' + client + ',\n\nA friendly reminder that invoice ' + num +
' (' + amt.toFixed(2) + ') was due on ' +
Utilities.formatDate(due, tz, 'd MMM yyyy') +
'. If you have already paid, please ignore this message.\n\nThank you,\n' + YOUR_NAME
);
sheet.getRange(i + 1, 7).setValue(new Date());
SpreadsheetApp.flush();
}
} finally {
lock.releaseLock();
}
}It only reminds when the invoice is past due and at least 7 days have passed since the last email, so a client is never nagged daily. When a client pays, change the status to Paid and the script leaves that row alone.
4. Make it run by itself
- In Apps Script, click the clock icon (Triggers) and choose Add Trigger.
- Function: sendReadyInvoices, event source Time-driven, Day timer, pick an hour.
- Add a second trigger for remindOverdue the same way.
The first run asks you to authorize the script. Because it uses MailApp, the only permissions it needs are to work with your spreadsheet and to send email as you.
You may see a screen saying "Google hasn't verified this app". That is normal for a script you wrote yourself, and it is where most people give up. Click Advanced, then "Go to [your project name] (unsafe)", and allow access. It is your own script running in your own account.
5. Three mistakes that break it
- Permissions. If nothing sends, open Executions in Apps Script. A permissions error usually means the authorization screen was dismissed. Run the function once manually and accept the prompts. If a trigger fails later, Apps Script emails you a failure notice, so check those emails and the Executions page for the exact error.
- Daily quotas. Google limits how many emails a script can send per day, and the limit is lower for free Gmail accounts than for Google Workspace accounts. Check the current numbers on Google's Apps Script quotas page before you point this at a long client list, and split big batches across days.
- Dates and amounts stored as text. If the due date is text like "05/11/2026", it can be read as the wrong day or month depending on your locale, and overdue checks go wrong. The same goes for an amount typed as "$500". The script above flags those rows instead of guessing. Format the columns properly and test with one row where you know the right answer.
A bonus fourth one: test with your own email first. Every time.
Where to take it next
Once this works, the same pattern handles payment-received confirmations, quote follow-ups and onboarding emails. The idea stays the same: a status column drives what the script does.
About the author: Tahir Abbas builds spreadsheet automation, lead-generation and web tools for small businesses at Softotech Solutions. He has more than ten years of freelance client work.
