Can my script be edited to ignore invalid email recipients in Send.email?

0
votes

Im running a script to help a group keep track of translations. The goal is to have the translator notified (emailed) when a document is updated (each language has it's own sheet within the doc). Each recipients email is listed in it's own cell in the row of the document they are responsible for translating. The script will run occasionally, but I think it is getting caught up on cells that have text (not emails) listed where email addresses should be.

I keep getting the error: 'Failed to send email: no recipient (line 21, file "Translation Notification")'

Is there a way to update my script so that it will invalid email recipients? 

 function shellFunction() {
  var sheets = ['Arabic', 'Portuguese', 'French','Spanish'];
  for (var s in sheets) {
    toTrigger(sheets[s]);
  }
}
  function toTrigger(sheetName) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheetName);
      var cell = ss.getActiveCell().getA1Notation();
      var row = sheet.getActiveRange().getRow();
      var cellvalue = ss.getActiveCell().getValue().toString();
      var recipients = sheet.getRange('J' + sheet.getActiveCell().getRowIndex()).getValue();
      var message = '';
      if (cell.indexOf('B') != -1)     
      {
        message = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue()
      }
      var subject = 'The ' + sheet.getRange('F' + sheet.getActiveCell().getRowIndex()).getValue() + ' ' + sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' needs your Translation';
      var body = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' has been updated. Can you please update ' + sheet.getRange('G' + sheet.getActiveCell().getRowIndex()).getValue() + '? Please remember to update the date column in the Resource Document when the translation is complete:' + ss.getUrl();
      MailApp.sendEmail(recipients, subject, body);
  }

EDIT: I realized Im looking for the function to ignore all invalid emails (not just blank cells) and continue to run.

3
google-apps-script
since you write "recipients" with an S, is there only one or possibly more than one recipients ? this could change the way to handle the error you get. The answer below only handles the case of one recipient alone. – Serge insas
@Sergeinsas actually it handles one cell as indicated in var recipients = – utphx
Yes, one cell doesn't mean one email address... the recipient argument can be a string of comma separated emails, but thx for the information. – Serge insas
Correct. Just wanted to clarify that the answer below does in fact handle more than one recipient. – utphx
Except if some unpredictable data like a comma is present after an email... In that case the script would expect a second addressand wouldn't find it. – Serge insas

3 Answers

1
votes

You can change 

if (cell.indexOf('B') != -1)     
  {
  message = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue()
  }
  var subject = 'The ' + sheet.getRange('F' + sheet.getActiveCell().getRowIndex()).getValue() + ' ' + sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' needs your Translation';
  var body = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' has been updated. Can you please update ' + sheet.getRange('G' + sheet.getActiveCell().getRowIndex()).getValue() + '? Please remember to update the date column in the Resource Document when the translation is complete:' + ss.getUrl();
  MailApp.sendEmail(recipients, subject, body);

To:

if (cell.indexOf('B') != -1 && recipients != "")   
  {
  message = sheet.getRange('A' +  sheet.getActiveCell().getRowIndex()).getValue()
  var subject = 'The ' + sheet.getRange('F' + sheet.getActiveCell().getRowIndex()).getValue() + ' ' + sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' needs your Translation';
  var body = sheet.getRange('A' + sheet.getActiveCell().getRowIndex()).getValue() + ' has been updated. Can you please update ' + sheet.getRange('G' + sheet.getActiveCell().getRowIndex()).getValue() + '? Please remember to update the date column in the Resource Document when the translation is complete:' + ss.getUrl();
  MailApp.sendEmail(recipients, subject, body);
  }

EDIT

You can use any email regex that works best for you but something like this should work:

var recipients = sheet.getRange('J' + sheet.getActiveCell().getRowIndex()).getValue().toString();

var getEmails = recipients.match(/([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9._-]+)/gi);

Now you have an array of hopefully valid emails that you can cycle through and use try/catch if there are still errors.

Note: no email regex is perfect

0
votes

Replace:

MailApp.sendEmail(recipients, subject, body);

with:

// Just in case there is blank space in the cell, replace it.
recipients = recipients.replace(/\s/g, '');
// Ensure recipients is non-empty.
if (recipients) {
  MailApp.sendEmail(recipients, subject, body);
}
0
votes

If you want you can simply do a try...catch. So you would have

try {
  MailApp.sendEmail(recipients, subject, body);
}
catch(error) {
  Logger.log(error)
}

which should go through typos and empty rows. It will still try to send the message, so in terms of performance it's not great, but this should then continue on with the code despite the error (you can do something else with the catch, like add the row where the error occurred, or the address that is wrong to a string and later send the list to yourself or whoever should check the emails)

EDIT: considering the comment left by asker to the answer.

The other answers so far (including mine) are assuming that you have emails in separate cells and you would iterate over the emails, sending 1 to each. Please clarify your question as what you are looking to do is remove e-mails that have an invalid format from the string.

In that case your best bet (barring actualy email validation, which requires you to have a complete list of absolutely certainly correct emails) would probably be to check the string for format errors. Here is a sample that will remove any email that does not have the correct format (note email addresses that do not exist will still remain and whoever runs the script will recieve a response about a failed mail delivery):

function myFunction() {
  var recipients = 'email1@gmail.com,email2gmail.com,email3@gmail.com,email4@gmailcom'
  var arr = recipients.split(',')
  var arr2 = []
  var i;

  for (i = 0; i < arr.length; i++){
    if (arr[i].indexOf('@') != -1){
      if (arr[i].indexOf('@') < arr[i].lastIndexOf('.')) {
        arr2.push(arr[i])
      }
    }
  }
  recipients = arr2.toString()
  return
}

Note, that you can refine the for loop a little to just use a single array instead of 2 by getting rid of the element inside the current array, however for simplicity sake and for it to be faster I wrote the easiest variant. In essence what this does is follows a simple logic:

  1. Does the email have an @ symbol? All emails must have one
  2. Is there a . symbol after the @ symbol. Again, this does not consider that the end might be .co or some other typo, only that it exists. You could just check for a full blown @gmail.com or other domain, if you are absolutely certain all emails will have that domain.

Finally we combine the array back into the string (you can technically get away with just reusing the 1 recipients variable if you really want to). I personally think the code will look a bit messier if you do though.

Again, note that there are limitations here and you may need to add extra conditions. For example just off the top of my head I did not consider any checks for the following scenarios:

Email address does not have anythi

  • ng after the last dot (e.g. email@gmail.)
  • Email does not contain empty domain (email@. or email@.com) though I am unsure if gmail will fail on those