Need only some columns to be sent in mail with one more column sorting criteria-CodePudding

I got a code with updated, need a table with few columns and some more criteria for sorting date. First Need to sort column X with TAT>2, column B with banks, and only B,C,D,E,G,H,J,K,X columns need to be mailed. Kindly help me with it.

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
  });
}

Like this photo table need to be displayed.

CodePudding user response：

Try

function createTable() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const dataRange = sheet1.getRange("A2:AH"   sheet1.getLastRow());
  dataRange.sort([{ column: 24, ascending: true }]);

  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 of [1, 2, 3, 4, 6, 7, 9, 10, 23]) { // B,C,D,E,G,H,J,K,X
        table = table   `<td style="background-color:${backgrounds[i][u]}">`   cells[u]   "</td>"; // Modified
      }
      table = table   "</tr>"
    }
  }

  //Send the email:
  MailApp.sendEmail({
    to: "###########@gmail.com",
    subject: "Example",
    htmlBody: table
  });
}