Why does a date length return Null?

Question

I have an array variable that get the values of a spreadsheet

I want to check if some of the cells are empty, so I look the length of each element of the array

But to my surprise, if the element is a date, the length returns "null". Shouldn't it return the full length of the date ? (e.g. Wed Dec 01 03:00:00 GMT-05:00 2021 should return 34)

Can someone explain this behaviour to me ?

Here is the code I use:

In my table the cell I1 is a date and the cell J1 is empty

var sheet = SpreadsheetApp.getActive().getSheetByName('Example');
    
var tab = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

Logger.log(tab[0][8]);
Logger.log(tab[0][8].length);
Logger.log(tab[0][9]);
Logger.log(tab[0][9].length);

This return:

1:28:09 PM  Notice  Execution started
1:28:15 PM  Info    Wed Dec 01 03:00:00 GMT-05:00 2021
1:28:17 PM  Info    null
1:28:18 PM  Info    
1:28:19 PM  Info    0.0

I think that the reason of null of tab[0][8].length is due to that the value is the date object. If you want to retrieve the length of the cell value, how about modifying var tab = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues(); to var tab = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues(); to var tab = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues();? By this, the cell values are retrieved as the string. By this, length returns the value except for null. — Tanaike
– Tanaike, Commented Mar 3, 2022 at 12:45
If my understanding of your situation was not correct, I apologize. — Tanaike
– Tanaike, Commented Mar 3, 2022 at 12:46
I think it is related to how js engine wrap it into wrapper object which is a string object which somehow .length of that object can't recognize it, i am also not clear why that happen, i am curious too. Although for now you can change the into .LocaleString() and can get the length. — vector
– vector, Commented Mar 3, 2022 at 12:55
If I use .getDisplayValues() the value changes to Dec-21 and the length given is now 6, but it seems to be considerably slower to fetch the data from the sheet with this method and I won't be able to do operations on the date itself (like add a month) as it is now a stored as a string in my array. — Tom
– Tom, Commented Mar 3, 2022 at 14:53
@Tanaike—you should post your comment as an answer, putting it in a comment just hides it from searches. — RobG
– RobG, Commented Mar 4, 2022 at 1:05

Wicket · Accepted Answer · 2022-03-04 02:24:06Z

Only Google Apps Script product engineers could answer why, but based on the https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date length is not a property of the Date object.

When using Logger.log or console.log to print any object to the IDE executions log, they will try to convert that object into a string according to the rules set to do that but Google Apps Script documentation doesn't provide details of what will be displayed when non string is passed. As you can see in the bottom each method have different results, Logger.log shows the same result as use Date.prototype.toString() while console.log prints the date time including the IATA timezone name.

JSON.stringify and Utilities.formatDate are methods for formatting Date object as strings, both work with the old and the new runtime. When using the default runtime (V8) you might also might other Date properties like Date.prototype.toDateString among others.

Please bear in mind that Date.prototype.toLocaleString will return the a result based on the server, on the user computer.

When using Google Sheets, please bear in mind that it has a different way to handle dates values than JavaScript. When using getValue / getValues Google Apps Script converts spreadsheet dates into a JavaScript Date objects based on the spreadsheet timezone. By the other hand getDisplayValue / getDisplyVAlues returns cell displayed values as string. You might use the number formatting options to format the date with certain limitations like not being able to display the timezone as the spreadsheet dates don't included it directly.

function myFunction() {
  const date = new Date();
  Logger.log(date);
  Logger.log(date.length);
  console.log(date);
  console.log(date.length);

}

10:39:27 AM Notice  Execution started
10:39:27 AM Info    Thu Mar 03 11:39:27 GMT-05:00 2022
10:39:27 AM Info    null
10:39:27 AM Info    Thu Mar 03 2022 11:39:27 GMT-0500 (Eastern Standard Time)
10:39:27 AM Info    undefined
10:39:27 AM Notice  Execution completed

References

I think that your answer will be useful. For example, in order to achieve the OP's goal, how about adding the following information? I thought that when the following information is added, your answer will be more useful. 1. In order to retrieve the cell values as the string, it uses getDisplayValue method. 2. At Google Apps Script, when the date object is shown with Logger.log, the result is the same with the value converted to the string by toString(). When setValues is required to be used, how about suggesting this?

Ricardo Jose Velasquez Cruz · Accepted Answer · 2022-03-03 17:25:50Z

0

Based on the comments we can edit the code and use both alternatives to convert the string and avoid the null.

var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
    
    
//var tab = sheet.getRange(1, 1, sheet.getLastRow()-1, sheet.getLastColumn()-1).getValues();

var tab = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues();

var date = new Date(tab[0][8]);

var date2 = date.toString();

Logger.log(tab[0][8]);
Logger.log(tab[0][8].length);
Logger.log(tab[0][9]);
Logger.log(tab[0][9].length);

Logger.log(date2.length)

answered Mar 3, 2022 at 17:25

community wiki

Ricardo Jose Velasquez Cruz

Collectives™ on Stack Overflow

Why does a date length return Null?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related