防止重复的电子邮件Google电子表格脚本

Question

我目前能够发送电子邮件与谷歌电子表格脚本。但是我的触发器和if条件并没有阻止我想要的电子邮件发送：

这是我的代码：‘

function myFunction() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("MASTER");

  const h3 = 'SPP Proyek JIS Tanggal xx dari xxx';
  const headers = ws.getRange("A2:M2").getValues();
  const item = headers[0][4];
  const spec = headers[0][5];
  const sat = headers[0][6];
  const qty = headers[0][7];
  const price = headers[0][8];
  const total = headers[0][9];
  const tujuan = headers[0][10];

  const lr = ws.getLastRow();

  const tableRangeValues = ws.getRange(3, 5,lr-2,7).getDisplayValues();
  const trigger = ws.getRange(3, 1,lr-2).getValues();
  const statusEmail = ws.getRange(3, 13,lr-2).getValues();

  const htmlTemplate = HtmlService.createTemplateFromFile("Email");
  htmlTemplate.h3 = h3;
  htmlTemplate.headers = headers;
  htmlTemplate.item = item;
  htmlTemplate.spec = spec;
  htmlTemplate.sat = sat;
  htmlTemplate.qty = qty;
  htmlTemplate.price = price;
  htmlTemplate.total = total;
  htmlTemplate.tujuan = tujuan;
  htmlTemplate.tableRangeValues = tableRangeValues;
  htmlTemplate.trigger = trigger;
  htmlTemplate.statusEmail = statusEmail;

  const htmlForEmail = htmlTemplate.evaluate().getContent();

  if ((trigger != 'FALSE') && (statusEmail != 'EMAIL_SENT')); {

  GmailApp.sendEmail(
    "sistem.jis@gmail.com", 
    "Approval SPP Komersial", 
    "HTML Support",
    { htmlBody: htmlForEmail }
   );

  ws.getRange(3, 13,lr-2).setValue('EMAIL_SENT');

}

“”“

这是我的示例文件链接：https://docs.google.com/spreadsheets/d/13TKIhY7HmK3o-j98q45XXb2nwZzfYwyYn7EULhY_RJw/edit#gid=1216091331

似乎我在触发器和if条件代码方面有问题，我不明白

谢谢!

Answer 1

定义const trigger = ws.getRange(3, 1,lr-2).getValues();将返回[[FALSE], [true], [FALSE], [true]]类型的数组

要使代码正常工作，您需要定义一个循环，该循环遍历每一行(和触发器) invidually

• Also，从if ((trigger != 'FALSE') && (statusEmail != 'EMAIL_SENT')); {

中删除

示例：

function myFunction() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("MASTER");

  const h3 = 'SPP Proyek JIS Tanggal xx dari xxx';
  const headers = ws.getRange("A2:M2").getValues();
  const item = headers[0][4];
  const spec = headers[0][5];
  const sat = headers[0][6];
  const qty = headers[0][7];
  const price = headers[0][8];
  const total = headers[0][9];
  const tujuan = headers[0][10];
  const lr = ws.getLastRow();
  const tableRangeValues = ws.getRange(3, 5,lr-2,7).getDisplayValues();
  var data = ws.getRange(3, 1,lr-2,13).getDisplayValues();
  for (var i = 0; i < data.length; i++){
    const trigger = data[i][0];
    const statusEmail = data[i][12];

    const htmlTemplate = HtmlService.createTemplateFromFile("Email");
    htmlTemplate.h3 = h3;
    htmlTemplate.headers = headers;
    htmlTemplate.item = item;
    htmlTemplate.spec = spec;
    htmlTemplate.sat = sat;
    htmlTemplate.qty = qty;
    htmlTemplate.price = price;
    htmlTemplate.total = total;
    htmlTemplate.tujuan = tujuan;
    htmlTemplate.tableRangeValues = tableRangeValues;
    htmlTemplate.trigger = trigger;
    htmlTemplate.statusEmail = statusEmail;

    const htmlForEmail = htmlTemplate.evaluate().getContent();
    Logger.log(trigger);
    if ((trigger != 'FALSE') && (statusEmail != 'EMAIL_SENT')) {
        GmailApp.sendEmail(
      "sistem.jis@gmail.com", 
      "Approval SPP Komersial", 
      "HTML Support",
      { htmlBody: htmlForEmail }
      );

      ws.getRange(3, 13,lr-2).setValue('EMAIL_SENT');
    }
  }
}

注意：在此示例中，我没有修改tableRangeValues，因为这些值稍后会在客户端处理。根据您希望它们是什么样子，您可能还希望迭代throguh tem。