根据给定数据自动发送电子邮件

Question

代码不应该在“参考”表中的“n”栏中的“电子邮件发送”栏中再次发送电子邮件。

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname= sheet.getSheetByName("Reference");
  var startRow = 2; // First row of data to process
  var numRows = sheetname.getLastRow("Reference"); // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheetname.getRange(startRow, 1, numRows, 15);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[6]; // First column
    var message = row[13]; // Second column
    var emailSent = row[14]; // Third column
    if (emailSent !== EMAIL_SENT && row[12] == "Mail Send"  && row[6] != "") { // Prevents sending duplicates
      var subject = row[11];
      MailApp.sendEmail(emailAddress, subject, message);
      sheetname.getRange(startRow + i, 15).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

如有任何帮助，我将不胜感激

Answer 1

先原谅英国人

为此，您可以使用下面的代码，并忽略“引用”选项卡

不要忘记创建触发器，以便在表单发送后触发触发器。

function Send_email() { 

  var INITIALline = 2; 
  var columnSEND = 11;  
  var STATUScolumn = 12;
  var textCONDITION = "Submit";
  var textSENT = "Sent"
    
  var tab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data"); 
  var interval = tab.getRange(INITIALline,1,tab.getLastRow()-INITIALline+1,STATUScolumn);
  var dice = interval.getValues();
  var yousent = false;
  var email,subject,message;
  
  for (var i=0; i<dice.length; ++i) {
    if((dice[i][columnSEND-1]==textCONDITION) && (dice[i][STATUScolumn-1]!=textSENT)){
    
      var email    = dice[i][6]
    
      subject = dice[i][7]+" | Your CASE ID IS | "+dice[i][0];
            
      var message = "<font size='3' face='Comfortaa'>Dear "+dice[i][3]+",<br/><br/>"+
      
                     "Thanks for connecting with us. This is your case id "+dice[i][0]+"..<br/><br/>"+
                     
                     "<i>Thanks & Regards</i><br/>"+
                     "<b>Team</b>";
      
      MailApp.sendEmail(email, subject, message,{ htmlBody: message});
      tab.getRange(INITIALline+i,STATUScolumn).setValue(textSENT);
      yousent = true;
      SpreadsheetApp.flush();
    }
  }
 }