javascript - 用于电子表格的 Google 脚本(If 语句)

Question

我希望有人能帮我解决这个问题。我是编码新手。我有一个谷歌电子表格，其中包含一个可以发送电子邮件的脚本。我试图让脚本忽略脚本已经发送电子邮件的行。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Email Menu')
    .addItem('Send Email', 'myFunction')
    .addSeparator()
    .addToUi();
};

function myFunction() {
  var msg = 'Email sent!';

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var EMAIL_SENT = "EMAIL_SENT";
  var startRow = 2;

  for (i=1; i<values.length;i++) {

    //sent = values[i][4]; //added this in to try and skip a row if "X" was in Sent Column

    //if (sent ==" ", ) //added this in to try and skip a row if "X" was in Send Column

    var email = values[i][0];
    var name = values[i][i];
    var subject = values[i][2];
    var body = values[i][3];
    var sent = values[i][5];

    if (sent != EMAIL_SENT){
      MailApp.sendEmail(email, subject, body);
      sheet.getRange(startRow + i,5).setValue(EMAIL_SENT);
      SpreadsheetApp.getUi().alert(msg);
    }
 }}

Google spreadsheet

Answer 1

在您的 for 循环中实现以下更改:

for (i=1; i<values.length;i++) {

  var email = values[i][0];  
  var name = values[i][1]; //not [i][i] !  
  var subject = values[i][2];
  var body = values[i][3];  
  var sent = values[i][4];

  if (sent !== EMAIL_SENT){
    sheet.getRange(i+1,5).setValue(EMAIL_SENT);
  }

}

基本上摆脱 startRow 变量(如果您没有在其他地方使用)，只需使用 (i+1,5) 将“EMAIL_SENT”放在右列中。为了清楚起见，我删除了您的部分代码。