Skip to content

Instantly share code, notes, and snippets.

@liyanasahir
Last active February 24, 2020 19:11
Show Gist options
  • Save liyanasahir/9126dc0d6f3c1f2403ed0ab2929b6212 to your computer and use it in GitHub Desktop.
Save liyanasahir/9126dc0d6f3c1f2403ed0ab2929b6212 to your computer and use it in GitHub Desktop.
Google App Script to use form data from spreadsheet to send personalised mails
/**
* Sends non-duplicate emails with data from the current spreadsheet.
*/
// This constant is written in last column(10th in our case) for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process --------> set actual value to 8
var numRows = 3; // Number of rows to process----------> set actual value to 57
// Fetch the range of cells from columns 1 to 10 for the given rows
var dataRange = sheet.getRange(startRow, 1, numRows, 10);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
Logger.log(data);
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[3]; // Fourth column
var name = row[1]; // Second column
var emailSent = row[9]; // tenth column
var imageUrl = "https://images.theconversation.com/files/304864/original/file-20191203-67028-qfiw3k.jpeg?ixlib=rb-1.1.0&rect=638%2C2%2C795%2C745&q=45&auto=format&w=496&fit=clip";
var imageBlob = UrlFetchApp
.fetch(imageUrl)
.getBlob()
.setName("imageBlob");
var htmlBody = "Hi " + name + "!<br>"+
"This is just a testing mail. <br><br> <strong>Heading</strong><br>Some words bla bla bla<br>"+
"<img src='cid:image1' align='middle'><br><br>"+
"Regards<br>Liyana Sahir<br>";
if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
var subject = 'Onnum koodi! :)';
MailApp.sendEmail({
to: emailAddress,
subject: subject,
htmlBody: htmlBody,
inlineImages:
{
image1: imageBlob
}
});
sheet.getRange(startRow + i, 10).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment