I have a script to send an email line-by-line. The problem I have is that I cannot use the data in the current line iteration in an email template. I know how to call functions from the template though in this case I cannot call the function that is being evaluated.
My solution was to create another function that would loop through the data again and send the line instance in an email then break. The problem is that now it takes to long to loop through the lines twice when im sure it can be done once.
I hope i made sense.
Code.gs
function sendMail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('EmailList');
// get the data as an array
var data = sheet.getDataRange().getValues();
// Loop over each line
for (var i = 1; i < data.length; i+=1) {
// Check the status of the email
if (data[i][4] != 'Sent') {
// get html file content
var html = HtmlService.createTemplateFromFile('Index').evaluate().getContent();
// send the email
MailApp.sendEmail({
to: data[i][2],
subject: 'Hi ' + data[i][0],
htmlBody: html
});
// Set the status to sent
sheet.getRange(i + 1,5).setValue('Sent');
}
} // end for
} // end function sendMail
function getData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('EmailList');
// get the data as an array
var data = sheet.getDataRange().getValues();
// Loop over each line
for (var i = 1; i < data.length; i+=1) {
// Check the status of the email
if (data[i][4] != 'Sent') {
var firstName = data[i][0];
var lastName = data[i][1];
var email = data[i][2];
var message = data[i][3];
break;
}
} // end for
var returnData = [firstName, lastName, email, message];
return returnData;
} // end function getData
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<? var data = getData(); ?>
</head>
<body>
Hi <?= data[0];?> <?= data[1];?> , with email address <?= data[2];?>
I have a message for you:
<?= data[3];?>
</body>
</html>
I have a script to send an email line-by-line. The problem I have is that I cannot use the data in the current line iteration in an email template. I know how to call functions from the template though in this case I cannot call the function that is being evaluated.
My solution was to create another function that would loop through the data again and send the line instance in an email then break. The problem is that now it takes to long to loop through the lines twice when im sure it can be done once.
I hope i made sense.
Code.gs
function sendMail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('EmailList');
// get the data as an array
var data = sheet.getDataRange().getValues();
// Loop over each line
for (var i = 1; i < data.length; i+=1) {
// Check the status of the email
if (data[i][4] != 'Sent') {
// get html file content
var html = HtmlService.createTemplateFromFile('Index').evaluate().getContent();
// send the email
MailApp.sendEmail({
to: data[i][2],
subject: 'Hi ' + data[i][0],
htmlBody: html
});
// Set the status to sent
sheet.getRange(i + 1,5).setValue('Sent');
}
} // end for
} // end function sendMail
function getData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('EmailList');
// get the data as an array
var data = sheet.getDataRange().getValues();
// Loop over each line
for (var i = 1; i < data.length; i+=1) {
// Check the status of the email
if (data[i][4] != 'Sent') {
var firstName = data[i][0];
var lastName = data[i][1];
var email = data[i][2];
var message = data[i][3];
break;
}
} // end for
var returnData = [firstName, lastName, email, message];
return returnData;
} // end function getData
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<? var data = getData(); ?>
</head>
<body>
Hi <?= data[0];?> <?= data[1];?> , with email address <?= data[2];?>
I have a message for you:
<?= data[3];?>
</body>
</html>
-
I can't see two
loopshere? – iJay Commented Jan 21, 2016 at 3:56 - Hi @iJay sorry i didn't include my full code. I've updated it now. This code above works for me on this scale but once I take this approach and apply it to my real work large scale, it exceeds the Google Apps Script time execution limit. – Justin Kaese Commented Jan 21, 2016 at 4:08
2 Answers
Reset to default 5You need to read this pushing variables to templates
Your Code.gs
for (var i = 1; i < data.length; i+=1) {
// Check the status of the email
if (data[i][4] != 'Sent') {
var firstName = data[i][0];
var lastName = data[i][1];
var email = data[i][2];
var message = data[i][3];
var returnData = [firstName, lastName, email, message];
var html = HtmlService.createTemplateFromFile('Index');
html.data = returnData ;
var template = html.evaluate().getContent();
// send the email
MailApp.sendEmail({
to: data[i][2],
subject: 'Hi ' + data[i][0],
htmlBody: template
});
// Set the status to sent
sheet.getRange(i + 1,5).setValue('Sent');
}
Your Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
Hi <?= data[0];?> <?= data[1];?> , with email address <?= data[2];?>
I have a message for you:
<?= data[3];?>
</body>
</html>
Here is the strategy I would use in order to cut down on the processing time. First get all the data that you want to inject into the HTML. Then get the HTML from the file just once, and put it outside of the for loop. Then construct new HTML to be injected into the original HTML for every loop. You will need to use string formulas, and concatenate text to dynamically change the HTML.
You won't look up the data from the spreadsheet on every loop, or have the templated HTML evaluate the HTML on every loop. You'll get the data once. It will be store in an array. You'll get the HTML once, and it will be stored in a variable.
You'll need to use JavaScript string methods to manipulate the HTML.