Trying to build a report to be sent via email. My idea was to build the email using an HTML template, but I seem to be missing out on something... The email should contain a report which will include all entries with a specific month in ColB.
Here's my code
function MonthlyPulseCheck() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var pulsedate = new Date("02/02/2022");
var pulsemonth = pulsedate.getMonth();
var Pulsetable = buildmonthlypulse(pulsemonth);
var emailtemplate = HtmlService.createTemplateFromFile('Monthly pulse check');
emailtemplate.OOOPULSE = Pulsetable;
var emailbody = emailtemplate.evaluate();
GmailApp.sendEmail('[email protected]',( 'monthly pulse check for month ' + pulsemonth), '',
{htmlBody: emailbody.getContent(),
name: 'test system',
from: '[email protected]',
replyTo: '[email protected]',
})
}
function buildmonthlypulse(pulsemonth) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var Pulsearray = spreadsheet.getRange('A2:C').getValues();
var i = 0;
var counter = 1;
var imax = 15;//Pulsearray.length;
for (var i = 0; i < imax; i++) {
var arraydate = new Date(Pulsearray[i][1]);
var arraymonth = arraydate.getMonth();
//var pulseHTML = 0;
//var checkdate = new Date(arraydate);
if (arraymonth === pulsemonth) {
if (counter === 1) {
var pulseHTML = '<table style="float: right;"> <body> <tr> <td> TEXT2</td> <td> TEXT</td> <td><a href="ynet.co.il"> NAME</a></td> </tr>'
}
var name = spreadsheet.getRange(i+2, 1).getValue();
var namelink = 'google.com';
var pulseHTML = pulseHTML + ' <tr> <td> 1</td> <td> 2</td> <td><a href="youtube.com"> 3</a></td> </tr>';
var counter = counter + 1;
}
}
if (counter != 1) {
var pulseHTML = pulseHTML + '</tbody> </table>';
}
return pulseHTML;
}Here's the HTML (used as a template)
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<?=OOOPULSE?>
</body>
</html>email is sent with just the digit 0 in the body. Can anyone make sense of this?
I tried to replicate your code and found some issues:
function buildmonthlypulse(), you declaredvar pulseHTMLmultiple times inside thefor loopwhich is not accessible to the return statement.<?= ... ?>which is use for printing values with contextual escaping. The html elements in yourvar pulseHTMLare recognized as string.Solution:
var pulseHTML = ''above the for loop statement (just like the commented one) and inside the for loop, removevarand retainpulseHTML. Example:pulseHTML = pulseHTML + '</tbody> </table>';"<?= ... ?>"use Force-printing scriptlets<?!= ... ?>. It is the same with Printing scriptlets but avoid contextual escaping.Example Output:
Using Printing scriptlets
Using Force-printing scriptlets
Nitpick:
<body>with<tbody>in thepulseHTML = '<table style="float: right;"> <body> <tr>....of your code.References: