How to Highlight Overdue Tasks in Google Sheets and Send Email Reminders (2026)

A task list in Google Sheets is easy to scan when overdue rows are highlighted. The highlighting only helps if someone has the sheet open. This guide covers both parts: conditional formatting and a days-overdue formula you can set up in a minute, then a daily email so owners hear about overdue work even when the spreadsheet is closed.

Step 1: columns that describe the work

Create a tab named Tasks with at least: Task, Owner email, Due date, Status, and Last reminder. Format Due date with Format › Number › Date. Use Status values such as Open and Done.

In an empty column, header Days overdue, starting in row 2 (adjust the column letters if your due date is not in C and Status is not in D):

=IF(OR($D2="Done",$C2=""),"",IF($C2<TODAY(),TODAY()-$C2,0))

A status label is also useful for filters. In Progress:

=IF($D2="Done","Done",IF($C2="","",IF($C2<TODAY(),"Overdue",IF($C2=TODAY(),"Due today",IF($C2-TODAY()<=7,"Due soon","On track")))))

Step 2: color the rows

Select your data range, starting at row 2 (for example A2:E200). Open Format › Conditional formatting and add one custom-formula rule at a time, in this order so the first match wins:

When	Custom formula	Suggested color
Done	`=$D2="Done"`	Light grey or green
Overdue	`=AND($C2<>"",$C2<TODAY(),$D2<>"Done")`	Light red
Due today	`=AND($C2=TODAY(),$D2<>"Done")`	Amber
Due in the next 7 days	`=AND($C2>TODAY(),$C2-TODAY()<=7,$D2<>"Done")`	Light yellow

Apply the formula to the whole row range, not a single column, if you want the entire task highlighted. Keep a filter view (Data › Filter views) named “Overdue” so people can jump to late work without changing a shared filter.

What Google Sheets will not do on its own

Notification settings (Tools › Notification settings) email you when the sheet is edited or a form is submitted. They do not fire because a due date arrived.
Conditional notifications (Tools › Conditional notifications), available on some work or school accounts, also trigger when a cell value changes. Google's own help notes that volatile functions such as TODAY() do not trigger those notifications when the value changes while the spreadsheet is closed. See Use conditional notifications.

So formatting and formulas are for when someone is looking at the sheet. Email still needs a script or an add-on.

Step 3: email owners of overdue work

Open Extensions › Apps Script, paste the script below, save it, run sendOverdueReminders once to approve permissions, then add a time-driven Day timer trigger.

/**
 * Email owners of overdue, unfinished tasks. Runs daily.
 * Columns: A Task | B Owner email | C Due date | D Status | E Last reminder
 * Status "Done" (any case) is skipped. Last reminder stops daily repeats:
 * a new email is sent when the task first becomes overdue, then at most
 * once every 7 days until it is marked Done.
 * Trigger: Triggers > Add trigger > sendOverdueReminders > Time-driven > Day timer.
 */
function sendOverdueReminders() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Tasks');
  const rows = sh.getDataRange().getValues();
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const tz = Session.getScriptTimeZone();
  const WEEK_MS = 7 * 24 * 60 * 60 * 1000;

  for (let r = 1; r < rows.length; r++) {
    const [task, email, due, status, lastSent] = rows[r];
    if (!email || !(due instanceof Date)) continue;
    if (String(status).toLowerCase() === 'done') continue;
    const dueDay = new Date(due); dueDay.setHours(0, 0, 0, 0);
    if (today < dueDay) continue;               // not overdue (or due) yet
    if (lastSent instanceof Date && (today - lastSent) < WEEK_MS) continue;
    if (MailApp.getRemainingDailyQuota() < 1) break;
    const days = Math.round((today - dueDay) / (24 * 60 * 60 * 1000));
    const when = days === 0
      ? 'due today'
      : (days + (days === 1 ? ' day' : ' days') + ' overdue');
    MailApp.sendEmail(
      email,
      'Overdue: ' + task,
      task + ' is ' + when + ' (due ' +
        Utilities.formatDate(due, tz, 'EEEE, MMM d, yyyy') + ').'
    );
    sh.getRange(r + 1, 5).setValue(Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd'));
  }
}

The Last reminder column is what prevents a daily flood: after the first overdue (or due-today) email, the script waits seven days before sending again, until Status is Done. Clear the cell if you want to send immediately on the next run.

Caveats

Daily email limit. MailApp sends from your account and counts against a daily recipient quota, which is lower on consumer Gmail than on Google Workspace. Google lists the current numbers on its Apps Script quotas page. The script checks getRemainingDailyQuota() before each send.
Real dates only. Text that looks like a date is skipped. Use Format › Number › Date.
Ownership. The trigger runs as the person who created it. If they leave, reminders stop until someone else adds the trigger.
Timezone. Set File › Settings › Time zone to the zone you mean, and keep the script's project time zone in sync.

Want reminders without maintaining a script? SheetReminders (disclosure: we make it) is a Google Sheets add-on that watches a date column and sends scheduled email reminders before, on or after each date, for example 30, 14 and 7 days before a due date, or after a date for overdue follow-ups. Sheet data stays inside your Google account and it uses narrow scopes. 14-day free trial, no credit card required. It is live on the Google Workspace Marketplace.
See SheetReminders
Short on time? Watch the 50-second version

Related Google Sheets guides

Instructions refer to Google Sheets, Google Docs, Google Forms, Google Drive and Apps Script as they worked on November 10, 2026. Not affiliated with Google or any other vendor named here. Google Sheets, Google Docs, Google Forms, Google Slides and Google Drive are trademarks of Google LLC. Features change, so verify in your own account. Questions or corrections: hello@loomwise.app.