Google sheets script send one email instead of many

August 19, 2022

I have a script that checks data from sheet and if number from one column is bigger than 19, then it sends an notification email with the information from row.

I have new data every day so it sends around 1-10 emails per day.

I would like to have just one email with the data from all the rows instead of many emails with the data from separate rows.

Is that somehow doable?

Thanks!

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('sheet'); 
  const data = sh.getRange('B2:D75'+sh.getLastRow()).getValues();
  data.forEach(r=>{
     let overdueValue = r[2];  
     if (overdueValue > 19){ // if value is bigger than 19 then send email
         let name = r[0];
         let message ='https:/' + name + '    ' + 'https://' + name ;
         let subject = 'Subject: ' + name
         MailApp.sendEmail('example@gmail.com', subject, message); 
     }
  });
    
}

>Solution :

In your script, how about the following modification?

Modified script:

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('sheet');
  const data = sh.getRange('B2:D75' + sh.getLastRow()).getValues();
  const messages = data.reduce((ar, r) => {
    let overdueValue = r[2];
    if (overdueValue > 19) {
      let name = r[0];
      let message = 'https:/' + name + '    ' + 'https://' + name;
      ar.push(message);
    }
    return ar;
  }, []);

  let subject = "Subject"; // Please set the subject of the email.
  MailApp.sendEmail('example@gmail.com', subject, messages.join("\n"));
}