The problem
You have a spreadsheet with dates in it. Due dates, renewal dates, follow-up dates, deadlines. When one of those dates is approaching, someone needs to send an email. Right now, that someone is you, and you have to remember to check the sheet every day. Sometimes you forget. The invoice goes overdue. The renewal lapses. The follow-up never happens. Not because you don't care, but because checking a spreadsheet against today's date and manually sending emails is exactly the kind of task that falls through the cracks. This guide builds a script that checks your date column every day and sends reminder emails automatically when a date is approaching or has passed.
What we're building
A daily script that:
- Scans a Google Sheet for rows where the date column is within a certain number of days from today
- Sends a reminder email for each matching row
- Logs that the reminder was sent so it doesn't send duplicates
This works for any date-driven reminder: invoice due dates, contract renewals, subscription expirations, task deadlines, client follow-ups, employee review dates, or anything else tracked in a spreadsheet.
Step-by-step setup
Step 1: Organize your sheet
You need at minimum:
- A column with the date to watch (due date, renewal date, etc.)
- A column with the email address to notify
- A column with a name or identifier for the row
Optional but recommended:
- A "Reminder Sent" column to prevent duplicate emails
Step 2: Open Apps Script
In your Google Sheet, click Extensions, then Apps Script. Delete any existing code and paste this:
function sendDateReminders() {
var sheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var lastRow =
sheet.getLastRow();
if (lastRow < 2) return;
// === CONFIGURATION ===
var dateColumn = 5; // column E: the date to watch
var emailColumn = 3; // column C: who to email
var nameColumn = 1; // column A: name/identifier
var reminderSentColumn = 8; // column H: "Reminder Sent"
flag
var daysBeforeDue = 3; // send reminder this many days before
the date
var alsoSendIfOverdue = true;
// === END CONFIGURATION ===
var data = sheet.getRange(2, 1,
lastRow - 1, reminderSentColumn).getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
for (var i = 0; i < data.length;
i++) {
var targetDate = data[i][dateColumn
- 1];
var email = data[i][emailColumn -
1];
var name = data[i][nameColumn -
1];
var alreadySent = data[i][reminderSentColumn
- 1];
if (!targetDate || !(targetDate
instanceof Date)) continue;
if (!email ||
!email.toString().includes('@')) continue;
if (alreadySent) continue;
var target = new
Date(targetDate);
target.setHours(0, 0, 0, 0);
var daysUntil =
Math.floor((target - today) / (1000 * 60 * 60 * 24));
var shouldSend = false;
var timing = '';
if (daysUntil >= 0 &&
daysUntil <= daysBeforeDue) {
shouldSend = true;
timing = daysUntil === 0 ?
'today' : 'in ' + daysUntil + ' day' + (daysUntil > 1 ? 's' : '');
} else if (daysUntil < 0
&& alsoSendIfOverdue) {
shouldSend = true;
timing = Math.abs(daysUntil) +
' day' + (Math.abs(daysUntil) > 1 ? 's' : '') + ' ago';
}
if (shouldSend) {
var dateStr =
Utilities.formatDate(target, Session.getScriptTimeZone(), 'MMMM d, yyyy');
MailApp.sendEmail({
to: email,
subject: 'Reminder: ' + name
+ ' due ' + timing,
body: 'Hi,\n\nThis is a
reminder that "' + name + '" is due ' + timing + ' (' + dateStr +
').\n\nPlease take any necessary action.\n\nThank you.'
});
sheet.getRange(i + 2,
reminderSentColumn).setValue('Sent ' + new Date().toLocaleDateString());
}
}
}Step 3: Configure the script
Update the configuration block:
- dateColumn: the column number (A=1, B=2, etc.) containing the dates to watch
- emailColumn: the column with the recipient's email
- nameColumn: the column with a name or label for each row
- reminderSentColumn: an empty column where the script will log that a reminder was sent
- daysBeforeDue: how many days before the date to send the reminder
- alsoSendIfOverdue: set to true if you want reminders for dates that already passed
Step 4: Set up the daily trigger
Click the clock icon in the Apps Script sidebar. Click "Add Trigger." Set the function to sendDateReminders, event source to "Time-driven," type to "Day timer," and pick a time window (7am to 8am works for most people).
Step 5: Test it
Add a test row with a date that's today or tomorrow. Run the script manually from the editor (select sendDateReminders, click Run). Check that the email arrives and the "Reminder Sent" column gets filled in.
Customizing the reminder
Change the email content. Edit the subject and body strings in the script. You can include any data from the row by referencing data[i][columnNumber - 1]. Send to yourself instead of the row's email. Change the "to" field to a fixed address. Useful when you want an internal reminder rather than a client-facing one. Send reminders at multiple intervals. Instead of one reminder at 3 days, send at 7 days, 3 days, and day-of. Change the reminderSentColumn to track which reminder was sent (e.g., "7-day sent," "3-day sent") and adjust the conditions accordingly. Send only on weekdays. Add a check at the top of the function: if (today.getDay() === 0 || today.getDay() === 6) return; This skips Saturdays and Sundays.
Common issues
Reminders keep sending every day for the same row. The "Reminder Sent" column prevents this. Make sure reminderSentColumn points to the right column number. If the column already has data in it (even a space), the script will skip that row. Dates show as numbers in the email. Make sure your date column is formatted as Date in the sheet. If the script still gets a number, wrap it in new Date(targetDate) which is already in the code. Script doesn't run on the weekend. Time-driven triggers run every day by default. If you added the weekday-only check, it will skip weekends intentionally. "Cannot read property of undefined." This usually means a column reference is wrong. Column numbers are 1-indexed (A=1, not A=0). Double-check all your configuration values.