Contrary as occurs with built-in functions like
=NOW() that return an new value every time that the spreadsheet is recalculated, custom functions are recalculated only when their arguments change. So if we have a sheet which ID is
503917557, then the resulting formula is
if the name of the sheet with ID =
503917557 is changed, the argument of the above formula is still the same, so it will not be recalculated.
It's worthy to note that if we overwrite a cell with exactly the same formula, the formula will not be recalculated either, because the optimization algorithm of Google Sheets see this action as "nothing happened".
On previous versions of Google Sheets Google Apps Script allowed the use of functions like NOW(), TODAY() and other deterministic built-in functions as custom function arguments, but nowadays they aren't allowed.
The current workarounds are
Use the web browser refresh feature as custom functions are recalculated on opening the spreadsheet
Add a dummy argument, like adding a number that is incremente every time that we want the formula to be recalculated, example:
Update the cell value by using a script able to be called from an installable trigger, custom menu, button, etc.
Below is my original answer.
From my answer to Custom Functions and Recalculation
The proper way to make that a custom function to recalculate is to
change a parameter of it. Regarding the use of NOW() and other similar
built-in functions as paremeters of custom functions, from Custom
functions in Google
Custom function arguments must be deterministic.
That is, built-in
spreadsheet functions that return a different result each time they
calculate — such as NOW() or RAND() — are not allowed as arguments to
a custom function. If a custom function tries to return a value based
on one of these volatile built-in function, it will display Loading...
by Mogsdad to this answer:
In fact, rather than "Loading...", this will display an #ERROR!, This function is not allowed to reference a cell with NOW(), RAND(),