How do I format this date string so that google sc

Question

I'm getting a json feed with a date string formatted like the following:

//2012-08-03T23:00:26-05:00

I had thought I could just pass this into a new Date as such

var dt = new Date("2012-08-03T23:00:26-05:00");

This works on jsfiddle but not in google scripts. It's returning an invalid date. After reading this post I recognize it may be how GAS interprets the date string so now I'm not sure how to reformat the date to make it work.

Is there a best way to reformat that date string so GAS can recognize it as a date?

Answer 1

This worked for me, when converting date-string to date-object in Google Script.

The date-string was taken from the Google Sheet cell by getValues() method.

From: 01.01.2017 22:43:34 to: 2017/01/01 22:43:34 did the job. And then the new Date().

var dateTimeObj = new Date(stringDate.replace(/^(\d{1,2})[-.](\d{1,2})[-.](\d{4})/g,"$3/$2/$1"));

Answer 2

The hard and sure shot way to make it work if the format is known beforehand is to parse it.

var dtString = "2012-08-03T23:00:26-05:00";
var date = dtString.split('T')[0]; 
var time = dtString.split('T')[1].split('-')[0]; 
var tz = dtString.split('T')[1].split('-')[1]; 
var dt = new Date(date.split('-')[0] , date.split('-')[1] - 1,  // month is special
                  date.split('-')[2], time.split(':')[0],
                  time.split(':')[1], time.split(':')[2] , 0);

I haven't tested this exact piece of code, but have used similar code. So, this gives you a fair idea of how to proceed.

Answer 3

Found this other possible and very simple code that seems to also do the job :

function test(){
  Logger.log(isoToDate("2013-06-15T14:25:58Z"));
  Logger.log(isoToDate("2012-08-03T23:00:26-05:00"));
  Logger.log(isoToDate("2012-08-03T23:00:26+05:00"));
}

function isoToDate(dateStr){// argument = date string iso format
  var str = dateStr.replace(/-/,'/').replace(/-/,'/').replace(/T/,' ').replace(/\+/,' \+').replace(/Z/,' +00');
  return new Date(str);
}

Answer 4

Google Apps Script uses a particular version of JavaScript (ECMA-262 3rd Edition) and as you have discovered can't parse date/times in ISO 8601 format. Below is a modified function I use in a number of my Apps Scripts

var dt = new Date(getDateFromIso("2012-08-03T23:00:26-05:00"));

// http://delete.me.uk/2005/03/iso8601.html
function getDateFromIso(string) {
  try{
    var aDate = new Date();
    var regexp = "([0-9]{4})(-([0-9]{2})(-([0-9]{2})" +
        "(T([0-9]{2}):([0-9]{2})(:([0-9]{2})(\\.([0-9]+))?)?" +
        "(Z|(([-+])([0-9]{2}):([0-9]{2})))?)?)?)?";
    var d = string.match(new RegExp(regexp));

    var offset = 0;
    var date = new Date(d[1], 0, 1);

    if (d[3]) { date.setMonth(d[3] - 1); }
    if (d[5]) { date.setDate(d[5]); }
    if (d[7]) { date.setHours(d[7]); }
    if (d[8]) { date.setMinutes(d[8]); }
    if (d[10]) { date.setSeconds(d[10]); }
    if (d[12]) { date.setMilliseconds(Number("0." + d[12]) * 1000); }
    if (d[14]) {
      offset = (Number(d[16]) * 60) + Number(d[17]);
      offset *= ((d[15] == '-') ? 1 : -1);
    }

    offset -= date.getTimezoneOffset();
    time = (Number(date) + (offset * 60 * 1000));
    return aDate.setTime(Number(time));
  } catch(e){
    return;
  }
}