How to add the formula TODAY() for programming a validation between two dates in apps script

Question

I have a table where we add fails that occurs in stations (is for a work Project), the first date to add is the scalation date, and the second one is the attention date, i want the attention date to be between the scalation date and today, because obviously you cant say you attended a station in the future.

The thing is that when i créate the validation in the Google sheets, i can easily put the two values i want which are the cell ‘D10’ and the formula ‘TODAY()’, and that Works pretty fine, but im making a form in apps script so these validations have to be added automaticly by code, so i recorded a macro to see how it Works in code and i got this:

spreadsheet.getRange('H10').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .setHelpText('Introduce una fecha entre el =D10 y el =HOY()')
  .requireDateBetween(new Date(1899, 11, 30), new Date(1899, 11, 30))
  .build());

The code is in spanish because thats the languaje im working with) but as you can see, the description of the validation says its between ‘D10’ and ‘HOY()’(TODAY()), but in the ‘requireDateBetween’ it uses the exact same date for both start and end: ‘1899, 11, 30’, again i have to say, that validation Works prefectly fine, but using it again it has not effect, anyway i modified it to use it in another cell and créate the validation throug code in that cell:

var ss= SpreadsheetApp.getActiveSpreadsheet()
  var form= ss.getSheetByName('REGISTRAR_FALLA')

  form.getRange('H11').setDataValidation(SpreadsheetApp.newDataValidation().setAllowInvalid(false).setHelpText('Introduce una fecha entre el =D10 y el =HOY()').requireDateBetween(new Date(form.getRange('D11').getValue()), new Date()).build());

So with the code like this i get the start date from the cell ‘D11’ and the today’s date to the end, but this today’s date does not work like the ‘TODAY()’ formula, so tomorrow its not going to be updated, it Will still remain in today’s date, i uploaded some pics so you can see how its shown in the Google sheets:

Validación hecha con el propio google sheets y validación hecha con apps script

So what I wanna know is if its posible to add that formula in code to the validation so it gets updated.

Answer 1

You can use the custom formula datavalidation using .requireFormulaSatisfied:

.requireFormulaSatisfied(`=ISBETWEEN(H2,0,TODAY())`)

Make sure H2 is the same range you're applying the data validation on.