使用应用程序脚本比较Google工作表中的日期

Question

我有一个GoogleApps电子表格，其中一列包含日期和时间。这张纸是由几张纸合并而成的。在某些行上，该值是一个日期（即typeof sheet.getRange（i，2）.getValue（）==“object”，带有日期的方法）。其他时间，此单元格是一个数字（即typeof sheet.getRange（i，2）.getValue（）=“number”），但单元格格式将单元格显示为一个日期。

例如，日期为2015年5月16日星期六上午9:30

如果typeof firstDate==“number”，则该值为42140.395833333336

如果typeof firstDate==“object”，则“valueOf”为1431783000000

此函数的目的是在日期发生变化时添加边框，以便每天显示在单独的单元格组中。当前，此函数在日期和数字之间的数据类型发生变化时添加额外的边框。

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Add Borders', functionName: 'Add_Borders'}
  ];
  spreadsheet.addMenu('Scheduling', menuItems);
}

/**
 * Retrieves all the rows in the active spreadsheet that contain data and logs the
 * values for each row.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function Add_Borders() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow();
  var cols = sheet.getLastColumn();
  var firstDate=new Date();
  var secondDate = new Date();

//Logger.log(sheet.getParent().getName() + " ! " + sheet.getName());

  for (var i = 2; i <= numRows - 1; i++) {
    firstDate = sheet.getRange(i,2).getValue();

    Logger.log("row " + i + " Date type is " + typeof firstDate)
    if (typeof firstDate == "number"){
      Logger.Log ("row " + i + " number value = " + firstDate)
    }
    else {
      Logger.Log ("row " + i + " date value = " + firstDate.valueOf())
      Logger.Log ("row " + i + " numeric value = " + firstDate)
    }


    firstDate = (typeof firstDate == "number") ? new Date(firstDate * 86400) : firstDate
    secondDate = sheet.getRange(i+1,2).getValue();
    secondDate = (typeof secondDate == "number") ? new Date(secondDate * 86400) : secondDate
 // Logger.log("row " + i + " first date = " + firstDate + " firstDate.getDate() = " + firstDate.getDate() + "; firstDate.getMonth() = " + firstDate.getMonth())
 // Logger.log("row " + i + " second Date = " + secondDate + " secondDate.getDate() = " + secondDate.getDate() + "; secondDate.getMonth() = " + secondDate.getMonth())
    if (firstDate.getDate() != secondDate.getDate() || firstDate.getMonth() != secondDate.getMonth()){
      sheet.getRange(i, 1, 1, cols).setBorder(null, null, true, null, null, null);
    }
    else {
      sheet.getRange(i, 1, 1, cols).setBorder(null, null, false, null, null, null);
    }
  }
};

Answer 1

经过一些实验后，我最终给出了一些定义：对于数字，值是自1899年12月31日起在当地时区的天数；日期。getTime（）返回自UTC 1970年1月1日以来的毫秒数。

因此，以下函数强制值：

function FixDate(dte){

  // If dte is a number then it contains a date in the form of the number of days since
  // 00:00 on 12/31/1899 in the current time zone

  if (typeof dte == "number"){
     return dte
  }

  // If dte is a date object then the getTime property contains the date as the number of
  // milliseconds since 00:00 on January 1, 1970 in GMT.  This value is converted to be
  // compatible with the other format

  return (dte.getTime() / 86400000) + 25569 - dte.getTimezoneOffset() / (60 * 24)
}