Having a hard time understanding Time Formatting-CodePudding

I have a spreadsheet with a lot of employee clock-in information on it, and in one of the columns, the time will appear as 24 hour time, example: "22:00". When I pull out this column as an array, "22:00" would appear in the Logs as "Sat Dec 30 22:00:00 GMT-08:00 1899". However, when I paste this value into another sheet, it appears like this: "12/30/1899"

I'm currently running a map method to reorganize this data, but my attempt to format the time for these cells has not been working. I would like for it to appear how it does in the first sheet, eg. "22:00".

const formatTime = function (time) {
  return Utilities.formatDate(time, "GMT 2", "HH:mm");
}

const correctOrder = filemakerData.map(function(r){
return [
  r[3], // Employee Name
  r[0], // Date
  r[1], // Building
  r[2], // SOR Number
  formatTime(r[4]), // Start Time
  formatTime(r[5]) // End Time
]
});

CodePudding user response：

Unfortunately, I cannot know your whole script, how about the following modifications?

Pattern 1:

In this pattern, the number formats are copied.

function sample1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Sheet1"); // Please set source sheet name.
  const dstSheet = ss.getSheetByName("Sheet2"); // Please set destinatione sheet name.

  const range = srcSheet.getRange("A1:F"   srcSheet.getLastRow());
  const filemakerData = range.getValues();
  const correctOrder = filemakerData.map(function (r) {
    return [
      r[3], // Employee Name
      r[0], // Date
      r[1], // Building
      r[2], // SOR Number
      r[4], // Start Time
      r[5] // End Time
    ]
  });
  const formats = range.getNumberFormats().map(r => [r[3], r[0], r[1], r[2], r[4], r[5]]);
  dstSheet.getRange(range.getA1Notation()).setValues(correctOrder).setNumberFormats(formats);
}

Pattern 2:

In this pattern, the values are retrieved by getDisplayValues().

function sample2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Sheet1"); // Please set source sheet name.
  const dstSheet = ss.getSheetByName("Sheet2"); // Please set destinatione sheet name.

  const range = srcSheet.getRange("A1:F"   srcSheet.getLastRow());
  const filemakerData = range.getDisplayValues();
  const correctOrder = filemakerData.map(function (r) {
    return [
      r[3], // Employee Name
      r[0], // Date
      r[1], // Building
      r[2], // SOR Number
      r[4], // Start Time
      r[5] // End Time
    ]
  });
  dstSheet.getRange(range.getA1Notation()).setValues(correctOrder);
}