dateTime string format (2021-09-18T09:00) being changed to 2021-09-18 09:00 when filing to sheet

user_16675774 0
Viewed 52

The sheet originally stored date-time strings in ISO-8601 ("T"<hh:mm>, i.e. "2021-06-30T21:00"). For some reason, upon trying to file these date-times as a string, the format of the date-time in the cell is being applied to " "<hh:mm> (ISO-8601 without the "T"). I added a logger.log just prior to the filing and I can see that the "T" is still in the string. Any ideas?

Note that the code hasn't changed in 2 months and this started happening 2 weeks ago. Thinking something has changed with google sheets auto-correcting date-time strings

Logger.log(data);

sheet.setActiveRange(range).setValues(data);

var LastRow = sheet.getLastRow();

Log "data":

[10.0, 29, 6.0, 2021-09-17T21:00, 2021-09-18T09:00, Yes, , 209, mail@mail.com, Sun Aug 15 2021 23:35:45 GMT-0400 (Eastern Daylight Time), Yes, , , , ]]"

How it's displaying in the sheet (note the date format in piece 3 and 4 is changed in the sheet)

10 29 6 2021-09-17 21:00 2021-09-18 9:00 Yes 209 mail@mail.com Sun Aug 15 2021 23:35:45 GMT-0400 (Eastern Daylight Time) Yes

I tried something like below and it seemed to have no affect.

sheet.getRangeList(["D2:D"+LastRow]).setNumberFormat('@STRING@');
google-apps-script
2 Answers

2021-09-18 09:00 is the default display format for datetime in your spreadsheet.

The type is time but not string such that you should format it with something like M-D H:m.

user_161251390

Note that formatting the value after it was written especially on strings like this will not likely fix the issue.

You need to format the cells right before they are written.

Sample code:

function myFunction() {
  data = [[10.0, 29, 6.0, "2021-09-17T21:00", "2021-09-18T09:00", "Yes", , 209, "mail@mail.com", "Sun Aug 15 2021 23:35:45 GMT-0400 (Eastern Daylight Time)", "Yes", , , , ]];
  
  sheet = SpreadsheetApp.getActiveSheet();
  lastRow = sheet.getLastRow();
  // set range D2:E to string before applying data
  sheet.getRange(2, 4, lastRow + 1, 2).setNumberFormat('@STRING@');
  // write data 
  sheet.getRange(lastRow + 1, 1, data.length, data[0].length).setValues(data);
  // in your case, applying string format after will not fix the data written before 
}

Output:

output

user_161186860
Related