How to fix the date and time formatting in Google Sheets when running script?

Question

I have a spreadsheet that currently has two tabs.

1. Data: This tab has sample data
2. Results: This tab has the same information from the sample tab, but "attendees" are split where there is a comma to a new row

I am currently running this script:

// Main of this script.
function result(range) {
  var output = [];
  for (var i in range) {
    var celLen = 1;
    var c1 = range[i].map(function(e, i){
      var cell = e.toString().split(", "); // Modified
      var len = cell.length;
      if (len == 1) {
        return cell[0];
      } else if (len > 1) {
        celLen = celLen > len ? celLen : len;
        var t2 = [];
        for (var k=0; k<cell.length; k++) {
          t2.push(cell[k]);
        }
        return t2;
      }
    });
    var c2 = c1.map(function(e, i){
      var r = [];
      if (!Array.isArray(e)) {
        for (var k=0; k<celLen; k++) {
          r.push(e);
        }
      } else {
        for (var k in e) {
          r.push(e[k]);
        }
        if (e.length < celLen) {
          for (var m=0; m<celLen - e.length; m++) {
            r.push("");
          }
        }
      }
      return r;
    });
    var c3 = c2[0].map(function(e, i){return c2.map(function(f, j){return c2[j][i]})});
    Array.prototype.push.apply(output, c3);
  }
  return output;
}

// For testing this script.
function main() {
  var ss = SpreadsheetApp.getActiveSheet();
  var data = ss.getDataRange().getValues();
  var r = result(data);
  ss.getRange(ss.getLastRow() + 1, 1, r.length, r[0].length).setValues(r);
}

and have =result(Data!A1:Z) in the second tab for results.

But in column A (Timestamp) & B (Date) in the results tab the format changes.

For example: The timestamp originally was 6/11/2020 14:31:46 and it got changed to Thu Jun 11 2020 14:31:45 GMT-0700 (GMT-07:00).

I have already tried changing the format directly on Google Sheets, but that does not work. Is there a way to prevent that from happening and leaving it as 6/11/2020 14:31:46?

Answer 1

The problem occurs because your script parsing as string (toString)for all the values.

You should add a condition to return date values as date or convert the resulting string to Date but the last is error prone, specially if you are not familiar with how Google Apps Script read and writes Date values.

In your script instead of

var cell = e.toString().split(", "); // Modified

try

var cell = (e instanceof Date && !isNaN(e)) ? [e] : e.toString().split(", ");

Disclaimer: I didn't tested yet.

Reference

• Detecting an “invalid date” Date instance in JavaScript