Power Automate runs the script every day (08:00). In this script I compare the current date with another date (column C). This check is done by the formula. The result is placed in column D. The final check is to put information into the array only if the number (Column D) is <=24.

The problem is that when Power Automate runs the script the formula doesn't update column D. If I open the workbook the formula is updated and the script works.

Example Sheet:

In column D there is this formula (cell D2) =D2-$E$2 and so on. In the column E2 there is the current date with formula =today()

Example code:

function main(workbook: ExcelScript.Workbook) {

  //first step. update formula
   workbook.getApplication().calculate(ExcelScript.CalculationType.full);

  // Get the "MyWorksheet" worksheet from the workbook.
  let selectedSheet = workbook.getWorksheet("MyWorksheet");
  let range = selectedSheet.getUsedRange();
  let rangeValues = range.getValues();

  const arr= [];

  for(let i=0; i< rangeValues.length; i++){
      
      //compare the number (column D)
      if(rangeValues[i][3]<=24){
        arr.push(rangeValues[i][0]);
      }
  }  
}

Where am I wrong?

I tried with manual and automatic calculation mode but without result.

To temporarily solve the problem, decrease the value in column D by 1 every day without using the formula.