Step-by-Step Guide: Setting Up Automated Email Reminders in Google Sheets

This guide will help you automate email reminders for upcoming deadlines using Google Sheets + Google Scripts.

What You’ll Need

✔ A Google Sheet with task deadlines
✔ Google Scripts to automate email alerts
✔ 5 Minutes to set it up ⏳

Step 1: Set Up Your Google Sheet

Create a Google Sheet with the following column headers:

| Task | Assigned To | Due Date | Status ✅ | Email |
|------------|------------|------------|------------|------------|
| Research key topics | Jamie | 2025-02-15 | In Progress | [email protected] |
| Draft first outline | Alex | 2025-02-20 | Not Started | [email protected] |
| Proofreading | Sam | 2025-02-25 | Pending | [email protected] |

Make sure the Due Date is in YYYY-MM-DD format for easy calculations!

Step 2: Open Google Apps Script

1️⃣ In Google Sheets, click on Extensions → Apps Script.
2️⃣ Delete any code inside the editor and copy & paste the script below.

Step 3: Copy & Paste the Email Reminder Script

```javascript
function sendEmailReminders() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change "Sheet1" to match your sheet name
var data = sheet.getDataRange().getValues();
var today = new Date();

for (var i = 1; i < data.length; i++) {
var dueDate = new Date(data[i][2]); // Column C: Due Date
var email = data[i][4]; // Column E: Email
var status = data[i][3]; // Column D: Status

// If the task is NOT completed, send reminders 7 days, 3 days, and 1 day before  

if (status !== "Completed") {  

  var daysLeft = Math.ceil((dueDate - today) / (1000 * 60 * 60 * 24));


  if (daysLeft === 7 || daysLeft === 3 || daysLeft === 1) {  

    MailApp.sendEmail({  

      to: email,  

      subject: "Reminder: Upcoming Task Due Soon!",  

      body: "Hi " + data[i][1] + ",\n\nThis is a reminder that your task **" + data[i][0] + "** is due on **" + data[i][2] + "**.\n\nKeep up the great work!\n\n- Your Team Project Tracker"  

    });  

  }  

}

}
}
```

Step 4: Set Up Automatic Scheduling (Triggers)⏰

To send reminders automatically, follow these steps:

1️⃣ In the Apps Script editor, click Triggers (Clock Icon) on the left sidebar.
2️⃣ Click "Add Trigger" (bottom right corner).
3️⃣ Select sendEmailReminders under “Function to Run.”
4️⃣ Under “Select Event Source”, choose Time-driven.
5️⃣ Under “Select Type of Time-based Trigger”, choose “Daily” to send emails once a day.
6️⃣ Click Save.

Now, Google Sheets will automatically send email reminders every day!

Step 5: Test the Script

1️⃣ Click Run ▶️ in the Apps Script editor.
2️⃣ Check your inbox for a test email.
3️⃣ Verify that team members receive reminders 7 days, 3 days, and 1 day before the due date.

Next: Read 1200+ student guides covering all aspects of student life.

❤ If you liked all this, consider supporting us by checking out Tiny Skills - 250+ Top Work & Personal Skills Made Easy