Automated Email- If condition

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. Attachment files should be uploaded to Google Drive, and their shareable links must be obtained to use them in Apps Script. By using the if statement, you can tailor emails to suit a variety of situations. You can choose different languages or send customized messages for specific recipients.

Google Sheet

Create Columns:

Each column will include the necessary information for sending the emails. For example, the columns might be labeled “Name,” “Email,” “Condition” (for group messages, such as A or B), “Message-Special Note” (for personalized messages), and a “Sent” column to track whether the email has been sent.

Input columns (green) are “Name,” “Email,” “Condition”, and “Message-Special Note”.

---

Apps Script code

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.

The message starts with common message(Hello, this is a general notification), next message depends on groups, next one on special note and ends with the common (Thank you for your attention)

function sendEmailCondition() {
  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 condRange = sheet.getRange("C2:C" + sheet.getLastRow()); // Condition column
  var conds = condRange.getValues();

  var msgRange = sheet.getRange("D2:D" + sheet.getLastRow()); // Message content
  var messages = msgRange.getValues();

  var emailSentRange = sheet.getRange("E2:E" + sheet.getLastRow()); // Sent status column
  var emailSentValues = emailSentRange.getValues();

  const timeZone = Session.getScriptTimeZone(); // Time zone for logging timestamp

  for (let i = 0; i < emails.length; i++) { // Iterate over all rows
    const name = names[i][0];
    const email = emails[i][0];
    const cond = conds[i][0];
    const message = messages[i][0];
    const sentStatus = (emailSentValues[i][0] || "").toLowerCase();

    // Check if email is valid and if sentStatus does not contain "sent"
    if (email && email.includes("@") && !sentStatus.includes("sent")) {
      // Common HTML message  //************************************ Common Message*************************
      let htmlMessage = `                             
        <div>  <p>Hello ${name}, <br> this is a general notification.</p> </div>
      `;

      // Append different paragraphs based on condition value
      if (cond === "A") {   //************************************ To Group A *************************                                                         
        htmlMessage += `                                                                 
          <div>  <h2>To Group A </h2>  <p>This is a special message for group A. Please take action accordingly.</p>  </div>
        `;
      } else if (cond === "B") {  //************************************ To GROUP B *************************                                                      
        htmlMessage += `
           <div>  <h2>To Group B </h2>  <p>This is a special message for group A. Please take action accordingly.</p>  </div>
        `;
      } else {    //******/************************************ To Neither A or B*************************                                                                     
        htmlMessage += `
          <div> <h3>*** Default Information</h3>  <p>This is the default message content. If you need further details, please reach out to us.</p> </div>     
        `;
      }

      // Append the common "Ending" message /**************************   Special Message + Common Message *************************                                              
      htmlMessage += `
        <div>  <h2> ${message}</h2> 
        <p>Thank you for your attention. </p>   </div>                                    
      `;

      const subject = "Conditional Email Notification";

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

        // Log the timestamp in the sheet
        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);
      }
    }
  }
}