How do I format this date string so that google scripts recognizes it?

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?

score 21 · Accepted Answer · edited Jan 14 '17 at 05:49

21

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;
  }
}

edited Jan 14 '17 at 05:49

Tony BenBrahim

7,040
2
36
49

answered Aug 05 '12 at 21:43

mhawksey

2,013
5
23
61

Hi. The link to "ECMA-262 3rd Edition" is broken. Do you know any places where the Google "Date" object is documented (e.g. constructors, methods, etc.)? Thanks. – Kalin Mar 03 '15 at 01:12
1

[ECMA-262 Language Specification Page](http://www.ecma-international.org/publications/standards/Ecma-262.htm) <-Links to HTML and PDF Versions. [Date Objects Section Direct Link in HTML Documentation](http://www.ecma-international.org/ecma-262/5.1/#sec-15.9) – Pau Coma Ramirez Apr 29 '15 at 13:40
on line 2 of your regexp STRING, you need two backslashes before the dot, like so: \\. \. is not a valid escape character, and if you want a backslash in STRING, you need to escape it. Now, if you regex was between / and /, you would not need to escape backslash – Tony BenBrahim Jan 14 '17 at 05:47
Google Apps Script `new Date(String)` does not accept what ECMA-262 3rd Edition says it should. So apparently Google doesn't go by ECMA-262 3rd Edition. [lECMA-262 3rd Edition 15.9.3.2](http://www.ecma-international.org/ecma-262/5.1/#sec-15.9.3.2) says `YYYY-MM-DDTHH:mm:ss.sssZ+HH:mm` but Google only seems to like `YYYY/MM/DD HH:mm:ss +HHmm`. – balazer Apr 22 '19 at 08:51

score 9 · Answer 2 · answered Jun 22 '13 at 16:49

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);
}

This doesn't work for "2016-07-12T16:30:00.000+0200", mhawskeys answer is more reliable — Paul, Aug 04 '16 at 06:15

score 1 · Answer 3 · answered Aug 04 '12 at 18:24

1

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.

answered Aug 04 '12 at 18:24

Srik

7,907
2
20
29

@Srik...worked perfect. Thanks for your help. Any idea why google scripts doesn't recognize that date string? – jrad Aug 04 '12 at 19:21
Only works for time zones GMT-XX:XX, what about timezones which are ahead of GMT/UTC? – kksensei Aug 09 '16 at 23:47

score 1 · Answer 4 · answered Jan 01 '17 at 20:09

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"));

answered Jan 01 '17 at 20:09

NikitOn

448
4
10

**Note:** Google Sheet time zone and Google Script time zone are equal. – NikitOn Jan 01 '17 at 20:10

score 1 · Answer 5 · answered Nov 02 '19 at 23:30

1

As of now new Date() seems to work:

var dT = new Date("2012-08-03T23:00:26-05:00");
console.info("dT: %s or %d", dT, dT.getTime());

returns dT: Sat Aug 04 06:00:26 GMT+02:00 2012 or 1.344052826E12 in Google Apps Script

answered Nov 02 '19 at 23:30

B.No

85
8

How do I format this date string so that google scripts recognizes it?

5 Answers5

Linked