0

I get the HTTP header date into Google Sheets formatted as Tue, 08 Dec 2020 18:15:15 GMT. It seems to be usual Java date format, but Google Sheets doesn't recognize it as date.

I tried all in-built possibilities of Google Sheets to re-format date - no success.

How is this possible to manipulate HTTP header date from the current format into format recognized by Google Sheets as real date?

Evgeniy
  • 2,337
  • 2
  • 28
  • 68

1 Answers1

1

Check out this question Google Sheet API V4(Java) append Date in cells, where a similar problem is flagged.

Also check out:
Google Date Formats

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 found online:

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

source

timmyx
  • 166
  • 9