使用无效电子邮件从 googlesheet 发送电子邮件停止循环。如何跳过

Question

我有一个发送电子邮件的 googlescript。它运行良好，但似乎在遇到无效电子邮件时完全停止。有一个字段是手动输入的电子邮件地址，该地址会定期出现错误。我离开办公室一周，60 封电子邮件失败，因为脚本因电子邮件无效而停在一行，没有继续前进。我不使用 javascript/googlescript 编写，所以这对我来说是一个非常具有挑战性的项目。我感谢任何帮助。

如果电子邮件无效，我假设我需要添加一些内容来跳过一行。我需要在脚本中更改什么？如果他/她输入了无效的电子邮件地址（脚本是通过表单提交触发的），是否有办法将其推回提交者？如果有最佳实践，我愿意接受替代方案和建议。

错误消息示例 “异常：无效电子邮件：[e-mail]、[e-mail]`、[e-mail]、[e-mail] at EmailHRBPonFormSubmit（自动电子邮件提交：40:13）”

function EmailTeamonFormSubmit() {

  

  var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

  var startRow = 2;                            

  var numRows = sheet.getLastRow()-1;        // Number of rows to process

  var lastColumn = sheet.getLastColumn();      // Last column

  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // get the data range of the active sheet

  var data = dataRange.getValues();            // get values for each row in the range

  //////////////////////////////////////////////

 //create a for loop; define variables based on increasing count, starting from zero

for (var i = 0;  i < data.length; i++) {

  var row = data[i];

    var emailSubmitter = row[1];

    var submitter = row[5];

    var emailStakeholder = row[11];

    var stakeholder = row[10];

    var jobName = row[12];

    var emailSentStatus = row[49]

    var BusinessPartneremail = row[47]

    var BusninessPartnername = row[48]

    var subject =  'New Submission: '+jobName;

    var uniqueID = row[46]

    var Responses = row[45] //copy of the entire form response

    

    //message to the team

    var message = "Hello, "+

    BusninessPartnername+ ", " +stakeholder+ 

      ""+

    ", A new submission was sent by " +submitter+ ". The Unique ID is: " +uniqueID+ ". The responses are below."     }

  }

}

Answer 1

一个快速的解决方案是添加一个try/catch。本质上，当MailApp.sendEmail失败时，脚本将从下一次迭代继续，直到到达data.length.像这样包装包含MailApp.sendEmail函数的 if 语句：function EmailTeamonFormSubmit() {&nbsp;&nbsp;&nbsp; var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");&nbsp; var startRow = 2;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; var numRows = sheet.getLastRow()-1;&nbsp; &nbsp; &nbsp; &nbsp; // Number of rows to process&nbsp; var lastColumn = sheet.getLastColumn();&nbsp; &nbsp; &nbsp; // Last column&nbsp; var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // get the data range of the active sheet&nbsp; var data = dataRange.getValues();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // get values for each row in the range&nbsp; //////////////////////////////////////////////&nbsp;//create a for loop; define variables based on increasing count, starting from zerofor (var i = 0;&nbsp; i < data.length; i++) {&nbsp; var row = data[i];&nbsp; &nbsp; var emailSubmitter = row[1];&nbsp; &nbsp; var submitter = row[5];&nbsp; &nbsp; var emailStakeholder = row[11];&nbsp; &nbsp; var stakeholder = row[10];&nbsp; &nbsp; var jobName = row[12];&nbsp; &nbsp; var emailSentStatus = row[49]&nbsp; &nbsp; var BusinessPartneremail = row[47]&nbsp; &nbsp; var BusninessPartnername = row[48]&nbsp; &nbsp; var subject =&nbsp; 'New Submission: '+jobName;&nbsp; &nbsp; var uniqueID = row[46]&nbsp; &nbsp; var Responses = row[45] //copy of the entire form response&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; //message to the team&nbsp; &nbsp; var message = "Hello, "+&nbsp; &nbsp; BusninessPartnername+ ", " +stakeholder+&nbsp;&nbsp; &nbsp; &nbsp; ""+&nbsp; &nbsp; ", A new submission was sent by " +submitter+ ". The Unique ID is: " +uniqueID+ ". The responses are below."&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; +Responses;&nbsp; &nbsp; //send emails only if Status and email Status criteria are met&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; try{&nbsp; if (emailSentStatus !== "Team email Sent"){&nbsp; &nbsp; MailApp.sendEmail(emailSubmitter+", "+emailStakeholder+", "+BusinessPartneremail, subject, message,{noReply: true});&nbsp; //write email and send&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; sheet.getRange(startRow + i, lastColumn).setValue("Team email Sent");// write email sent when done&nbsp;&nbsp; &nbsp; &nbsp; SpreadsheetApp.flush(); //refresh spreadsheet&nbsp; &nbsp; }&nbsp; } // try&nbsp;&nbsp;&nbsp; catch(e) {continue;}&nbsp;&nbsp;}}