Search code examples
htmlgoogle-apps-scriptgoogle-sheetsgmail-api

Is it possible to present the list of resources from Google sheets - one below the other, in an email output using HTML code?


Link to Gsheet (refer Cols G-H) - https://docs.google.com/spreadsheets/d/1eSP42EmcMOCf9PMYGXetAVGHKz4c567F1-AOEmZwVlY/edit?usp=sharing I use HTML code in my Google Appscript to send automated emails. The appscript works well, but I was wondering if there's any workaround to present the list one below the other in HTML. In sheets, I have used TEXTJOIN+CHAR(10) to make a combined list. I prefer to have the same view in email output as well. Below is the sample HTML that I am using:

<table border="1" cellpadding="1" cellspacing="1" style="width:500px">
    <tbody>
        <tr>
            <td>
            <p>Here&#39;s the list of people from Column G&nbsp;</p>

            <p><?= level2names?></p>

            <p>&nbsp;</p>

            <p>&nbsp;</p>
            </td>
        </tr>
    </tbody>
</table>

<p>&nbsp;</p>

Below is the appscript I am using:

function trial() {
  var name1 = 0;
  var email = 1;

  var emailTemp = HtmlService.createTemplateFromFile("htmlcode");

  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  
  var data = ws.getRange("G2:H" + ws.getLastRow()).getValues();

  data.forEach(function(row){
    emailTemp.level2names = row[name1];    
    
    var htmlMessage = emailTemp.evaluate().getContent();
    GmailApp.sendEmail(
      row[email],
      "Trial Output!",
      "Your email doesnt support HTML",
      {name: "Vish", htmlBody: htmlMessage}
    );
  });
}

Expected output: enter image description here

Current Output: enter image description here


Solution

  • In Code.gs, replace all \n newline characters in row[name1] to <br>:

    data.forEach(function(row){
        // Add this line:
        row[name1] = row[name1].replace(/\n/g, "<br>")
    
        emailTemp.level2names = row[name1]; 
    
        var htmlMessage = emailTemp.evaluate().getContent();
        GmailApp.sendEmail(
          row[name1],
          "Trial Output!",
          "Your email doesnt support HTML",
          {name: "Vish", htmlBody: htmlMessage}
        );
      });
    

    And change your scriptlet type to force printing:

    <!-- Add the ! character after the ? -->
    <?!= level2names?>