Last active
February 24, 2020 19:11
-
-
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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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