Full Code
The only function to call from a menu or button is the CreateMailingList button. Be aware that is merely an example with hard-coded values for getRange().
// column indexes
const C_NAME = 0;
const C_EMAIL = 1;
const C_RESPONDED = 2;
const C_SENT = 3;
/**
* Creates a list with email addresses to mail to
* Asks user to send personalized email to each recipient
*/
function CreateMailingList() {
// get reference to sheet by name
let mailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Maillist");
// our table is row 2 to 15, with 4 columns, this fetches our entire table
let range = mailSheet.getRange(2,1,16,4);
// get data from the selected cell range
let data = range.getValues();
// get valid list of email addresses
// having a "yes" in the sent column makes the address invalid
let validRows = GatherValidEmailAddresses(data);
MailToList(validRows);
}
/**
* Check if email is valid and has not been sent or responded to yet
* @param {Array[][]} data - Table range
* @returns {Array} row data with valid email address
*/
function GatherValidEmailAddresses(data) {
let validList = [];
for (let row = 0; row < data.length; row++) {
let email = data[row][C_EMAIL];
let sent = data[row][C_SENT];
let responded = data[row][C_RESPONDED];
if (!email.toString().length > 0) continue;
if (!email.includes("@")) continue;
if (sent.toString().toLowerCase().trim() === "yes") continue;
if (responded.toString().toLowerCase().trim() === "yes") continue;
validList.push(data[row]);
}
return validList;
}
/**
* Send email to list
* @param {Array} validRows - Row from the Maillist table
*/
function MailToList(validRows) {
if (validRows == undefined || validRows == null) {
SpreadsheetApp.getActive().toast("No valid email addresses found.");
return;
}
// build mail string for verification
var mailString = "";
validRows.forEach(element => mailString = mailString.concat(element[C_NAME], ": ", element[C_EMAIL], ", \n"));
var result = SpreadsheetApp.getUi().alert("You're about to send emails to: "+validRows.length +" persons: \n"+mailString, SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
if(result === SpreadsheetApp.getUi().Button.CANCEL) {
SpreadsheetApp.getActive().toast("Canceled. No emails were sent!");
}
else {
for(var i = 0; i < validRows.length; i++) {
// set placeholders in subject and body
let replaceArray =
[
{find: "[name]", replacement: validRows[i][C_NAME], empty: "acquaintance"},
{find: "[email]", replacement: validRows[i][C_EMAIL], empty: "unknown"},
];
let replaced = ComposeEmail(replaceArray);
MailApp.sendEmail({
to: validRows[i][C_EMAIL],
subject: replaced.subject,
htmlBody: replaced.body
});
}
}
}
/**
* Fetch email subject and body from Settings sheet
* Set placeholders to actual data from table
* @param {Array} validRows - Row of the table, validated to mail to
* @returns {Object} {subject: subject content, body: body content}
*/
function ComposeEmail(replaceArray) {
let settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
let emailSubject = settingsSheet.getRange("B2").getValue().toString();
let emailBody = settingsSheet.getRange("B3").getValue().toString();
replacedEmailSubject = ReplaceStrings(emailSubject, replaceArray);
replacedEmailBody = ReplaceStrings(emailBody, replaceArray);
return {subject: replacedEmailSubject, body: replacedEmailBody};
}
/**
* replaces all 'find' with 'replacement' or 'empty' when replacement has no length
*/
function ReplaceStrings(targetText, replaceArr) {
for (var i = 0; i < replaceArr.length; i++) {
try {
if (replaceArr[i].replacement.length > 0) {
targetText = targetText.replaceAll(replaceArr[i].find, replaceArr[i].replacement);
}
else {
targetText = targetText.replaceAll(replaceArr[i].find, replaceArr[i].empty);
}
}
catch(error) {
targetText = targetText.replaceAll(replaceArr[i].find, replaceArr[i].empty);
}
}
return targetText;
}
