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

  }
}

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

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 column

<iframe name="sif2" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

The 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?

CodePudding user response：

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:

• getDisplayValue

CodePudding user response：

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

<iframe name="sif3" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

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.