0
296
ViewsHow to get a date in a specified format in Google Sheets App script?
I have a function to compare a date in a cell of a column to today's date and create a column for today if it is missing.
function isColumn() {
var today = Utilities.formatDate(new Date(), 'GMT+1', 'dd.MM.yyyy')
ss = SpreadsheetApp.getActiveSpreadsheet()
s = ss.getSheetByName("Register")
var dateCell = s.getRange(2,3,1,1)
Logger.log(dateCell.setNumberFormat("dd.MM.yyyy").getValue())
Logger.log(today.valueOf())
if (dateCell.getValue().valueOf() === today.valueOf()) {
Logger.log("Today's column exists")
} else {
Logger.log("Create today's column")
}
}This is the log I always get
4:43:23 PM Info Thu Nov 11 00:00:00 GMT+01:00 2021
4:43:23 PM Info 11.11.2021
4:43:23 PM Info Create today's columnThe if condition can never evaluate as the date formats are wrong even though I used .setNumberFormat() method and the date displays in the correct format in the sheet.
How can I make the .getValue() method get the date in the correct format so I can evaluate the if condition?
2 answers
Answer question0
Use getDisplayValue() instead.
getDisplayValue() Returns the displayed value of the top-left cell in the range. The value is a String. The displayed value takes into account date, time and currency formatting formatting, including formats applied automatically by the spreadsheet's locale setting. Empty cells return an empty string.
Example:
function isColumn() {
var today = Utilities.formatDate(new Date(), 'GMT+1', 'dd.MM.yyyy')
ss = SpreadsheetApp.getActiveSpreadsheet()
s = ss.getSheetByName("Register")
var dateCell = s.getRange(2,3,1,1);
dateCell.setNumberFormat("dd.MM.yyyy");
Logger.log(dateCell.getDisplayValue());
Logger.log(today.valueOf())
if (dateCell.getValue().valueOf() === today.valueOf()) {
Logger.log("Today's column exists")
} else {
Logger.log("Create today's column")
}
}
Output:
Reference:
0
I found out a solution too, meanwhile.
function isColumn() {
var today = (new Date()).toLocaleDateString('cs-CZ')
ss = SpreadsheetApp.getActiveSpreadsheet()
s = ss.getSheetByName("Register")
var dateCell = s.getRange(2,3,1,1).setNumberFormat("dd.mm.yyyy")
if (dateCell.getValue() === "") {
Logger.log("Create today's column")
createColumn()
}else{
Logger.log(dateCell.getValue().toLocaleDateString ('cs-CZ'))
}
Logger.log(today)
if (dateCell.getValue().toLocaleDateString('cs-CZ') === today) {
Logger.log("Today's column exists")
} else {
Logger.log("Create today's column")
createColumn()
}
}Converting the dates to locale date strings also worked for making it possible to evaluate the if condition.
Notice how I avoided Utilities all together to prevent date incompatibilities.