I have a spreadsheet that currently has two tabs.
- Data: This tab has sample data
- 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
?