Send email to the corresponding person with a attached table of that corresponding row based on a cell value using Google app script

Question

I want to send the email to the corresponding person with a attached table of that row based on the cell value. For example, In the sheet, when the the column D==="New" and "Ongoing", an email will send to the recipients of C column with a generated table of that row. I don't want onedit trigger as I will send the email only once in a week. When table will generate, it will only take column Resource, status, Due date and Link. I have write the code for sending email and as well creating a table. The problem is when I am creating the table, It is taking all the data where status is New. For this reason, all the recipients get the same table but I want the table in a way that the recipient will get only his corresponding row's table. I have attached my code here. Can anyone one suggest what should I add here? Sheet link: https://docs.google.com/spreadsheets/d/1GC59976VwB2qC-LEO2sH3o2xJaMeXfXLKdfOjRAQoiI/edit#gid=0

Code:

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var sheet=ss.getSheetByName("Sheet3")
  var sheetData=sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues()
  var filteredRows_new = sheetData.filter(function(row){
    if (row[3] === 'New') {
      return row
    }
  })
  // create the html table
  var header=sheet.getRange(1,1,1,sheet.getLastColumn()).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 data=filteredRows_new.map(col=>[col[1],col[3],col[5],col[10]])
 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.data=data
 var htmlformail=htmltemplate.evaluate().getContent()
 console.log(htmlformail)

  // extract only the new status data for email
  filteredRows_new.forEach(function(row) {
  //Email address
  var email = row[2];
  GmailApp.sendEmail(email,"automation","open the link for html", { htmlBody: htmlformail })
})

  //extract the rows for ongoing
  var filteredRows_ongoing = sheetData.filter(function(col){
    if (col[3] === 'ongoing') {
      return col
    }
  })
  //console.log(filteredRows_ongoing)
}

Answer 1

I faced a very similar problem and it seems that there is no dedicated function in app script documentation for that. That why I tried to create a lib to be able to do it in an elegant manner.

Your file is lock, we cannot acces ! See bellow the best answer I can provide, some value like header name have to be updated with your data.

To be able to use those lib, you have to create two new gs script and a copy paste the two scripts UtilsGSheetTableHelper.gs and UtilsGmailHelper availlable there : https://github.com/SolannP/UtilsAppSsript

And then on the code.gs file

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();
  // create custom table filtered by the column having header "State" where the value match "New"
  var filterTable = new TableWithHeaderHelper(table) 
                .getTableWhereColumn("State").matchValueRegex(/(New)|(Ongoing)/);
  // 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("Mail").cellAtRow(i).getValue();
    // Any other value of column Target Value
    var anyOtherValue = filterTable.getWithinColumn("Target Value").cellAtRow(i).getValue();
    // Send email 
    MailApp.sendEmail({
      to:mail ,
      subject: "BIKINI ASSEMBLY",
      cc:cc,
      htmlBody:`<h1>Sheet table weekly</h1><hr><p>${anyOtherValue}<p>`,
    });
  } 
}

Then you can attach the script to a button to click at the end of the week.

As further improvement you can update a cell with the date of last mail send and even more !

Take care !

UPDATE

see the code for matching your request :

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();
  // 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 bodyMail = filterTable.getWithinColumn("Description").cellAtRow(i).getValue();
    var subjectMail = filterTable.getWithinColumn("Title").cellAtRow(i).getValue();
    
    // Send email 
    MailApp.sendEmail({
      to:mail ,
      subject: subjectMail,
      htmlBody:subjectMail,
    });
  } 
}