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);
}
}
}
}