Send due-date email reminders from Google Sheets with Apps Script

To send due-date reminders from Google Sheets, add a “Reminder sent” column, paste the Apps Script below and give it a daily time-driven trigger. Each run emails the rows due within three days, or up to a week late, and writes the date into that column, so a row is not emailed twice. It runs free in your Google account. Botlery builds larger Sheets automations for a free fixed-price quote.

Updated October 2026 · 8 min read · By Rohail Nisar, founder of Botlery

Invoices that fall due, certificates that expire, services and renewals: most small businesses track these in a sheet and rely on someone remembering to look at it. A short Apps Script can look for you every morning and email whoever needs reminding. The one detail that makes it trustworthy is a “Reminder sent” column, so a reminder goes out once and is never repeated by the next day’s run.

Step 1: set up the sheet

Make a tab called Reminders with these four headers in row 1. They can be in any order, and you can add other columns (notes, amount, owner) anywhere, because the script finds its columns by header name.

• Due date must hold real dates, not text. Select the column and choose Format › Number › Date; a cell that still reads “next week” is skipped. Formulas such as =B2+30 are fine.
• Email can hold one address or several separated by commas. Each address counts towards Google’s daily quota.
• Reminder sent starts empty. The script writes the date into it after emailing. Anything you type there yourself (“paid”, “skip”) also stops the reminder.

Step 2: paste the script

Open Extensions › Apps Script, replace the sample code with the script below and save. The settings at the top are the only part you normally change.

Apps Script · Code.gs

/**
 * Due-date reminder emails from a Google Sheet, each row emailed once.
 * From botlery.com/blog/google-sheets-email-reminders-apps-script.html
 *
 * Setup: a tab with the four headers below in row 1, then Extensions > Apps Script,
 * paste this file, run sendDueReminders once (to authorise), then run installDailyTrigger.
 *
 * @OnlyCurrentDoc  Limits the script to this one spreadsheet.
 */
const CONFIG = {
  sheetName: 'Reminders',
  columns: { item: 'Item', email: 'Email', due: 'Due date', sent: 'Reminder sent' },
  daysBefore: 3,       // email when the due date is this many days away, or closer
  maxDaysOverdue: 7,   // skip rows that were already this late when first seen
  runHour: 8,          // the daily trigger fires at some point in this hour
  maxPerRun: 40,       // headroom under Google's daily email quota
  replyTo: '',         // optional: where replies should go, e.g. 'office@yourbusiness.com'
};

function sendDueReminders() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) return;                 // a previous run is still going
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(CONFIG.sheetName);
    if (!sheet) throw new Error('No tab called "' + CONFIG.sheetName + '"');
    const tz = ss.getSpreadsheetTimeZone();
    const values = sheet.getDataRange().getValues();
    const col = findColumns(values[0]);
    const today = dayNumber(new Date(), tz);
    let sent = 0;

    for (let r = 1; r < values.length; r++) {
      const row = values[r];
      const due = row[col.due];
      const email = String(row[col.email]).trim();
      if (row[col.sent] !== '' || !email || !(due instanceof Date)) continue;  // done, or nothing to send
      const daysLeft = dayNumber(due, tz) - today;
      if (daysLeft > CONFIG.daysBefore || daysLeft < -CONFIG.maxDaysOverdue) continue;
      if (sent >= CONFIG.maxPerRun || MailApp.getRemainingDailyQuota() < 1) break;

      try {
        MailApp.sendEmail(buildEmail(row, col, email, daysLeft, tz));
        sheet.getRange(r + 1, col.sent + 1).setValue(new Date());
        SpreadsheetApp.flush();                     // save the stamp before the next email
        sent++;
      } catch (err) {
        console.error('Row ' + (r + 1) + ' not sent: ' + err.message);  // stays blank, retried tomorrow
      }
    }
    console.log('Reminders sent: ' + sent);
  } finally {
    lock.releaseLock();
  }
}

function buildEmail(row, col, email, daysLeft, tz) {
  const item = String(row[col.item]).trim() || 'Your item';
  const dueText = Utilities.formatDate(row[col.due], tz, 'EEEE d MMMM yyyy');
  let when;
  if (daysLeft > 1) when = 'is due in ' + daysLeft + ' days';
  else if (daysLeft === 1) when = 'is due tomorrow';
  else if (daysLeft === 0) when = 'is due today';
  else when = 'was due ' + (-daysLeft) + (daysLeft === -1 ? ' day' : ' days') + ' ago';
  const mail = {
    to: email,
    subject: 'Reminder: ' + item + ' ' + when,
    body: 'Hello,\n\nA quick reminder: ' + item + ' ' + when + ' (' + dueText + ').\n\n' +
      'If it is already done, please ignore this email.\n',
  };
  if (CONFIG.replyTo) mail.replyTo = CONFIG.replyTo;
  return mail;
}

// Column positions by header name, so columns can be moved or added freely.
function findColumns(headerRow) {
  const names = headerRow.map((h) => String(h).trim().toLowerCase());
  const col = {};
  Object.keys(CONFIG.columns).forEach((key) => {
    col[key] = names.indexOf(CONFIG.columns[key].toLowerCase());
    if (col[key] === -1) throw new Error('Missing column "' + CONFIG.columns[key] + '" in row 1');
  });
  return col;
}

// Whole days since 1970 in the sheet's time zone: daylight-saving days of 23 or 25 hours don't matter.
function dayNumber(date, tz) {
  const ymd = Utilities.formatDate(date, tz, 'yyyy-MM-dd').split('-').map(Number);
  return Date.UTC(ymd[0], ymd[1] - 1, ymd[2]) / 86400000;
}

// Run once. Replaces any earlier copy of the trigger, so it is safe to run again.
function installDailyTrigger() {
  ScriptApp.getProjectTriggers()
    .filter((t) => t.getHandlerFunction() === 'sendDueReminders')
    .forEach((t) => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger('sendDueReminders').timeBased().everyDays(1).atHour(CONFIG.runHour).create();
}

// Adds a "Reminders" menu to the sheet for a manual run.
function onOpen() {
  SpreadsheetApp.getUi().createMenu('Reminders')
    .addItem('Send due reminders now', 'sendDueReminders')
    .addItem('Install the daily trigger', 'installDailyTrigger')
    .addToUi();
}

How it decides who gets an email

1. Rows with anything in Reminder sent, no email address or no real date are skipped.
2. It counts whole days between today and the due date in the spreadsheet’s own time zone, so a daylight-saving change cannot shift a reminder by a day.
3. Rows due within daysBefore days, or up to maxDaysOverdue days late, get an email. Everything later waits; anything older is left for a person.
4. After each email it writes the date into Reminder sent and calls SpreadsheetApp.flush(), so the stamp is saved before the next email goes. If a run is ever cut short, the rows already emailed are already marked.
5. It stops at maxPerRun emails or when MailApp.getRemainingDailyQuota() reaches zero. The rest go out on the next run.
6. If one address is wrong, that row stays blank, the error is written to the execution log, and the run carries on with the next row.

Step 3: run it once and authorise

Choose sendDueReminders in the function list and press Run. Google asks for permission to view and edit this spreadsheet and to send email as you. The @OnlyCurrentDoc line at the top keeps the request to this one spreadsheet instead of all of yours. The script uses MailApp, which can only send; GmailApp would also ask to read and delete your mail, which a reminder script has no need for. Check the Execution log for “Reminders sent: N” and look at the sheet: the emailed rows now carry today’s date.

Before that first run, put something in Reminder sent for any old rows you do not want emailed. Anything up to a week overdue goes out on the first run, and the people on the list will see it.

Step 4: schedule it every day

Run installDailyTrigger once. It creates a time-driven trigger that runs sendDueReminders every day in the hour set by runHour, and removes any older copy, so running it twice does not double the emails. You can do the same by hand in the Triggers panel (the clock icon): add a trigger for sendDueReminders, event source Time-driven, type Day timer, and pick the hour.

Do not expect 8:00 sharp. Google picks a time inside the hour you choose and then keeps to it, so 24 hours pass between runs (Google’s installable triggers guide). The hour follows the script’s time zone in Project Settings; set it to match the spreadsheet’s (File › Settings) so “8am” means the same thing in both.

The script also adds a Reminders menu to the sheet, so anyone with edit access can send the day’s reminders on demand.

Google’s limits that apply

From Google’s Quotas for Google Services page, last updated 3 September 2026 and checked on 7 October 2026. Google says the limits can change without notice.

For a typical reminder list the email quota is the one that matters. A run that sends 40 emails takes seconds, far inside the six-minute limit. If you regularly need more than 100 reminders a day, that is the point where a Workspace account, or a proper email service, earns its cost.

Common changes

• A second reminder. Add a “Second reminder sent” column and a second pass with a smaller window (for example on the due date itself) that checks and stamps that column instead.
• A different lead time per row. Add a “Days before” column and use it in place of CONFIG.daysBefore when it is filled in.
• Copy yourself in. Add cc: 'you@yourbusiness.com' to the object in buildEmail. It counts as another recipient.
• Replies to the office. Set replyTo in the settings. MailApp sends from the Google account the script runs as, which for the daily run is the trigger’s owner; to send as an alias such as office@, use GmailApp.sendEmail with its from option and an alias already set up in that Gmail account, or run the script under a shared Workspace account.

Pitfalls

• The trigger belongs to one person. Installable triggers run as the account that created them. If that person leaves or loses access to the sheet, the reminders stop. Create the trigger from a shared admin account.
• Sorting during a run. The script reads the sheet once, then stamps rows by position. A run takes a few seconds, but do not sort the tab at that moment.
• Dates typed as text are skipped without an error. If a reminder never arrives, check that the cell is right-aligned (a date) rather than left-aligned (text).
• Executions page. Every run, and every error, is listed under Executions in the Apps Script editor. Look there first when something did not send.

Frequently asked questions