Automated Email – Simple Text

This post includes the Google Sheets interface and Apps Script code discussed in the YouTube video. For detailed explanations on how to use Apps Script and other specifics, please refer to the YouTube video below.

Google Sheet

Create Columns

Each column will contain the information needed to send the emails. For example, you may have columns like “Name,” “Email,” and a “Sent” column that tracks whether the email has been sent. Input columns (green) are “name” and “email” and output (yellow) is “sent”.

---

Apps Script

Script Code

Here’s a script that will send emails to recipients who haven’t received an email yet, and update the “Sent” column afterward.

You can further customize the subject (the subject line of the email) and body (the content of the email) in the code as per your specific needs. In the provided script, subject and body are highlighted in red in the code, and you can replace the text inside them with your own custom message.

function sendEmail() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet
  if (!sheet) {
    Logger.log("Sheet not found. Please check the sheet name.");
    return;
  }

  var nameRange = sheet.getRange("A2:A" + sheet.getLastRow()); // Names
  var names = nameRange.getValues();

  var emailRange = sheet.getRange("B2:B" + sheet.getLastRow()); // Email addresses
  var emails = emailRange.getValues();
  
  var emailSentRange = sheet.getRange("E2:E" + sheet.getLastRow()); // Sent status
  var emailSentValues = emailSentRange.getValues();

  const subject = "Notice of Due Date"; // subject ++++++++++++++++++++++++++++++++++++++++++++++++
  const timeZone = Session.getScriptTimeZone();

  for (var i = 0; i < emails.length; i++) {
    const name = names[i][0];
    const email = emails[i][0];
    const sentStatus = (emailSentValues[i][0] || "").toLowerCase();

    // Skip entries where email is invalid or already marked as sent
    if (!email.includes("@") || sentStatus.includes("sent")) continue;

    // Customize   ++++++++++++++++++++++++++++++++++++++++++++++++++
    const Message = ` 
      Hello ${name}. 
      The due date is 04/15/2025.
      Thanks.
    `;

    try {
      // Send the email
      MailApp.sendEmail({
        to: email,
        subject: subject,
        body:Message
      });

      // Log the timestamp in the sheet, marking it as sent
      const timestamp = Utilities.formatDate(new Date(), timeZone, "MMM dd, yyyy HH:mm");
      sheet.getRange("E" + (i + 2)).setValue(`Sent at: ${timestamp}`);
      Logger.log("Email sent to: " + email);

    } catch (error) {
      Logger.log("Error sending email to " + email + ": " + error.message);
    }
  }
}