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