javascript - 如何确保使用触发器仅发送一次

Question

我已经通过脚本自动发送电子邮件。但是我只想在单元格C：C“完成”时自动发送并且如果单元格12中有[K]数字1，则在发送电子邮件时会自动写入数字1。但是自动触发器总是发送电子邮件。

表格的样子： https ://docs.google.com/spreadsheets/d/1jgAdvV1UcAnh9qw3QQU9q_8MVSIyZmrukhOVxz3CJUA/edit#gid=0

// This constant is written in column L for rows for which an email
// has been sent successfully.
var EMAIL_SENT = '1';

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 1; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3); //
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var text = sheet.getRange("D2").getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var message = row [2]; // Third column
    var emailSent = row[3]; // Fourth column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      var subject = 'Text is'+ text +'';


      MailApp.sendEmail('[e-mail]', subject, message);
      sheet.getRange(startRow + i, 12).setValue(EMAIL_SENT);//sheet.getRange(startRow + i, 12).setValue(EMAIL_SENT)
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

要发送电子邮件，它只会在单元格 D 中搜索更改为“完成”的状态

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

看起来你在准备这个问题上并没有付出太多努力。

var EMAIL_SENT = '1';
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 1; //This should be changed...perhaps to sheet.getLastRow()-startRow+1
  var dataRange = sheet.getRange(startRow, 1, numRows, 3); //
  var data=dataRange.getValues();
  var text=sheet.getRange("D2").getValues();//This should be getValue() and column D in your sheet is State so this is probably wrong
  for (var i=0;i<data.length;++i) {
    var row = data[i];
    var message = row [2];//This is your data insert column so this is not your message
    var emailSent = row[3];//This is not column12 as it is in your spreadsheet so that's wrong 
    if (emailSent != EMAIL_SENT) { //this does not work as it stands now
      var subject = 'Text is'+ text +''; //This text is not defined on your spreadsheet
      MailApp.sendEmail('[e-mail]', subject, message);//There is no subject nor any intelligent message
      sheet.getRange(startRow + i, 12).setValue(EMAIL_SENT);
    }
  }
}