google-apps-script - 如何将已发送电子邮件添加到状态列并在脚本再次运行时排除这些行？

Question

我对脚本非常陌生，并且正在努力为我们的青年联盟计划的服装筹款活动编写脚本。当通过单击图像触发该脚本时，将向列表中的所有收件人发送一封电子邮件，在电子邮件发送到的所有行中将“已发送电子邮件”添加到状态列“我”...然后每当脚本再次运行，仅向尚未通过电子邮件发送的新数据行发送电子邮件。

我有插入已发送电子邮件的脚本，但如果再次运行脚本，它不会注意状态列“我”，我会收到重复的电子邮件。我已经使用教程视频让我走到了这一步，但现在我被卡住了。我试图从类似的问题中获得帮助，但我没有足够的经验来修改它以满足我的需要。这是 google 表格的副本，“发送电子邮件”表格包含运行脚本的信息。更新：

https://docs.google.com/spreadsheets/d/1MBVhLj1A7Z_cpYxs_s5ae11ykJvTmS16E0jW4tGsNU4/edit?usp=sharing

function sendOrderEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Send Emails"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var dataRange = sheet.getRange("A2:I10");
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var emailAddress = rowData[0];
    var recipient = rowData[1];
    var message1 = rowData[2];
    var parameter1 = rowData[3];
    var message2 = rowData[4];
    var message3 = rowData[5];
    var parameter2 = rowData[6];
    var message4 = rowData[7];
    var emailSent = rowData[9];
    var message = 'Hi ' + recipient + ',\n\n' + message1 + parameter1 + ' ' + message2 + ' ' + message3 + parameter2 + '. ' + message4 + '\n\n' + 'Tri-Valley Youth League Softball';
    var subject = 'Order Reference Number ' + parameter2;
    if (emailSent != "EMAIL_SENT" ) { // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 9).setValue("EMAIL_SENT");
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

标签： google-apps-scriptgoogle-sheetsgmail

Answer 1

尝试这个：

function sendOrderEmails() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Send Emails");
  var startRow=2;
  var dataRange=sheet.getRange("A2:J10");//Include column 10 in the range
  var data=dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var rowData = data[i];
    var emailAddress = rowData[0];
    var recipient = rowData[1];
    var message1 = rowData[2];
    var parameter1 = rowData[3];
    var message2 = rowData[4];
    var message3 = rowData[5];
    var parameter2 = rowData[6];
    var message4 = rowData[7];
    var emailSent = rowData[8];//This is column 9
    var message = 'Hi ' + recipient + ',\n\n' + message1 + parameter1 + ' ' + message2 + ' ' + message3 + parameter2 + '. ' + message4 + '\n\n' + 'Tri-Valley Youth League Softball';
    var subject = 'Order Reference Number ' + parameter2;
    if (emailSent != "EMAIL_SENT" ) { 
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 9).setValue("EMAIL_SENT");
    }
  }
}

我这样做是为了让您可以看到不同的行和列以及数据索引。

function rowsColumnsAndIndices() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(1,1,26,26);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      vA[i][j]=Utilities.formatString('r: %s, c: %s\ni: %s,j: %s', i+1,j+1,i,j);
    }
  }
  rg.setValues(vA);
}