Send single email containing a table based on a condition to the recipients when the names are repet-CodePudding

This is the extended version of my previous question. I want to send email once in a week to the recipients based on the status column. Sheet Link: https://docs.google.com/spreadsheets/d/1GC59976VwB2qC-LEO2sH3o2xJaMeXfXLKdfOjRAQoiI/edit#gid=1546237372
The previous code is attached in the sheet.
From the sheet, When the Status column will be new and ongoing, a table will be generated with column Title, Link and due date and send a single email to the recipients even they are repeated. In the sheet, For resource Anushka, Status New appeared twice and Ongoing once. The table will be like-
Anushka || New || 10/25/2022
Anushka || New || 10/25/2022
Anushka || Ongoing || 10/25/2022
And after creating it, it will send single email to each recipients though they have appeared several times. I have done it for getting multiple emails whatever the status is with the help of another commenter from stackflow but I want to modify it and change it. The code for this one is a bit longer as I have two helper gs file, html table code and the main one. That's why I am not writing all the codes here. But in the sheet from the extension, one can see my code.

If anyone give me suggestions how to change or modify the logic, it will be appreciated.

Code

function macro(){
  // get range of cell with data from A1 to any cell near having value (call data region)
  var table = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getRange("A1").getDataRegion();
  var header=table.getValues()
  var resource=header[0][1]
  var status_r=header[0][3]
  var due_date_r=header[0][5]
  var link_r=header[0][10]
  
  // create custom table filtered by the column having header "State" where the value match "New"
  var filterTable = new TableWithHeaderHelper(table) 
                .getTableWhereColumn("Status").matchValueRegex(/(New)/);
                
  // for each row matching the criteria
  
  for(var i=0; i< filterTable.length() ; i  ){
    // Get cell range value at column Mail
    var mail = filterTable.getWithinColumn("Email").cellAtRow(i).getValue();
    // Any other value of column Target Value
    var resource_col = filterTable.getWithinColumn("Resource").cellAtRow(i).getValue();
    var status_col = filterTable.getWithinColumn("Status").cellAtRow(i).getValue();
    var due_date_col = filterTable.getWithinColumn("Due Date").cellAtRow(i).getValue();
    var link_col = filterTable.getWithinColumn("Link").cellAtRow(i).getValue();
    var new_data=[[resource_col,status_col,due_date_col,link_col]]
    var htmltemplate=HtmlService.createTemplateFromFile("email")
    htmltemplate.resource=resource
    htmltemplate.status_r=status_r
    htmltemplate.due_date_r=due_date_r
    htmltemplate.link_r=link_r
    htmltemplate.new_data=new_data
    var htmlformail=htmltemplate.evaluate().getContent()
    
    var subjectMail =  "Automation Support Services Actions Items";
    var dt1 = new Date() 
    var dt2 = due_date_col

   // get milliseconds
   var t1 = dt1.getTime()
   var t2 = dt2.getTime()

   var diffInDays = Math.floor((t1-t2)/(24*3600*1000));
   // 24*3600*1000 is milliseconds in a day
    console.log(diffInDays);
    
    // Send email 
    MailApp.sendEmail({
      to:mail ,
      subject: subjectMail,
      htmlBody:htmlformail,
    });
  } 
}```

CodePudding user response：

2 loops can make the job.

// get range of cell with data from A1 to any cell near having value (call data region)
var table = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3").getRange("A1").getDataRegion();
// init html header data
var header=table.getValues()
var resource=header[0][1]
var status_r=header[0][3]
var due_date_r=header[0][5]
var link_r=header[0][10]

var listOfEmails = [];
var tableWithHeader = new TableWithHeaderHelper(table) 

// get all email
for(var i=0; i< tableWithHeader.length() ; i  ){
  var mail = tableWithHeader.getWithinColumn("Email").cellAtRow(i).getValue();
  listOfEmails.push(mail)
}
// filter all email to get unique liste of email
var uniqueMailList = listOfEmails.filter((c, index) => {
    return listOfEmails.indexOf(c) === index;
});

for(var i=0; i< uniqueMailList.length; i  ){
  // get mail of target i
  var mail = uniqueMailList[i]
  // filter table using mail of target i and status
  var mailTable = new TableWithHeaderHelper(table) 
                .getTableWhereColumn("Status").matchValueRegex(/(New)/)
                .getTableWhereColumn("Email").matchValue(mail);
  // initialise html template
  var htmltemplate=HtmlService.createTemplateFromFile("email")
  htmltemplate.resource=resource
  htmltemplate.status_r=status_r
  htmltemplate.due_date_r=due_date_r
  htmltemplate.link_r=link_r
  var new_data = []
  var htmlformail
  // loop into the filtered table of target i only
  for(var j=0; j< mailTable.length() ; j  ){
    // Any other value of column Target Value
    var resource_col = mailTable.getWithinColumn("Resource").cellAtRow(j).getValue();
    var status_col = mailTable.getWithinColumn("Status").cellAtRow(j).getValue();
    var due_date_col = mailTable.getWithinColumn("Due Date").cellAtRow(j).getValue();
    var link_col = mailTable.getWithinColumn("Link").cellAtRow(j).getValue();
    new_data.push([resource_col,status_col,due_date_col,link_col])
  }
  htmltemplate.new_data=new_data
  htmlformail=htmltemplate.evaluate().getContent()

  var subjectMail =  "Automation Support Services Actions Items";
  var dt1 = new Date() 
  var dt2 = due_date_col

  // get milliseconds
  var t1 = dt1.getTime()
  var t2 = dt2.getTime()

  var diffInDays = Math.floor((t1-t2)/(24*3600*1000));
  // 24*3600*1000 is milliseconds in a day
  console.log(diffInDays);
    
  // Send email 
  MailApp.sendEmail({
    to:mail ,
    subject: subjectMail,
    htmlBody:htmlformail,
  });
}

I'm not confident on the new_data.push([resource_col,status_col,due_date_col,link_col]), it's seems to be corect but I have no no way to verify that

Anyway thanks for using the utils script at https://github.com/SolannP/UtilsAppSsript, glad to see it help