https://www.pbainbridge.co.uk/2024/08/check-comma-separated-email-address-is.html

Check comma separated email address is valid.gs

/**
 * Developed by The Gift of Script: https://www.pbainbridge.co.uk/
 */


function checkEmailAddresses() {


  // create regular expression for testing valid email address
  var emailAddressRegEx = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/gm;


  // get Data sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Data");

  // get cell containing email addresses
  var emailAddressCell = dataSheet.getRange(5, 3).getValue();
  console.log(emailAddressCell);

  // split email addresses by comma and space
  var emailAddressArray = emailAddressCell.split(", ");


  // loop through each email address
  var emailAddressArrayLength = emailAddressArray.length;
  for (var i = 0; i < emailAddressArrayLength; i++) {

    // get single email address
    var singleEmailAddress = emailAddressArray[i];
    console.log("singleEmailAddress is: " + singleEmailAddress);

    // test valid email address against RegEx
    var checkEmailAddress = singleEmailAddress.match(emailAddressRegEx);
    if (checkEmailAddress) {

      console.log("This is a valid email address format.");

    } else {

      console.log("This is an invalid email address format.");


      // launch HTML popup
      var popupTitle = "Invalid email address format";
      var popupMessage = "There is a problem with the following email address, please ensure you have entered it correctly into the Google Sheet with a comma and space.<br/><br/>Email address: " + singleEmailAddress;

      var htmlOutput = HtmlService
        .createHtmlOutput(popupMessage)
        .setWidth(380)
        .setHeight(180);
      SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);


      // break out of the loop to stop
      break;


    };

  };


}