0

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 a quick demo sheet

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('someone@gmail.com',( 'monthly pulse check for month ' + pulsemonth), '',
                     {htmlBody: emailbody.getContent(),
      name: 'test system', 
      from: 'alias@gmail.com', 
      replyTo: 'alias2@gmail.com',
})
}

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>&nbsp;TEXT2</td> <td>&nbsp;TEXT</td> <td><a href="ynet.co.il">&nbsp;NAME</a></td> </tr>'
  }
  var name =  spreadsheet.getRange(i+2, 1).getValue();
  var namelink = 'google.com';
  
  var pulseHTML = pulseHTML + '  <tr> <td>&nbsp;1</td> <td>&nbsp;2</td> <td><a href="youtube.com">&nbsp;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?

user13708028
  • 315
  • 3
  • 13
  • 1
    I think it's likely something wrong with your function `buildmonthlypulse(pulsemonth)` for one thing I'd change your range `spreadsheet.getRange(2,1,spreadsheat.getLastRow()-1,3).getValues();` because with it the way you have it your likely getting a lot of extra null lines at the bottom. – Cooper Dec 14 '20 at 19:42
  • Try building your text with `Utilities.formatString()` – Cooper Dec 14 '20 at 19:45
  • Thanks for this input. I think I have a handle on this now, apart from the following: I'm trying to "build" a link to a specific range inside a Sheet. So, I'm building something like this https://docs.google.com/spreadsheets/d/1XXXXXX/edit#gid=99999999&range=A2:D2 The build is working fine but when it runs through the template and evaluate process something is lost and the "&" turns into "%E2%A6%A5". Any thoughts? – user13708028 Dec 15 '20 at 08:48

1 Answers1

4

I tried to replicate your code and found some issues:

  1. In your function buildmonthlypulse(), you declared var pulseHTML multiple times inside the for loop which is not accessible to the return statement.
  2. In your HTML file, you are using Printing scriptlets <?= ... ?> which is use for printing values with contextual escaping. The html elements in your var pulseHTML are recognized as string.

Solution:

  1. Declare var pulseHTML = '' above the for loop statement (just like the commented one) and inside the for loop, remove var and retain pulseHTML. Example: pulseHTML = pulseHTML + '</tbody> </table>';
  2. Instead of using Printing scriptlets "<?= ... ?>" use Force-printing scriptlets <?!= ... ?>. It is the same with Printing scriptlets but avoid contextual escaping.

Example Output:

Using Printing scriptlets

enter image description here

Using Force-printing scriptlets

enter image description here

Nitpick:

  • Replace <body> with <tbody> in the pulseHTML = '<table style="float: right;"> <body> <tr>.... of your code.
  • Proper indentation.

References:

Nikko J.
  • 5,319
  • 1
  • 5
  • 14
  • Thanks for this input. I think I have a handle on this now, apart from the following: I'm trying to "build" a link to a specific range inside a Sheet. So, I'm building something like this https://docs.google.com/spreadsheets/d/1XXXXXX/edit#gid=99999999&range=A2:D2 The build is working fine but when it runs through the template and evaluate process something is lost and the "&" turns into "%E2%A6%A5". Any thoughts? – user13708028 Dec 15 '20 at 16:22
  • @user13708028 - Kindly create a separate post for it as it is not related to the original post. You can link this post to your new post for reference. – Nikko J. Dec 15 '20 at 16:43
  • Understood. Here's a link to the new post https://stackoverflow.com/questions/65311615/html-lost-during-evaluation-process – user13708028 Dec 15 '20 at 18:28