Google Scripts比较日期

Question

我正在尝试运行一个脚本，该脚本接收单元格的日期，将其与今天进行比较，如果相差4天，则运行该脚本。我不确定如何让if语句工作。目前，它将发送电子邮件，但不会检查声明是否正确。我不确定如何将它合并到工作表中。

到目前为止，看看我的代码

 var checkStatus = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses").getRange("K:K"); 
 var status = checkStatus.getValue();*
 
  
 var formattedDate = Utilities.formatDate(new Date(), "GMT-4", "MM-dd-yyyy");
  
 if(status != "closed" && "NEED THIS TO BE DATE OF ENTRY" > (today+4)'{*
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").getRange("A1");
    var emailAddress = emailRange.getValue();
    var message = 'It looks like there is a ticket that has not yet been closed in your sheet. Please check the sheet and ensure all customers are taken care of!'
    var subject = 'Tech Support Ticket Over-Due';
    MailApp.sendEmail(emailAddress, subject, message);
    }
}

​

​

基本上，我希望它给我发电子邮件，如果A列中有任何日期早于4天，并且在L列中没有“已关闭”的状态。

Answer 1

计算提交日期和今天之间的天数，然后检查是否大于4。

Here's一个如何计算天差的例子。(为了简化，我删除了UTC调整。请阅读该答案的注释，并了解哪些内容对您的方案有意义。)

function daysBetween(startDate, endDate) {
  var millisecondsPerDay = 24 * 60 * 60 * 1000;
  return (endDate - startDate) / millisecondsPerDay;
}

因此，要遍历回复并向4天前提交的未“关闭”的回复发送电子邮件，您可以这样做：

function doSomething() {
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses").getDataRange().getValues();
  var now = new Date();
  
  for (var i = 0; i < responses.length; i++) {
    var response = responses[i];
    var submittedAt = response[0]; // Column A
    var status = response[10]; // Column K
    
    var daysSinceSubmission = daysBetween(submittedAt, now);
    
    if (status != "closed" && daysSinceSubmission > 4) {
      // Send email
    }
  }
}