Originally written for bulldo.gs — republished here with the canonical link pointing home.
I want to automatically send email reminders when a date in my Google Sheet matches today, but my comparisons keep firing a day early or not at all.
// Run daily via time-driven trigger; col A = email, col B = due date, col C = task name
function sendDateReminders() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');
var data = sheet.getDataRange().getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
for (var i = 1; i < data.length; i++) {
var email = data[i][0];
var dueDate = new Date(data[i][1]);
dueDate.setHours(0, 0, 0, 0);
if (dueDate.getTime() === today.getTime()) {
var subject = 'Reminder: ' + data[i][2] + ' is due today';
var body = 'This is your reminder that "' + data[i][2] + '" is due today.';
MailApp.sendEmail(email, subject, body);
}
}
}
Why date comparison almost always fires wrong
When Apps Script reads a date cell via getValues(), it returns a JavaScript Date object. That object carries the full timestamp — including a time component that reflects the spreadsheet's timezone offset. A cell you formatted as '2026-06-10' arrives in your script as something like 'Wed Jun 10 2026 00:00:00 GMT-0500', and new Date() for the current moment might be 'Wed Jun 10 2026 09:14:33 GMT-0500'. Comparing those two with === or getTime() will always return false, so your reminder never fires.
The fix is one line per date: call setHours(0, 0, 0, 0) on both the cell value and today's date before comparing. That zeroes out hours, minutes, seconds, and milliseconds, leaving only the calendar date. I keep a small normalizeDate() helper at the top of every sheet-automation file I write; re-typing setHours four times per comparison is where bugs hide.
Once both sides are normalized, use getTime() to compare — it returns an integer (milliseconds since epoch) and === works correctly. Direct Date object comparison with == does not, because two different Date objects are different references even when they represent the same moment.
Sheet layout and reading the data
The script assumes a sheet named 'Tasks' with three columns: column A is the recipient email, column B is the due date (formatted as a date in Sheets, not plain text), and column C is a task name used in the email subject and body.
getDataRange().getValues() returns a two-dimensional array. Row 0 is your header row, so the loop starts at i = 1. Each data[i] is one row; data[i][0] is the email, data[i][1] is the date object, data[i][2] is the task name. If your sheet has columns in a different order, adjust the index numbers accordingly.
One practical note: if column B contains a plain text string ('June 10') instead of a real Sheets date, wrapping it in new Date() will still parse it, but you lose timezone-safe behavior and cross-locale reliability. Format the column as Date in Sheets and let the serialization handle it cleanly.
Setting up the daily trigger
The script does nothing on its own. You need a time-driven trigger to run sendDateReminders() once per day. In the Apps Script editor, go to Triggers (the clock icon), add a new trigger, set the function to sendDateReminders, choose Time-driven, Day timer, and pick a time window like 7am–8am.
Pick a time window that's early enough to be useful but after the spreadsheet's timezone midnight. If your sheet is set to US/Eastern and you run the trigger at 1am UTC (which is 8pm Eastern the previous day), today inside the script will be one calendar day behind what your users expect. Match the trigger's timezone — set under Project Settings — to the sheet's timezone.
MailApp.sendEmail() counts against your daily Gmail quota: 100 emails per day for personal accounts, 1,500 for Google Workspace. If you have more than a few hundred rows, batch sends or switch to GmailApp.sendEmail() with an alias. For most reminder sheets, MailApp is fine.
FAQ
Why does my reminder send a day early?
The date cell arrives as a Date object with a non-zero time component. When you compare it to today without zeroing both sides with setHours(0,0,0,0), a cell storing midnight of the target date is numerically less than today's timestamp mid-morning — so the comparison passes a day before it should.
Can I send reminders X days before the due date instead of on the day itself?
Yes. After normalizing today with setHours(0,0,0,0), add the offset: var target = new Date(today.getTime()); target.setDate(target.getDate() + 3); then compare dueDate.getTime() === target.getTime(). Adjust the number to however many days ahead you want the reminder.
My script runs but no emails arrive — what should I check first?
Run sendDateReminders() manually from the editor and watch the execution log. The most common causes are: (1) the sheet name in getSheetByName() doesn't match exactly (case-sensitive), (2) column B contains text strings instead of real date values, or (3) the script hasn't been granted Gmail permission yet — the first manual run prompts for OAuth authorization that a trigger won't prompt for on its own.
Does this work if recipients are different people per row?
Yes. MailApp.sendEmail() accepts any valid address in column A — it sends a separate email per matching row. Each call is independent, so different rows can have different recipients, subjects, and body text.
Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.
