Apps Script中计划时间与当前时间对比异常问题求助

我有一段Apps Script代码用于计算并对比计划时间与当前时间，但当计划时间晚于当前时间时，对比结果始终为“YES”。尝试过用Utilities.formatDate强制设置日期时间格式，但问题依旧，怀疑是单元格日期时间格式或代码中的类型对比逻辑导致的。其中convertToTehranTime函数负责将时间转换为德黑兰时区，并存储到指定列（scheduledTime）。

原代码片段 #

主循环代码 #

for (var i = 2; i <= lastRowA; i++) {
  var row = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0];

  var scheduledTime = row[scheduledTimeColumnIndex];

  convertToTehranTime(i);
  var now = new Date();
  var scheduledDateTime = new Date(scheduledTime);
  scheduledDateTime = Utilities.formatDate(scheduledDateTime, "Asia/Tehran", "dd/MM/yyyy HH:mm");

  if (scheduledDateTime <= now) {
    sheet.getRange(i, 18).setValue("YES");
  }
}

时间转换函数 #

function convertToTehranTime(i) {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  // Retrieve the row data using the i value
  var rowData = data[i - 1];

  var timeString = rowData[12]; // Column M
  var timeZoneOffset = 3.5 - rowData[9]; // Column J
  
  // Convert the time string to a Date object
  var date = parseTimeString(timeString);
  
  // Calculate the new date and time in Tehran time
  var newDate = new Date(date.getTime() + timeZoneOffset * 60 * 60 * 1000); // Convert to Tehran time
  
  // Check if the scheduled date falls on a weekend (Saturday or Sunday)
  if (newDate.getDay() === 6 || newDate.getDay() === 0) { // Saturday or Sunday
    // Increment the date to the next working day
    newDate.setDate(newDate.getDate() + getNextWorkingDayOffset(newDate));
  }

  rowData[13] = newDate; // Column N

  // Update the sheet with the new data
  sheet.getDataRange().setValues(data);
}

问题根源分析 #

1. 类型不匹配的对比：Utilities.formatDate返回的是字符串类型，而now是Date对象。直接用字符串和Date对象比较时，JavaScript会强制转换类型，导致逻辑错误（比如字符串按字典序比较，而非时间先后）。
2. 代码顺序错误：先读取scheduledTime再调用convertToTehranTime，导致读取的是转换前的旧值，而非最新的德黑兰时区时间。
3. 时区未统一：now是本地时区时间，而scheduledDateTime是德黑兰时区的格式化字符串，两者时区不一致，对比无意义。

修复方案 #

1. 保持Date对象对比：全程使用Date对象进行时间比较，避免转换为字符串。
2. 统一时区：将当前时间也转换为德黑兰时区的时间，或统一使用UTC时间消除时区差异。
3. 调整代码顺序：先调用convertToTehranTime获取最新时间，再读取对应单元格的值。
4. 优化性能：避免在循环中反复调用getRange和getDataRange，减少对Spreadsheet API的调用次数。

修改后的代码 #

优化后的主循环 #

function compareScheduledTime() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var lastRowA = sheet.getLastRow();
  var scheduledTimeColumnIndex = 13; // 对应列N，索引从0开始
  var tehranTimeZone = "Asia/Tehran";

  for (var i = 2; i <= lastRowA; i++) {
    // 先更新德黑兰时间
    convertToTehranTime(i);
    // 获取该行最新数据
    var rowData = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0];
    var scheduledDateTime = rowData[scheduledTimeColumnIndex];

    if (!(scheduledDateTime instanceof Date)) {
      // 处理无效日期的情况
      sheet.getRange(i, 18).setValue("INVALID DATE");
      continue;
    }

    // 将当前时间转换为德黑兰时区的Date对象
    var now = new Date();
    var nowTehranStr = Utilities.formatDate(now, tehranTimeZone, "yyyy-MM-dd HH:mm:ss");
    var nowTehran = new Date(nowTehranStr);

    // 直接用Date对象对比时间先后
    if (scheduledDateTime <= nowTehran) {
      sheet.getRange(i, 18).setValue("YES");
    } else {
      sheet.getRange(i, 18).setValue("NO");
    }
  }
}

优化后的时间转换函数 #

function convertToTehranTime(i) {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  // 只操作目标行，提升性能
  var rowRange = sheet.getRange(i, 1, 1, sheet.getLastColumn());
  var rowData = rowRange.getValues()[0];

  var timeString = rowData[12]; // Column M
  var timeZoneOffset = 3.5 - rowData[9]; // Column J
  
  var date = parseTimeString(timeString);
  if (!date) {
    rowData[13] = "INVALID TIME";
    rowRange.setValues([rowData]);
    return;
  }
  
  var newDate = new Date(date.getTime() + timeZoneOffset * 60 * 60 * 1000);
  
  // 原逻辑：周六（6）和周日（0）为周末，调整到下一个工作日
  if (newDate.getDay() === 6 || newDate.getDay() === 0) {
    newDate.setDate(newDate.getDate() + getNextWorkingDayOffset(newDate));
  }

  rowData[13] = newDate; // Column N
  rowRange.setValues([rowData]);
}

关键修复点说明 #

• 避免字符串对比：始终使用Date对象进行<=操作，确保对比的是时间戳而非字符串顺序。
• 时区统一：将当前时间转换为德黑兰时区的Date对象，保证对比的是同一时区的时间。
• 性能优化：转换函数中只操作目标行，而非整个表格，减少不必要的API调用。
• 错误处理：增加无效日期的判断，避免因时间格式错误导致的逻辑异常。

内容的提问来源于stack exchange，提问作者Hamed