Weird inconsistencies in writing a date into Google sheets

Question

I'm seeing weird inconsistencies and discrepancies when writing constructed dates into google sheets, they do not always transform into date object once the sheet values are retrieved at a later time.

By constructed dates I mean they are created by sticking together smaller day, month and time strings into a single string "01/02/1991 00:00:00". They are then written into the sheet using .setValues(). And then after work is done, I retrieve the array using .getValues() to only find that once in a while, those dates are retrieved as string values, with the vast majority being date objects.

Originally, I had not padded out the values, but now I add (inside the string) leading 0s. This has fixed issues in some placed but not all places.

Here are some excerpts of how I create the array that gets written eventually using .setValues

  var date = data_range[i][header_date].toString()
  var day = parseInt(date.slice(6,8),10)
  var month =  parseInt(date.slice(4,6),10)
  var year =  parseInt(date.slice(0,4),10)

  organised_data[organised_data_index].data = organised_data[organised_data_index].data.concat(data_range[i].slice(header_data, header_data+int_entries))

  var datetime_array = []
  for (var k = 0; k< minutes_array.length; k++){
    datetime_array[k] = pad(day,2) + "/" + pad(month,2) + "/"+ year + " " + pad(parseInt(minutes_array[k]/60),2)+ ":" + pad((minutes_array[k] % 60),2) + ":00" 
  }
  organised_data[organised_data_index].datestamp = organised_data[organised_data_index].datestamp.concat(datetime_array)

Here is what I'm seeing in the debug: https://i.imgur.com/OTT296s.png

Here is what I'm seeing in the sheet itself: https://i.imgur.com/hDvqGP3.png

Has anyone else faced these issues, am I doing something wrong?

So for anyone asking why I've written my dates like this, it's because the script will be passed around between countries, and I've noticed that the localisation changes and therefore using date objects inside the javascript gets really screwey. I've opted to create the string itself since the data is time zone agnostic. Could we treat that side of my problem as a constraint?

Thanks guys, this is killing me.

Welcome. Others may know the answer to this straight off the bat. In my case, I need to see the spreadsheet and code. Would you please share a copy of your spreadsheet (excluding any private or confidential information)? — Tedinoz
Questions looking the same kind of help of this one should include a minimal reproducible example. — Rubén
Does the inconsistency always occur for the cases when the time is 00:00:000? Would be nice to see how you set and get values. — ziganotschka
Hi Wholemeal - this is an interesting bug. When I generate 15,000 strings using your "dd/MM/YYYY hh:mm:ss" format and put them in a sheet, over 9,000 of them come back as strings instead of date objects when I read them back. When I use the format "MMM dd, YYYY hh:mm:ss", I get 15,000 dates back. I will update my answer if I get a more definitive answer. Can you provide more of your own code and sheet data to better understand what you're doing? Have you tried a different string format? How are you staying "time zone agnostic"? Have you tried Date.UTC()? — dwmorrin

Cooper Cooper · Accepted Answer · 2019-08-09T02:33:26

I don't actually see your inconsistencies I wish I could see all of your code.

Another problem with Date objects is that Passing Date() objects with google.script.run is not allowed and will cause the entire object to loose it's data. For that reason I have used a function like this to produce date strings that I can easily pass and then be used as constructors for dates on the server. On some projects I like to eliminate the timezone altogether and just assume that the time zone of the spreadsheet is the same as the WebApp user even though it often is not.

function formatDateTime(dt){
  if(dt && Object.prototype.toString.call(dt) === '[object Date]'){
    var M=dt.getMonth()+1;
    var d=dt.getDate();
    var h=dt.getHours();
    var m=dt.getMinutes();
    var s=dt.getSeconds();
    var MM=(M<10)?String('0'+M):String(M);
    var dd=(d<10)?String('0'+d):String(d);
    var hh=(h<10)?String('0'+h):String(h);
    var mm=(m<10)?String('0'+m):String(m);
    var ss=(s<10)?String('0'+s):String(s);
    var ds=dt.getFullYear() + '-' + MM + '-' + dd + 'T' + hh + ':' + mm;
    return ds;
  }else{
    throw("Invalid Parameter Type in formatDateTime Code.gs.  Parameter is not a date.");
  }
}

Reference

Weird inconsistencies in writing a date into Google sheets

2 Answers