The following Google Apps Script is designed to loop through a Google Sheet cell of email addresses that have been separated by a comma and space. It then uses a regular expression (regex) to confirm the email address meets the correct formatting criteria.
This code was developed as a way of implementing additional checks when asking users to be precise in how exactly they enter multiple email addresses. So if they were to forget the space for instance it could alert them, before the rest of the code risked failing as whatever task it was designed to do.
 |
| Regular expression to check the format of an email address |
Regex breakdown
- ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
- ^ matches the beginning of the string.
- [a-zA-Z0-9._%+-] match any character in this set e.g. from "a" to "z" and any "." or "_"
- + match one or more of the preceding token.
- @ match a "@" character.
- [a-zA-Z0-9.-] match any character in this set e.g. from "a" to "z" and any "." or "-"
- \. escaped character to match a "."
- [a-zA-Z] match any character in this set e.g. from "a" to "z"
- {2,} match two or more of the preceding token.
- $ matches the end of the string.
Therefore something like "example1@gmail.com" will be accepted but "example2@gmailcom" will not.
Download
Check comma separated email address is valid download (please use 'File' > 'Make a copy' for your own version).
Thank you for sharing this! The regex alone is gold!
ReplyDeleteOne change I would make when using this would be to modify line 22 from:
var emailAddressArray = emailAddressCell.split(", ");
to:
var emailAddressArray = emailAddressCell.split(",").map((v) => v.trim());
I made the separating character in the split function a single comma (no space) and then used the map array function to trim any whitespace from the beginning and end of each email address. This allows your users to enter the comma separated list with or without spaces, and things will still work properly.
If you really need each element separated by a comma and a space you can then remake the comma separated string and supply the separator as " ,".
var emailAddressString = emailAddressArray.join(", ");
Love it Edd! Thank you so much for sharing.
DeleteI'm going to put that into practice going forwards as it'll help users who make a typo when separating email addresses.
Kind regards