Custom Functions and Recalculation

Question

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?

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

Answer 2

The Problem:-

A custom script on a cell of a spreadsheet will only execute itself when the parameters of our custom function defined on the cell have changed or have been manipulated. If they remain same, the earlier result is driven from the cache.

Possible Solutions:-

A)

Obviously you may use Now() on any cell and then pass that cell to your function parameter. Make sure to change the spreadsheet settings -> calculation -> onchange every minute. (This will change the parameter of the function every minute and thus the script will be executed)

Updated Spreadsheet doesn't seem to be allowing Now() or Rand() or such functions anymore to be passed indirectly/directly to the custom function.

B)

Add a 'Refresh' menu item and maybe go to your script and define a function which changes the value of a cell when you click the 'Refresh' menu. Pass the value as a parameter to your function where you are executing your script.

C)

Go to your script and make a function. Let's say updateCellValue(). Now define the same logic here which is aimed at changing the value of a cell.

Example: SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());

Now go to Edit->Current Project's trigger and create a trigger that will trigger your function (updateCellValue) after every custom-defined time limit. This will do the same thing as solution B but you won't have to click the Refresh button of menu.

Seems like a bit of hack but probably I feel that's the only way as of now to update the spreadsheet for custom-defined functions like fetching data dynamically from a server.