How to send a data array in one email without looping in google apps script?

Question

I am writing an email function that is supposed to send an email from ALL the range data in the spreadsheet after clicking a button, the problem is I cannot get one email with all the data, instead, every email is sent individually through the loop, is there a way that I can have all data sent in as HTML data in one email? When I place var htmlMessage=emailTemp.evaluate().getContent(); outside the loop it triggers BarcodeT is not defined.

function email(){
  
  var Barcode=0;
  var Name=1;
  var Added=2;
  var Price=3;

  var emailTemp=HtmlService.createTemplateFromFile("Email");
  var ws=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Refill Request");
  var data = ws.getRange("A3:D" + ws.getLastRow()).getValues();
  

   let BarcodeT="";
   let NameT="";
   let AddedT="";
   let PriceT="";
  
  data.forEach(function(row){
    BarcodeT+=emailTemp.ItemCode=row[Barcode];
    NameT+=emailTemp.ItemName=row[Name];
    AddedT+=emailTemp.QuantityNeeded=row[Added];
    var htmlMessage=emailTemp.evaluate().getContent();

    })
            

    var address = "[email protected]";

    MailApp.sendEmail(address,"Refill Request","Your email doesn't support HTML.",
    {name: "Email App", htmlBody: htmlMessage})

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <table>
  <tr>
    <th>Item Code</th>
    <th>Item Name</th>
    <th>Quantity Needed</th>
  </tr>
  <tr>
    <td><?=BarcodeT ?></td>
    <td><?=ItemName ?></td>
    <td><?=QuantityNeeded ?></td>
  </tr>
    </table>
  </body>
</html>

Tanaike · Accepted Answer · 2022-03-08 02:34:17Z

2

In your script, how about the following modification?

Modified script:

function email() {
  var Barcode = 0;
  var Name = 1;
  var Added = 2;
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Refill Request");
  var data = ws.getRange("A3:D" + ws.getLastRow()).getValues();
  var htmlMessage = "<table><tr><th>Item Code</th><th>Item Name</th><th>Quantity Needed</th></tr>";
  data.forEach(function (row) {
    htmlMessage += `<tr><td>${row[Barcode]}</td><td>${row[Name]}</td><td>${row[Added]}</td></tr>`;
  });
  htmlMessage += "</table>";
  var address = "[email protected]";
  MailApp.sendEmail(address, "Refill Request", "Your email doesn't support HTML.", { name: "Email App", htmlBody: htmlMessage });
}

In this modification, the HTML table is directly created in Google Apps Script and it is used as the HTML body.

answered Mar 8, 2022 at 2:34

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to send a data array in one email without looping in google apps script?

1 Answer 1

Modified script:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Comments

Your Answer

Sign up or log in

Post as a guest

Related