Occasional Exception: Failed to send email: no recipient (onFormSubmit)

user_13258458 0
Viewed 134

I have a script for emailing responses when someone submits a form. It works most of the time, but sometimes (too often for my liking) I get this error in my inbox

onFormSubmit | Exception: Failed to send email: no recipient | formSubmit

The form sends responses to a sheet which contains text for the email body. This is the code:

function onFormSubmit(e) {
  var form = FormApp.openById('xyz'); 
  var lastRow = form.getResponses().length; //the length of the form results, i.e. the number of submission. We choose the one submitted now.
  var sheet = SpreadsheetApp.openById('abc').getSheetByName('Calculation'); //link to the responses spreadsheet
  var email = sheet.getRange(lastRow+1,2).getValue();
  var timestamp = sheet.getRange(lastRow+1,1).getValue();
  var temp = new Date(timestamp);
  var formattedDate = Utilities.formatDate(temp, "GMT", "HH:mm' GMT, 'd MMMM, yyyy");
  var sh1 = SpreadsheetApp.openById('abc').getSheetByName('Results'); //link to the results spreadsheet
  
  //results of part 1
  var title1 = sh1.getRange(lastRow+1,4).getValue(); 
  var subtitle1 = sh1.getRange(lastRow+1,5).getValue();
  var sentence1 = sh1.getRange(lastRow+1,6).getValue();
  var bullet1=[]; // = "<li>" + sh1.getRange(lastRow+1,6).getValue()+"</li>";
  for (col = 1; col<6; col++) {
    if (sh1.getRange(lastRow+1,col+5).getValue().length >2){
      bullet1 += "<li>" + sh1.getRange(lastRow+1,col+5).getValue()+"</li>";
    }
  }
  var subtitle2 = sh1.getRange(lastRow+1,12).getValue();
  var sentence2 = sh1.getRange(lastRow+1,13).getValue();
  var bullet2=[]; // ="<li>" + sh1.getRange(lastRow+1,8).getValue()+"</li>";
  for (col = 1; col<6; col++) { //4
    if (sh1.getRange(lastRow+1,col+12).getValue().length >2){ //5
    bullet2 += "<li>" + sh1.getRange(lastRow+1,col+12).getValue()+"</li>";
    }
  }
    
  var emailsubject = "Your Survey Results";
  var name = sheet.getRange(lastRow+1,27).getValue();
  var salutation = "Dear " + name + ","+'<br><br>'+"Thank you for your survey submission at "+ formattedDate +"."+'<br><br>';
  var result1 = '<h3>'+title1+'<small> TYPE</small></h3><h4>'+subtitle1+'</h4><p>'+sentence1+'</p><ul>'+bullet1+'</ul><h4>'+subtitle2+'</h4><p>'+sentence2+'</p><ul>'+bullet2+'</ul>';
  var signature = "Find out more ...";
  var emailbody = salutation +'<div style="width:450px"><table><tr><td style="background-color:lightblue; border: 1px solid black; padding: 10px;">'+ result1 +'</td></tr></table></div><br>'+ signature;

  GmailApp.sendEmail(email,emailsubject, '', {htmlBody:emailbody});
}

Is there some failsafe I could add, or some code change I should make so it always reads the email address from the sheet? Or, maybe this should be implemented differently? I've checked both sheet and form results and the email addresses are there in each case.

Many thanks.

google-forms google-apps-script google-sheets
1 Answers

You specify

var lastRow = form.getResponses().length;

and

var email = sheet.getRange(lastRow+1,2).getValue();

In other words, if you have 5 form responses you expect the email address to be in row 6, column 2 (so cell B6).

  • Without seeing your data I can not tell if this cell contians a valid email address indeed.
  • However, you can implement an if statement catching an empty entry for the email
  • For this modify your code to
...
if(email != "" && email != " "){
  GmailApp.sendEmail(email,emailsubject, '', {htmlBody:emailbody});
}

Further information:

If you want to make sure that email contains a valid email address, you can proceed as here.

user_115997890
Related