如何比较Google Apps脚本/表格中的IF语句中的当前编辑时间与指定标准时间? [英] How to compare current edited time vs. specified criteria time in an IF Statement in Google Apps Script/Sheets?

问题描述

目标:根据用户何时将D列下的单元格更改为"Chat =>"，将当前日期或明天的日期插入到用户编辑的K列/行下的单元格中.电子邮件."而且，如果可能的话，我希望日期的比较和输出基于JST(日本标准时间)时区.

Goal: Insert the current date or tomorrow's date to a cell under Column K/Row of user edit, depending on when the user changes a cell under Column D to "Chat => Email." And if possible, I want the comparison and output of the date to be based on the JST (Japan Standard Time) time zone.

我的代码上下文:在下面的代码中，我创建了一个名为"insertDeadlineDate()"的函数.它从另一个.gs文件接收onEdit()信息.在insertDeadlineDate()代码中，我试图创建一个if语句来检查以下几点并执行结果.

Context of my code: In my below code, I've created a function called "insertDeadlineDate()" which receives onEdit() information from another .gs file. Within the insertDeadlineDate() code, I'm trying to create an if statement to check the following points and execute the result.

1. 如果用户将单元格值更改为"Chat =>电子邮件"如果当前时间在上午9:30之间，则为AND和下午2:00(JST)，然后将当天的日期插入到用户编辑的K列/行中.
2. 如果用户将单元格值更改为"Chat =>电子邮件"并且，如果当前时间在下午2:00之间和下午6:30(JST)，然后将当天的日期插入到用户编辑的K列/行中.

仅供参考:由于我不知道该写些什么，所以IF语句为空.对不起...

FYI: I have the IF statements blank because I don't know what to write. I'm sorry...

function insertDeadlineDate(range, sheetName, row, column) {
  const statusSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Status');
  const editedRangeValue = range.getValue();
  
  // Column numbers for Status sheet
  const caseTypeColumnStatus = 4;

  if (sheetName == 'Status' && column == ticketTypeColumnStatus && row > 15 && editedRangeValue == 'Chat => Email') {
    var statusColumn = column + 3;
    if () { // If the current time is between 9:30 A.M. and 2:00 P.M. (JST), then TRUE. 
      // Insert today's date
      statusSheet.getRange(row, statusColumn).setValue(new Date());
    } else if () { // If the current time is between 2:00 P.M. and 6:30 P.M. (JST), then TRUE.
      // Insert tomorrow's date
      statusSheet.getRange(row, statusColumn).setValue();
    }
  }
}

示例工作表屏幕截图:

推荐答案

由于未传递任何参数，因此进行了一些调整，这直接是 onEdit .

I have made some adjustments since I am not passing any parameter, this is onEdit directly.

function onEdit(e) { 
  const sheetName = 'Status';
  const statusSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const cell = statusSheet.getActiveCell();
  const row = cell.getRow();
  const column = cell.getColumn();
  const editedRangeValue = cell.getValue();

  // var currentDate = new Date(new Date().toLocaleString('en-US', { timeZone: 'Japan' }));

  // The above line of code translates your time into japan timezone but since 
  // sheets is converting your timezone automatically to the regional one, 
  // it adjusts an additional day when used. Be careful in using the above line.

  var currentDate = new Date();

  // Use the line below to test different time slots, we do not want to wait the 
  // exact time just to test this script.
  // currentDate.setHours(11, 30, 0 ,0);

  startDate = new Date(currentDate.getTime());
  startDate.setHours(9, 30, 0, 0);

  endDate1 = new Date(currentDate.getTime());
  endDate1.setHours(14, 0, 0, 0);

  endDate2 = new Date(currentDate.getTime());
  endDate2.setHours(18, 30, 0, 0);

  // Column numbers for Status sheet
  const caseTypeColumnStatus = 4;

  if (sheetName == 'Status' && column == caseTypeColumnStatus && row > 15 && editedRangeValue == 'Chat => Email') {
    var statusColumn = column + 3;
    if (startDate <= currentDate && endDate1 > currentDate) { // If the current time is between 9:30 A.M. and 2:00 P.M. (JST), then TRUE. 
      // Insert today's date
      statusSheet.getRange(row, statusColumn).setValue(currentDate);
    } else if (endDate1 <= currentDate && endDate2 > currentDate) { // If the current time is between 2:00 P.M. and 6:30 P.M. (JST), then TRUE.
      // Insert tomorrow's date
      var tomorrow = new Date(currentDate.setDate(currentDate.getDate() + 1));
      if(tomorrow.getDay() == 6){ 
        // if saturday, add 2 days 
        tomorrow.setDate(tomorrow.getDate() + 2);
      } 
      else if(tomorrow.getDay() == 0){
        // if sunday, add 1 day
        tomorrow.setDate(tomorrow.getDate() + 1);
      } 
      statusSheet.getRange(row, statusColumn).setValue(tomorrow);
    }
  }
}

这篇关于如何比较Google Apps脚本/表格中的IF语句中的当前编辑时间与指定标准时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！