Need an EMail with HTML table using filtered google sheet data-CodePudding

Need to send an email to send to the recipients based on certain criteria. I have done this code about 200 mails received. Loop is not been ending and filtering function also didnt work. Complete table is been receiving. Kindly help me with this.

Data link also been attached: https://docs.google.com/spreadsheets/d/1Cxq2lxNqMFR8hpIwW4qmpjBYve2FakVtSarb7hqGRVE/edit#gid=0

function createTable(data){ 
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  function create_filter(){
    const  ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet1 = ss.getSheetByName("Filter_Sheet");
    const  range = sheet1.getRange("A2:AH");
    const  filter = range.createFilter();
    const Filter_Criteria1 = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(2);
    const  Filter_Criteria2 = SpreadsheetApp.newFilterCriteria().whenTextContains(["Report Pending"]||["Site Visit Pending"]||["Query Raised"]||["Hold"]);
    const coll1 = 24;
    const coll2 = 10;
    
    const  add_filter1 =  filter.setColumnFilterCriteria(coll1,Filter_Criteria1);
    const  add_filter2 = filter.setColumnFilterCriteria(coll2,Filter_Criteria2);}
  
  //To be looped in the future, 10 in range to be replaced with k
  var dataRange = sheet.getRange("A2:AH");
  var data = dataRange.getValues();

  //Table Header
  var table = "<html><body><br><table border=1><tr><th>Compliance Score</th><th>STSOR Value</th><th>STSOR %</th><th>ZZ lines</th><th>ZZ%</th><th>PI lines Counted</th><th>PI %</th><th>NRDS Value</th><th>NRDS %</tr></br>";

  //table Body
 for (var i = 0; i < data.length; i  ){
    cells = data[i]; //puts each cell in an array position
    table = table   "<tr>";

      for (var u = 0; u < cells.length; u  ){
          table = table   "<td>"  cells[u]  "</td>";
      }
    table = table   "</tr>"
    

  //Send the email:
 MailApp.sendEmail({
    to: "[email protected]", 
    subject: "Example",
    htmlBody: table}); 
     }
}

CodePudding user response：

Try

function createTable() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const dataRange = sheet1.getRange("A2:AH");

  var criterias = ["Report Pending", "Site Visit Pending", "Query Raised"]
  var backgrounds = dataRange.getBackgrounds(); // Added
  var data = dataRange.getDisplayValues(); // Modified
  var table = "<html><body><br><table border=1><tr><th>Compliance Score</th><th>STSOR Value</th><th>STSOR %</th><th>ZZ lines</th><th>ZZ%</th><th>PI lines Counted</th><th>PI %</th><th>NRDS Value</th><th>NRDS %</tr></br>";
  for (var i = 0; i < data.length; i  ) {
    cells = data[i];
    if (cells[23] > 2 && criterias.indexOf(cells[9]) != -1) {
      table = table   "<tr>";
      for (var u = 0; u < cells.length; u  ) {
        table = table   `<td style="background-color:${backgrounds[i][u]}">`   cells[u]   "</td>"; // Modified
      }
      table = table   "</tr>"
    }
  }

  //Send the email:
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "Example",
    htmlBody: table
  });
}