Looping into bullet points w/ google script and html template

user_14100454 0
Viewed 620

Issue I am facing:

I have a spreadsheet with cells that will populate an HTML template for emails through Google Apps Script. These cells are linked to a list of bullet points in the HTML template that I would like to vary from 1 - 6 bullets in length, based on whether or not the cells have information in them or not. I am currently struggling to find a way to loop the cells without including blank bullet points in the final email.

I have researched various similar methods but nothing has quite been able to fit with the amount of looping and bulleting necessary in my script.

Here is some of the relevant code:

 template.tweb = web.getRange(1,1).getValue();
 template.tweb1 = web.getRange(2,1).getValue();
 template.tweb2 = web.getRange(3,1).getValue();    
 template.tweb3 = web.getRange(4,1).getValue();
 template.tweb4 = web.getRange(5,1).getValue();
 template.tweb5 = web.getRange(6,1).getValue();

I currently have it set up without a loop because I was unable to get more than the last row to populate the HTML template.

Here is the HTML code:

<p><strong>Website Development</strong></p>
<em>Weekly Highlights</em>
<br>
<ul>
 <li>The Website Development team completed the following:</li>
  <ul><li> <?= tweb ?></li></ul>
  <ul><li> <?= tweb1 ?></li></ul>
  <ul><li> <?= tweb2 ?></li></ul>
  <ul><li> <?= tweb3 ?></li></ul>
  <ul><li> <?= tweb4 ?></li></ul>
  <ul><li> <?= tweb5 ?></li></ul>

Additionally, here is an example of my failed looping code:

var e = pr.getLastRow();
 for (var i = 1; i < e+1 ; i++ ) {
 var tpr = pr.getRange(i,1).getValue();
 }
   template.tpr = tpr;

<p><strong>Public Relations</strong></p>
<em>Weekly Highlights</em>
<ul>
 <li><?= tpr ?></li>
</ul>

Thank you for your help!!

google-apps-script html-email html javascript
1 Answers

In your case the best is to use Apps Script scriptlets

They allow you to create your html dynamically, integrating loops and statements with your html code.

Sample:

Code.gs

function doGet() {
  var template = HtmlService.createTemplateFromFile("index");
  return template.evaluate();
}

function getData() {
  var web = SpreadsheetApp.getActive().getActiveSheet();
  return web.getRange(1,1,6,1).getValues();
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p><strong>Website Development</strong></p>
<em>Weekly Highlights</em>
<br>
<ul>
 <li>The Website Development team completed the following:</li>
  <? var data = getData(); ?>
   <? for (var i = 0; i < data.length; i++) { ?>
     <? if (data[i][0]!="" && data[i][0]!=" ") { ?>
       <ul><li> <?= data[i][0] ?></li></ul>
     <? } ?>
   <? } ?>
  </ul>
  </body>
</html>
user_115997890
Related