How to compare current edited time vs. specified criteria time in an IF Statement in Google Apps Script/Sheets?

Question

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.

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.

If the user changed the cell value to "Chat => Email" AND if the current time is between 9:30 A.M. and 2:00 P.M. (JST), then insert the current day's date to Column K/Row of user edit.
If the user changed the cell value to "Chat => Email" AND if the current time is between 2:00 P.M. and 6:30 P.M. (JST), then insert the current day's date to Column K/Row of user edit.

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();
    }
  }
}

Sample Sheet Screenshot:

Just to clarify the scenario, what should happen if the current time is between 6:30 PM (JST) and 9:30 AM - or can that scenario for your script never occur? — kirkg13
Thanks for the question here!! So if the current time is between 6:30 PM to 9:30 AM, then I'd prefer the script to do nothing! — John Tashiro

NaziA NaziA · Accepted Answer · 2021-01-07T14:48:50

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);
    }
  }
}

How to compare current edited time vs. specified criteria time in an IF Statement in Google Apps Script/Sheets?

1 Answers