使用无效电子邮件从 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 zerofor (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() {

  

  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." 

   

    +Responses;

    //send emails only if Status and email Status criteria are met

      

  try{

  if (emailSentStatus !== "Team email Sent"){

    MailApp.sendEmail(emailSubmitter+", "+emailStakeholder+", "+BusinessPartneremail, subject, message,{noReply: true});  //write email and send

    

      sheet.getRange(startRow + i, lastColumn).setValue("Team email Sent");// write email sent when done 

      SpreadsheetApp.flush(); //refresh spreadsheet

    }

  } // try

  

  catch(e) {continue;}

  

}

}