I'm basically trying to take data from a Google Spreadsheet, create a HTML email and send it with the data.
I've managed this in the past, here is how.
Code.gs
function getData() {
var sh = SpreadsheetApp.getActive()
.getSheetByName('Form responses 1');
return sh.getRange(sh.getLastRow(), 1, 1, sh.getLastColumn())
.getValues()[0]
}
function testEmail() {
var htmlBody = HtmlService
.createTemplateFromFile('mail_template')
.evaluate()
.getContent();
var mailADdy = "email here";
MailApp.sendEmail({
to:mailADdy,
subject: 'Test Email markup - ' + new Date(),
htmlBody: htmlBody,
});
}
mail_template.html
<html>
<head>
<style>
@media only screen and (max-device-width: 480px) {
/* mobile-specific CSS styles go here */
}
</style>
</head>
<body>
// var data runs the getData function and puts it into an array we can use
<? var data = getData(); ?>
<? var first = data[2]; ?>
<? var second = data[3]; ?>
<? var third = data[4]; ?>
<div class="main">
<p style="text-align: center;"><strong>This is a test HTML email.</strong></p>
<table style="margin-left: auto; margin-right: auto;">
<tbody>
<tr>
<td style="text-align: left;">First question to be put here</td>
<td style="text-align: right;">
<strong><?= first ?></strong>
</td>
</tr>
<tr>
<td style="text-align: left;">Second question here</td>
<td style="text-align: right;">
<?= second ?>
</td>
</tr>
<tr>
<td style="text-align: left;">Third question here</td>
<td style="text-align: right;">
<?= third ?>
</td>
</tr>
<tr>
</tbody>
</table>
<p></p>
</div>
</body>
</html>
We run the testEmail function, in the HTML it calls the getData function and returns a 1D array of data, that we use in the HTML.
This all works fine. But this only looks at the last row of a spreadsheet and sends the email. It doesn't iterate through the rows.
I want to go through several rows of data and send an email per row.
I tried the following, based on my previous code. But it only sends one email. This is the first row in the array.
I've only made adjustments to the getData function, but so far, nothing has worked. Here is where I am at the moment.
function getData(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange(2,1, sheet.getLastRow()-1, sheet.getLastColumn());
var values = range.getValues();
for (i=0; i < values.length ; i++) {
return values[i];
}
}
It's returning an array on every iteration on the loop, only sends one email.
Does anyone know how to fix this or have any pointers?
I did try passing vars into the sendEmail function,
something like
sendEmail(firstName, varA, varB) {
// code here
}
but couldn't work out how to call/use them in the HTML file
Thanks in advance
See Question&Answers more detail:
os 
