Email multiple cells to recipient instead of one email per cell using Apps Script-CodePudding

Currently I'm working on a script that checks values of one column and based on that send email containing information from another column. Everything works perfectly except one thing - it send one email per value and I'd like to send all the values in one email. Can someone please help with the issue ?


const helpsheet = SpreadsheetApp.openById("ID").getSheetByName('Sheet');
 const date =  helpsheet.getRange(1,10).getValue();


 const ss = SpreadsheetApp.openById("ID2");
 const sh = ss.getSheetByName('Sheet2'); 
 const data = sh.getRange('A2:c' sh.getLastRow()).getValues();

var recipients = 'EMAIL@EMAIL';
var subject = 'SUBJECT'; 
 
 data.forEach(r=>{
    let overdueValue = r[2];  
    if (overdueValue > date)
    
    
    {
        let path = r[0];


 MailApp.sendEmail({
   to: recipients,
   subject: subject,
   htmlBody: 'Hi guys '   path  '!<br><br>Best regards,'
 });

    }
 });  
} ```

CodePudding user response：

Of course I can't test this because 1st I don't want a bunch of emails to myself and 2nd I don't have a data set that matches your data, but I'm pretty sure this will do what you want. What I do is build an array of rows that pass the sniff test using the Array push method below. Then when I have all the rows that pass I send one email.

function test() {
  try {
    const helpsheet = SpreadsheetApp.openById("ID").getSheetByName('Sheet');
    const date =  helpsheet.getRange(1,10).getValue();

    const ss = SpreadsheetApp.openById("ID2");
    const sh = ss.getSheetByName('Sheet2'); 
    const data = sh.getRange('A2:c' sh.getLastRow()).getValues();

    var recipients = 'EMAIL@EMAIL';
    var subject = 'SUBJECT'; 
    var passed = [];
    data.forEach( r => {
      let overdueValue = r[2];  
      if (overdueValue > date) {
        passed.push(r);
      }
    });

    if( passed.length > 0 ) {  // maybe nothing passed the test
      var path = JSON.stringify(passed);
      MailApp.sendEmail( { to: recipients,
                           subject: subject,
                           htmlBody: 'Hi guys '   path  '!<br><br>Best regards,'
        }
      );
    }
  }
  catch(err) {
    console.log(err);
  }
}