0

I have looked at various solutions posted i.e. parsing, substrings and splitting and none of them either produce a value or the required value.

The format received via Salesforce API is "2014-08-19T02:26:00.000+0000"

Essentially I would like a custom function that can be used within Google Sheets to convert this date/time format and take daylight saving into consideration

Thank you beforehand

Gadget
  • 71
  • 1
  • 1
  • 3
  • You can write a simple parse function that splits the string into it it's parts (e.g. `s.split(/\D/)`), then use the *Date* constructor with values. It should need only 2 lines of code, a couple more if validation is required. – RobG Apr 17 '16 at 20:47
  • Javascript Date provides toLocaleString function. So, new Date("2014-08-19T02:26:00.000+0000").toLocaleString(); should be enough, I think, providing the document/spreadsheet/script has Locale setting set properly. – jad Apr 17 '16 at 20:48
  • @jad— *toLocaleString* provides an implementation dependent string representing the local time and varies greatly from browser to browser. The standard *toString* method provides a more consistent string (though still browser dependent). But that's not the OP's issue. – RobG Apr 17 '16 at 20:54
  • Take a look at the following question and answer: [Link to SO Question](http://stackoverflow.com/questions/36344156/google-apps-script-and-rfc-3339-issue) – Alan Wells Apr 17 '16 at 21:19
  • The question should be complete within itself, it should not require the title. It's not clear to me what you are trying to achieve, do you need help parsing the string, or formatting it as a local date and time? Some code attempting one or the other would help greatly (and is expected before answers will be given). – RobG Apr 17 '16 at 23:17

2 Answers2

0

I use a simple function like below :

function parseDate(string) {
  var parts = string.split('T');
  parts[0] = parts[0].replace(/-/g, '/');
  var t = parts[1].split(':');
  var refStr = new Date(new Date(parts[0])).toString();// use this to get TZ  for daylight savings
  var fus = Number(refStr.substr(refStr.indexOf('GMT')+4,2));
  return new Date(new Date(parts[0]).setHours(+t[0]+fus,+t[1],0));
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • It does not make sense to strip the date from the time part. If you are going to trust the Date constructor to parse strings (which isn't recommended) then the time and zone should be left attached. It is also strongly recommended to not trust the built–in *toString* method, it is entirely implementation dependent and may return different strings on different implementations. The host timezone offset is available reliably using [*getTimezoneOffset*](http://www.ecma-international.org/ecma-262/6.0/#sec-date.prototype.gettimezoneoffset). – RobG Apr 17 '16 at 23:15
  • Oh, and the above ignores the minutes part of the offset (if it's present at all), so zones that have offsets like 0430 will be incorrectly converted. And it uses only the date part, which will be parsed as UTC in most browsers so will not get the right local date or time so if daylight saving applies, it will get it wrong for about 24 hours around the changeover times. – RobG Apr 17 '16 at 23:26
  • @Rob I understand your comment and I appreciate but keep in mind that this post is specifically about Google Apps Script which is not browser depending since it runs on server side. That said, I agree the way I get the tz is not very elegant :-) I'll think about something better. – Serge insas Apr 18 '16 at 05:54
  • Ok, I guess swap "browser" with "implementation". The OP seems to infer that it runs on the client. Does Google Apps script provide its own Date implementation? – RobG Apr 18 '16 at 06:08
0

firstly thank you for everyone's input. By using a combination of the info provided by RobG and Serge insas I revised the script and created one that suited my needs. Please see below, any further advice would be welcome.

/*

The script first has all variables declared.
As the script runs inconjunction with an API query running off single trigger for defined sequential functions where the previous parsed date records are cleared and then re-parsed and runs with loop function for a whole column of data within specified range

*/

function parseDate() {
  var source_spreadsheet = SpreadsheetApp.openById("Sheet_Id");
  SpreadsheetApp.setActiveSpreadsheet(source_spreadsheet);
  var sheet = source_spreadsheet.getSheetByName("Sheet_Tab");
  var startRow = 2;
  var numRows = 4500;
  var startCol = 1;
  var numCols = 7;
  var dataRange = sheet.getRange(startRow, startCol, numRows, numCols)
  sheet.getRange(startRow, startCol + 1, numRows, numCols - 1).clear({contentsOnly: true});
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var SFConnectDate = row[0];
    var DConnected = row[1];
    var SFCutoverDate = row[2];
    var DInUse = row[3];
    var Lat = row[5];
    var Long = row[6];
    if (SFConnectDate != "" && DConnected == "" && Lat != "" && Long != "") {
        var parts = SFConnectDate.split('T');
        parts[0] = parts[0].replace(/-/g, '/');
        var Fdd = parts[0].split('/');
        var AllTime = parts[1].split('.');
        var Ftt = AllTime[0].split(':');
        var D = new Date(Fdd[0],(Fdd[1]-1),Fdd[2] ,Ftt[0],Ftt[1],Ftt[2]);
        var TZ = (D.getTimezoneOffset())/60;
        var DConnected = new Date(Fdd[0],(Fdd[1]-1),Fdd[2],(Ftt[0]-TZ),Ftt[1],Ftt[2]);
        sheet.getRange(startRow + i, 2).setValue(DConnected);
    }
  }
}
Gadget
  • 71
  • 1
  • 1
  • 3