Recipe

Send a reminder email before a due date in Google Sheets

Each day, any row whose due date is within three days gets one reminder email, then the Reminded column is stamped.

Set it up

1. Use columns A Task, B Due date (a real date), C Email, D Reminded. Paste the script and set DR.
2. Run sendReminders once and approve the prompts.
3. Triggers > Add trigger > sendReminders > Time-driven > Day timer.

The script

/**
 * Email a reminder a set number of days before a due date.
 * Columns: A Task, B Due date, C Email, D Reminded (filled by the script).
 * Add a daily time-driven trigger for sendReminders.
 */
var DR = { sheetName: 'Sheet1', daysBefore: 3, remindedCol: 4 };

function startOfDay_(d) {
  return new Date(d.getFullYear(), d.getMonth(), d.getDate());
}

function sendReminders(today) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(DR.sheetName);
  var values = sheet.getDataRange().getValues();
  var base = startOfDay_(today || new Date());
  var sent = 0;
  for (var i = 1; i < values.length; i++) {
    var due = values[i][1];
    if (!(due instanceof Date) || values[i][DR.remindedCol - 1]) continue;
    var days = Math.round((startOfDay_(due) - base) / 86400000);
    if (days < 0 || days > DR.daysBefore) continue;
    var to = String(values[i][2]).trim();
    if (!/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(to)) continue;
    MailApp.sendEmail(to, 'Reminder: ' + values[i][0] + ' is due in ' + days + ' day(s)', 'Task: ' + values[i][0]);
    sheet.getRange(i + 1, DR.remindedCol).setValue(new Date());
    sent++;
  }
  return sent;
}

What you should see

Row 2 reminded, row 3 not yet due. Sample values, not output from a real account.

Common errors and fixes

Reminders never send

Column B must contain real dates, not text. Format it as a date.

Service invoked too many times for one day: email

The daily quota is used up. Unsent rows are retried the next day.

The reminder arrived late

Day timers run within an hour window, not at an exact minute.

Permissions. Running this script asks you to grant Google permissions for the services listed above, to your own script. TriggerShelf never receives your Google data. See the permissions table. Google will show "Google hasn't verified this app"; that is expected for a script you paste yourself.

TriggerShelf is independent and not affiliated with or endorsed by Google LLC. Google Sheets, Gmail, Google Drive and Apps Script are trademarks of Google LLC. Quota figures come from Google's Apps Script quotas page. Test on a copy first.