I'd be grateful for your help.

At present this script sends an email to a recipient (email address contained in the spreadsheet) everytime a new row is added to my spreadsheet (using a on change trigger event). A recipient may receive multiple messages during this time.

What i'd like is for multiple rows of data to be sent in one email to a specific recipient (i'm going to change the trigger to a time driven one.)

Sample of current data:

How would you do this?

Also would I need to change the .getactive command if im doing this?

Thanks in advance.

Mark

function sendemail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const sr = 2;
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 6);
  const vs = rg.getValues();
  vs.forEach((r, i) => {
    let Subject = "New Content on IG.com";
    let message =
      "<p><b>Title: </b>"   r[0]   "</p>"  
      "<p><b>Article: </b>"   r[1]   "</p>";
    let EmailSent = r[5];
    let SendTo = r[4];
    if (EmailSent != "Yes") {
      sh.getRange(i   sr, 6).setValue("Yes");
      MailApp.sendEmail({to: SendTo,cc: "",subject: Subject,htmlBody: message});
    }
  })
}

CodePudding user response：

Send one email to each recipient:

function sendemail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const sr = 2;
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 6);
  const vs = rg.getValues();
  const sub = "New Content on IG.com";
  let eml = {pA:[]};
  vs.forEach((r, i) => {
    let msg = "<p><b>Title: </b>"   r[0]   "</p>"   "<p><b>Article: </b>"   r[1]   "</p>";
    let sent = r[5];
    let rec = r[4];
    if (sent != "Yes") {
      sh.getRange(i   sr, 6).setValue("Yes");
      if(!eml.hasOwnProperty(r[4])) {
        eml[r[4]] = [msg]
        eml.pA.push(r[4]);
      } else {
        eml[r[4]].push(msg)
      }
    }
  });
  eml.pA.forEach(p => { MailApp.sendEmail({to: p,subject:sub,htmlBody:eml[p].join('<br>')})});
}