Home > database >  How to convert date/time format from yyyy-mm-ddT00:00:00Z to yyyy-mm-dd 00:00:00 using google apps s
How to convert date/time format from yyyy-mm-ddT00:00:00Z to yyyy-mm-dd 00:00:00 using google apps s

Time:04-06

I pulled delivery date/times from easypost api using apps scripts. When I write the date/time to the sheet, it is in the wrong format. It's yyyy-mm-ddT00:00:00Z and it says it's text not a date. I need help either running a script to make the change after the data hits the sheet. Can anyone please help?

var parsed = JSON.parse(response.getContentText());
    activeSheet.getRange(i, 26).setValue(parsed.status);

if (parsed.status == 'delivered'){
  var deliveredEvents = parsed.tracking_details.filter(function (el) {
    return el.status == 'delivered';
  });

  if (deliveredEvents.length == 1) {
    activeSheet.getRange(i, 22).setValue(deliveredEvents[0].datetime);

Here's how the dates look in the sheet:

Deliver Date 2022-03-18T16:03:42Z

CodePudding user response:

If the date string is in a format that the constructor recognizes then:

activeSheet.getRange(i, 22).setValue(new Date(deliveredEvents[0])).setNumberFormat("yyyy-MM-dd");

If it doesn't recongize it the you may have to extract fullYear,month, and date and create the Date() object yourself. You don't really format the date you can format the way the spreadsheet represents it or you can post it as a string in which case it's no long a Date() object

based upon your example:

let t = "1969-12-31".split('-');
let dt = new Date(t[0],t[1]-1,t[2]);
  • Related