I need to send an email to the recipients based on certain criteria. I have done this code about 200 mails received. Loop is not been ending and the filtering function also didn't work. The complete table has been received. Kindly help me with this.
The data link has also been attached:
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: "rama.mannava465@gmail.com",
subject: "Example",
htmlBody: table});
}
}
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: "ramauni3994@gmail.com",
subject: "Example",
htmlBody: table
});
}