Send Email To Users Depending On An Email Address In A Column Of Google Sheets-CodePudding

I have a brilliant script that was given to me by @cooper that updates a sent column and sends an email everytime a new row is added to a google sheet using google apps script.

What I now want to do is instead of the email going to me ([email protected]) I want it to be send to an email address contained in column E.

Structure of google sheets can be seen below:

Can someone help?

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 = "[email protected]";
    if (EmailSent != "Yes") {
      sh.getRange(i   sr, 6).setValue("Yes");
      MailApp.sendEmail({to: SendTo,cc: "",subject: Subject,htmlBody: message});
    }
  })
}

CodePudding user response：

It would just be a matter of replacing the SendTo hardcoded value with that row column value.

let SendTo = r[4];