Google app script : check email duplication on email column, if found same email will call another e-CodePudding

It is possible to send another email body if found same email address in email column. eg. column b have 2 [email protected] email is same, when user input the email with same [email protected] again, the user will receive email message and show that your email had been registered before.

anyone help is much appreciated.

my code

    function sendmail() {
  
  Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
  const range = this.getRange(offsetRow, columnNumber, 2);
  const values = range.getDisplayValues();
  if (values[0][0] && values[1][0]) {
    return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()   1;
  } else if (values[0][0] && !values[1][0]) {
    return offsetRow   1;
  }
  return offsetRow;
};

  
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet1");
  var EMAIL_SENT = "EMAIL_SENT";
  
  // var lastRow = sh1.getLastRow();
  var lastRow = sh1.get1stEmptyRowFromTop(1) - 1;
  
  var email = sh1.getRange(lastRow, 2).getValue();
  var name = sh1.getRange(lastRow, 1).getValue();
  var registercode = sh1.getRange(lastRow, 3).getValue();
  
  subjecttxt = "Thank you for registered with us";
  
  var email_Body;
  if (lastRow < sh1.get1stEmptyRowFromTop(3)) {
    email_Body = "Dear "   name   ","   "<br>"   "your registeration is successfully, your registered code is "   registercode   "Thank you for your registeration";  
  } else {
    email_Body = "Registration had been closed, Thank you.";
  }
  
  
              MailApp.sendEmail({
                to:email,
                subject: subjecttxt,
                htmlBody: email_Body,
              }) ;           
  sh1.getRange(lastRow, 4).setValue(EMAIL_SENT);
}

CodePudding user response：

Yes, it is possible - compare the new email value to the preexisting ones

For this, modify

  var email_Body;
  if (lastRow < sh1.get1stEmptyRowFromTop(3)) {
    email_Body = "Dear "   name   ","   "<br>"   "your registeration is successfully, your registered code is "   registercode   "Thank you for your registeration";  
  } else {
    email_Body = "Registration had been closed, Thank you.";
  }

  var email_Body;
  var listedRecipients = sh1.getRange("B2:B"   (lastRow - 1)).getValues().flat();
  if (listedRecipients.indexOf(email) == -1) {

    if (lastRow < sh1.get1stEmptyRowFromTop(3)) {
      email_Body = "Dear "   name   ","   "<br>"   "your registeration is successfully, your registered code is "   registercode   "Thank you for your registeration";
    } else {
      email_Body = "Registration had been closed, Thank you.";
    }
  }
  else {
    email_Body = "Your email had been registered before."
  }

Explanation:

Getting the values of the range "B2:B" (lastRow - 1) will return your all email recipients apart from the last one.
flat() will convert the 2D value range into a 1D array.
On this 1D-aray you can use the method indexOf() to verify either the new recipient is contained in the list of the already existing ones.
Based on the outcome you can implement an if...else condition.