Custom functions and recalculation
I have a custom function that takes three range names as input parameters. When the values of the cells in these ranges are changed, the output of the function remains unchanged. This is a "feature" of the platform.
Is there any correct way (for now) to have custom functions in Google Sheets recalculated dynamically? I know people tried to add now()
as a parameter which was used to recalculate continuously regardless of the need. (Now locked on new sheets.) Has anyone found a satisfactory solution?
source to share
The correct way to recalculate a custom function is to change its parameter. Regarding the use of NOW () and other similar built-in functions like custom function parameters, from Custom Functions in Google Sheets
Custom function arguments must be deterministic . That is, built-in table functions that return a different result each time they evaluate - for example, 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 functions, Loading ... ad infinitum will be displayed.
From a comment from Mogsdad to this answer:
In fact, instead of "Loading ...", this will display #ERROR !, This function cannot refer to a cell with NOW (), RAND () or RANDBETWEEN ()
source to share
Problem: -
A custom script in a spreadsheet cell will only run when the parameters of our custom function defined in the cell have changed or have been changed. If they remain the same, the earlier result is flushed from the cache.
Possible solutions: -
AND)
Obviously you can use Now () on any cell and then pass that cell to your function parameter. Make sure to change spreadsheet settings -> calculation -> replace every minute. (This will change the function parameter every minute and thus the script will be executed)
The updated spreadsheet doesn't seem to allow Now () or Rand () or something like that function anymore to be passed indirectly / directly to the custom function.
B)
Add an Update menu item and maybe go to your script and define a function that changes the cell value when you click the Update menu. Pass the value as a parameter to your function where you run your script.
FROM)
Go to your script and make a function. Let's say updateCellValue (). Now define the same logic to change the cell value.
Example: SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
Now go to Edit-> Current Trigger Project and create a trigger that will fire your function (updateCellValue) after every custom time limit. This will do the same as solution B, but you don't have to click the Refresh button in the menu.
Sounds like a bit of a hack, but I probably feel like the only way to update the spreadsheet at the moment is for custom functionality like fetching data dynamically from the server.
source to share