How to Set Up Reminders in Google Sheets for Due Dates
If you track due dates in Google Sheets - rent payments, invoice deadlines, contract renewals, maintenance follow-ups - you have probably missed one at some point. Not because you forgot it existed. Because you forgot to check the sheet.
Here are three layers of reminders you can add to any Google Sheet with due dates. Start with the visual layer, add formulas, then automate with email alerts.
Layer 1: Conditional Formatting (Visual Reminders)
This is the easiest win. Conditional formatting highlights rows based on how close they are to the due date. You see red, you act.
Setup
Assume your due dates are in column D, starting in row 2. Select the range you want to format (like A2:H50).
Go to Format > Conditional formatting. Add these rules in order:
Overdue (red): Custom formula: =AND($D2<>"", $D2
Due today (orange): Custom formula: =AND($D2<>"", $D2=TODAY()). Set background to orange.
Due within 3 days (yellow): Custom formula: =AND($D2<>"", $D2<=TODAY()+3, $D2>TODAY()). Set background to yellow.
Due within 7 days (light yellow): Custom formula: =AND($D2<>"", $D2<=TODAY()+7, $D2>TODAY()+3). Set background to light yellow.
Now your sheet visually screams at you. Red rows are past due. Orange is today. Yellow is coming up soon. At a glance you know what needs attention.
Adding a "Completed" Override
If you have a "Status" or "Completed" column (say column F), add a condition to skip completed items:
Modify each formula to include: =AND($D2<>"", $D2
Now completed items stay neutral even if their due date has passed.
Layer 2: Countdown Formulas
Add a "Days Until Due" column that shows exactly how many days remain. Put this in a new column:
=IF(D2="", "", IF(D2
This shows "OVERDUE by 3 days" or "5 days left" depending on the situation. Much more useful than staring at raw dates and doing mental math.
For a priority-based version that accounts for urgency levels:
=IF(D2="","",IF(D2
This categorizes everything into OVERDUE, URGENT (1-3 days), SOON (4-7 days), or OK. Use it as a filter or sort column to see the most pressing items first.
Layer 3: Automatic Email Reminders
Conditional formatting and formulas only work when you open the sheet. For actual reminders that come to you, you need Apps Script.
The Script
Go to Extensions > Apps Script and paste this:
function checkDueDates() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tasks");
var data = sheet.getDataRange().getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
var overdue = [];
var upcoming = [];
for (var i = 1; i < data.length; i++) {
var item = data[i][0]; // Column A: item name
var dueDate = new Date(data[i][3]); // Column D: due date
var status = data[i][5]; // Column F: status
dueDate.setHours(0, 0, 0, 0);
if (status === "Done") continue;
if (!dueDate || isNaN(dueDate)) continue;
var daysUntil = Math.floor((dueDate - today) / 86400000);
if (daysUntil < 0) {
overdue.push(item + " - due " + (-daysUntil) + " days ago");
} else if (daysUntil <= 3) {
upcoming.push(item + " - due in " + daysUntil + " days");
}
}
if (overdue.length === 0 && upcoming.length === 0) return;
var body = "";
if (overdue.length > 0) {
body += "OVERDUE:
" + overdue.join("
") + "
";
}
if (upcoming.length > 0) {
body += "DUE SOON:
" + upcoming.join("
");
}
MailApp.sendEmail(
"[email protected]",
"Due Date Alert: " + overdue.length + " overdue, "
+ upcoming.length + " upcoming",
body
);
}
Set a daily trigger (clock icon > Add Trigger > Time-driven > Day timer > 7am-8am). Every morning you get an email listing what is overdue and what is coming up in the next 3 days.
Customizing the Reminder Window
Change daysUntil <= 3 to daysUntil <= 7 for a week of advance notice. Or add multiple tiers:
if (daysUntil <= 1) {
urgent.push(item + " - due TOMORROW");
} else if (daysUntil <= 3) {
soon.push(item + " - due in " + daysUntil + " days");
} else if (daysUntil <= 7) {
upcoming.push(item + " - due in " + daysUntil + " days");
}
Real-World Applications
This same pattern works for any kind of due-date tracking:
Rent collection: Due dates for each tenant with a grace period. See our late rent notification guide for a landlord-specific version.
Invoice tracking: When client payments are expected. Flag anything past 30 days.
Lease renewals: Track expiration dates 60-90 days out. I built a full lease expiration tracker with this approach.
Maintenance follow-ups: When vendor work should be completed. Flag aging requests by priority.
Contract renewals: Insurance, subscriptions, vendor contracts. Get reminded before auto-renewal kicks in.
The Limitation You Will Hit
All three layers share one weakness: they depend on you keeping the sheet updated and (for layers 1 and 2) actually opening it.
Apps Script reminders solve the "opening" problem. But they introduce maintenance problems. Scripts break silently. Triggers get revoked. Column positions change and the script reads wrong data.
If due dates are critical to your business - like rent collection - you probably want something more reliable than a script you wrote once and forgot about. That is why purpose-built monitoring tools exist.
For rent and maintenance tracking specifically, RentGuard connects to your Google Sheet, checks it daily, and sends text and email alerts when items are overdue. No code to maintain. $15/month.
For the free template approach, grab our due date tracker template with all the formulas and formatting pre-built.
Stop missing late rent payments
RentGuard monitors your Google Sheet and alerts you when rent is overdue or maintenance is aging. No migration. 5 minute setup. 30 days free.
Start Free Monitoring →