JSFiddle - React, Tailwind, and code Playground

by pierian_design

JavaScript

function crGetOnlyNumbers(aCellValues) {
  var aNumbersOrDate = aCellValues.filter(function(uCurrent) {
    switch ( Object.prototype.toString.call(uCurrent).slice(8, -1) ) {
      case 'Number':
      case 'Date':
        return true;
      default:
        return false;
    }
  });
  var aNumbers = aNumbersOrDate.map(function(uCurrent, key) {
    if ( Object.prototype.toString.call(uCurrent).slice(8, -1) === 'Date') {
      return crConvertUnixDateToOADateNumber(uCurrent, key);
    } else {
      var currentNumber = Number(uCurrent);
      if ( !isNaN(currentNumber) && isFinite(currentNumber) ) {
        return currentNumber;
      }
      return 0;
    }
  });
  return aNumbers;
}

function crConvertUnixDateToOADateNumber(date, sKey){
  var epoch = new Date('12/30/1899');
  var msPerDay = 24 * 60 * 60 * 1000;
  var oCache = CacheService.getScriptCache(); //10 minutes by default
  var sCacheKey = 'timeDifference_' + sKey;
  if (oCache.get(sCacheKey)) {
    var differenceBetweenSheetAndServerTime = oCache.get(sCacheKey);
  } else {
    var time = Number(date - epoch);
    var oaDate = time / msPerDay;
    var differenceBetweenSheetAndServerTime = crGetDifferenceBetweenSheetAndServerTimeInMS(date, oaDate);
    oCache.put(sCacheKey, differenceBetweenSheetAndServerTime,10);
  }

  var timeWidthDifference = Number(date - epoch - differenceBetweenSheetAndServerTime);
  var oaDateWidthDifference  = timeWidthDifference / msPerDay;

  return oaDateWidthDifference;
}

function crGetDifferenceBetweenSheetAndServerTimeInMS(date, oaDate) {
  var oCellForExperiment = SpreadsheetApp.getActiveSheet().getActiveCell();
  var oCellValue = oCellForExperiment.getValue();
  var oCellNumberFormat = oCellForExperiment.getNumberFormat();
  oCellForExperiment.setValue(oaDate).setNumberFormat('[h]:mm:ss');
  var oSheetTime = oCellForExperiment.getValue();
  var oServerTime = new Date(date);
  oCellForExperiment.setValue(oCellValue).setNumberFormat(oCellNumberFormat);
  var difference =...