Automating Google Sheets to Send Email Reminders

Automating Google Sheets to Send Email

Google Sheets, part of the Google Workspace suite, offers robust capabilities, especially when combined with Google Apps Script. One powerful use case is sending renewal reminders, ensuring timely notifications without manual intervention. In this guide, we’ll demonstrate automating reminder emails using Google Apps Script.

---

Setting Up Your Google Sheet:

Open Google Sheets and in the first row add your column names. For our example follow the below structure

• Column Structure:
  • Customer Name
  • Policy Number
  • Email Address
  • Date of Renewal (or Event)
  • Status

It’s essential to maintain a consistent structure, so the automation script knows where to find the required data.

Accessing the Script Editor

• Click on Extensions and select Apps Script from your Google Sheet.

---

Scripting the Automation

In the Script Editor, input the following code:

function sendReminders() {
  // Get the active sheet from the current spreadsheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Get the number of rows containing data in the sheet.
  var lastRow = sheet.getLastRow();

  // Get the current date.
  var currentDate = new Date();

  // Loop through each row starting from the second row (assuming the first row contains headers).
  for (var i = 2; i <= lastRow; i++) {
    
    // Get the status from the fifth column (E) of the current row.
    var status = sheet.getRange(i, 5).getValue();
    
    // Get the date of the renewal or event from the fourth column (D) of the current row and convert it to a Date object.
    var eventDate = new Date(sheet.getRange(i, 4).getValue());
    
    // Calculate the number of days until the event/renewal date.
    var daysToEvent = Math.round((eventDate - currentDate) / (1000 * 60 * 60 * 24));

    // Check if the event/renewal is in 10 days and if the reminder has not been sent yet.
    if (daysToEvent === 10 && status !== "Sent") {
      
      // Fetch customer name from the first column (A) and the email from the third column (C).
      var customerName = sheet.getRange(i, 1).getValue();
      var email = sheet.getRange(i, 3).getValue();
      
      // Define the email's subject and body.
      var subject = "Renewal Reminder";
      var body = "Hello " + customerName + ",\n\n" +
                 "This is your renewal reminder. Please take necessary action.\n\nThank you!";
      
      // Use the MailApp service to send the email.
      MailApp.sendEmail(email, subject, body);
      
      // Update the status in the fifth column (E) to "Sent" indicating that the reminder has been sent.
      sheet.getRange(i, 5).setValue("Sent");
    }
  }
}

Code Explanation:

• The script scans each row of the sheet.
• It calculates the difference between the current date and the renewal/event date.
• If this difference matches the reminder timeframe (e.g., 10 days) and the status is not “Sent”, it sends an email and updates the status.

Scheduling the Script

To run the script daily or at your preferred interval:

• In the Apps Script dashboard, click the clock icon on the left.
• Click the “+” button at the lower right.
• Choose the sendReminders function.
• Set the event source to “Time-driven”. Define your preferred frequency.

Permissions

On the first run or share, permissions are required:

• Google Apps Script will request access to manage spreadsheets in Google Drive and send emails on your behalf. Grant these permissions to allow the script to function.

---

💁 Check out our other articles😃

 👉  Create Stunning Water Droplet Animation with JavaScript, HTML, and CSS: Step-by-Step Guide

 👉  Creating a Toggle Switcher with Happy and Sad Faces using HTML, CSS, and JavaScript

Testing

Always test before fully automating:

• Add a few sample rows in your Google Sheet with upcoming renewal dates.
• Run the script manually by selecting the sendReminders function and clicking the play button in the Google Apps Script editor.
• Check the respective email addresses to see if they received the reminder.

---

Customizations

This script offers a foundation. You can:

• Set different reminder timeframes or multiple reminders.
• Customize email content.
• Integrate with other Google services or APIs.

---

Conclusion

Automating renewal reminders through Google Sheets and Apps Script is efficient, eliminating the need for manual monitoring and intervention. With this guide, businesses can ensure timely notifications, fostering improved client relations and operational effectiveness.