Cell that contains an hour change when I paste it to a new sheet-CodePudding

I'm using this simple script to paste data into a new spreadsheet. In column C I have an hour in this format 05:00:00 When the script pastes the value, this hour changes! I can't understand why. Is it about a time zone somewhere? Thanks j.

function copyDataToArchive() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();; // sss = source spreadsheet
  var ss = sss.getSheetByName("1. Dispatching"); // ss = source sheet
  //Get full range of data
  var sRange = ss.getRange('a6:l80');
  //get A1 notation identifying the range
  //var A1Range = SRange.getA1Notation();
  //get the data values in range
  var sData = sRange.getValues();

var tss = SpreadsheetApp.openById("IDPasteSheet"); // tss = target spreadsheet
    var ts = tss.getSheetByName("ARCHIVAGE"); // ts = target sheet
    ts.getRange("a" (getLastDataRowArchivage(ts) 1) ":l" (getLastDataRowArchivage(ts) 75)).setValues(sData);

CodePudding user response：

A solution was found in the comments but for future reference I'd like to add an explanation of why this happened.

When copying values, getValues() gets just the raw data within the cell without taking into account the format. Then when you use setValues(), by default the format in previously unused cells is set to "Automatic", so Sheets will just guess the format based on the values and adjust accordingly. With dates/times this can cause issues.

getDisplayValues() works because it takes into account the formatting to read exactly what's displayed and turn it into a String. Sheets is better at parsing this since it's closer to what users normally paste. I guess it does something like "prioritizing" the intended display value.

Maybe there would be some fringe scenarios where a string would not work, so I would recommend you use copyTo() instead if you plan to use the values in the range the same way and keep the formatting, since that's what the method is designed to do. In your case it probably would look like this:

function copyDataToArchive() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();; // sss = source spreadsheet
  var ss = sss.getSheetByName("1. Dispatching"); // ss = source sheet

  var sRange = ss.getRange('a6:l80');

var tss = SpreadsheetApp.openById("IDPasteSheet"); // tss = target spreadsheet
    var ts = tss.getSheetByName("ARCHIVAGE"); // ts = target sheet
    sRange.CopyTo(ts.getRange("a" (getLastDataRowArchivage(ts) 1) ":l" (getLastDataRowArchivage(ts) 75)));