Skip to content

Instantly share code, notes, and snippets.

@ericksli ericksli/Mail merge.csv
Last active Apr 13, 2019

Embed
What would you like to do?
Google Apps Script mail merge
<p>Dear <?= name ?>,<p>
<p>Congratulation! You won our smartphone giveaway. Your <?= prize ?> will be shipped to you within one month.</p>
Email Name Prize Sent By Sent At
test1@example.com Atlas Cantu Google Pixel 3
test2@example.com Keir James Huawei Mate 20 Pro
test3@example.com Rhianne Kendall Samsung Galaxy Note 9
test4@example.com Lillie-Rose Hahn Sony Xperia XZ3
test5@example.com Leyton Zamora LG G7 ThinQ
function sendEmail() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Mail merge');
var range = sheet.getRange(2, 1, sheet.getMaxRows() - 1, 5);
var values = range.getValues();
var template = HtmlService.createTemplateFromFile('email');
for (var row in values) {
var rowValues = values[row];
var rowIndex = parseInt(row) + 2;
// Early termination when no email address found in a row
if (isCellEmpty(rowValues[0])) {
return;
}
// Skip sent row
if (!isCellEmpty(rowValues[4])) {
continue;
}
template.name = rowValues[1];
template.prize = rowValues[2];
var html = template.evaluate().getContent();
var subject = rowValues[1] + ', you are the winner!'
GmailApp.createDraft(rowValues[0], subject, html, {
htmlBody: html
});
// Write the user's email address and timestamp to that row
sheet.getRange(rowIndex, 4).setValue(Session.getActiveUser().getEmail());
sheet.getRange(rowIndex, 5).setValue(new Date());
}
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.