Custom Functions and Recalculation

问题:

I have a custom function that accepts three range names as input parameters. When values of cells in these ranges change, the function output stays the same. This is a "feature" of the platform.

Is there any proper way (as of yet) to make custom functions in Google Sheets recalculate dynamically? I know people have tried adding now() as a parameter, which used to continuously recalculate regardless of need. (Now blocked in new sheets.) Has anyone found a satisfactory solution?

回答1:

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 Sheets

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... indefinitely.

From a comment 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(), or RANDBETWEEN()