How to Automatically Send Email Reminders for Overdue Dates in Google Sheets

Tracking task due dates, invoice payments, or client deliverables manually in a spreadsheet is time-consuming and prone to human error. Even with conditional formatting highlighting overdue items in bright red, team members still have to open the sheet daily, filter for late records, and compose manual reminder emails.

By connecting Google Sheets to your Gmail account using Google Apps Script, you can build an automated notification system. This guide demonstrates how to scan your sheet daily, identify rows where a due date has passed, send tailored email reminders automatically, and log the dispatch timestamp so recipients aren't emailed twice.

Step 1: Structure Your Task Tracker Sheet

To ensure your script reads cell data correctly, organize your tracker sheet (named Task_Tracker) with clear column headers in Row 1:

• Column A: Task Name
• Column B: Assignee Name
• Column C: Assignee Email
• Column D: Due Date
• Column E: Status (e.g., Pending, Completed)
• Column F: Email Sent Date (Updated by script automatically)

Step 2: Write the Automated Overdue Email Script

Open the Apps Script editor via Extensions > Apps Script, delete any default text, and paste the following code:

function sendOverdueEmailAlerts() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Task_Tracker");
  var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, 6);
  var data = dataRange.getValues();
  
  var today = new Date();
  today.setHours(0, 0, 0, 0); // Normalize time to midnight for accurate date comparison
  
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var taskName      = row[0];
    var assigneeName  = row[1];
    var assigneeEmail = row[2];
    var dueDate       = new Date(row[3]);
    var status        = row[4];
    var emailSent     = row[5];
    
    dueDate.setHours(0, 0, 0, 0);
    
    // Check if task is overdue, pending, and hasn't had an alert sent today
    if (status !== "Completed" && dueDate < today && emailSent === "") {
      
      var subject = "OVERDUE TASK REMINDER: " + taskName;
      var body = "Hi " + assigneeName + ",\n\n" +
                 "This is an automated reminder that your task '" + taskName + "' was due on " + 
                 dueDate.toLocaleDateString() + " and is currently marked as OVERDUE.\n\n" +
                 "Please update the task status in the shared tracking sheet as soon as possible.\n\n" +
                 "Best regards,\nOperations Team";
                 
      // Send notification via Gmail App
      MailApp.sendEmail(assigneeEmail, subject, body);
      
      // Stamp the current date/time in Column F to avoid duplicate emails
      sheet.getRange(i + 2, 6).setValue(new Date().toLocaleString());
    }
  }
}

Step 3: Set Up Daily Automated Execution (Triggers)

To make the system check for overdue rows every morning without manual intervention:

1. In the left menu of the Apps Script editor, click the Triggers icon (clock symbol).
2. Click + Add Trigger in the bottom right corner.
3. Set the parameters:
   • Which function to run: sendOverdueEmailAlerts
   • Select event source: Time-driven
   • Select type of time based trigger: Day timer
   • Select time of day: 8am to 9am
4. Click Save and authorize access permissions for your Gmail account.

Best Practices & Quota Limits