Custom Functions Executing without change in parameters.

[Sanjay Deepak]

My custom functions are executing at some interval (not regular) when the input parameters are not changing.  Trying to debug this cause as I am concerned about hitting the daily limit.

If the input parameters to the custom function are not changing why would the custom function get triggered?  There are no triggers setup in the project and the spreadsheet remains open throughout.  

Is there anyway to determine what is triggering the custom function?

[Sanjay Deepak]

Hi Adam,

Thanks for your quick response.  I am unable to find a "Formula" tab in google sheets.  Is this something I need to enable?   I have checked all the input parameters and they don't appear to be changing as they are fixed values.   Obviously something is triggering the custom functions.  Last night my computer was in sleep mode and these functions were executing 1 - 2 times an hour at no obvious time.  

On Saturday, December 3, 2022 at 11:55:42 PM UTC-5 adamkr...@gmail.com wrote:

If the input parameters to your custom function are not changing, and there are no triggers set up for the function, then it is likely that the function is being triggered by changes in other parts of the spreadsheet. Custom functions in Google Sheets are designed to automatically recalculate whenever there are changes in the spreadsheet that affect their input parameters or their output values.

For example, if you have a custom function that takes a cell range as an input parameter, and the values in that cell range are changed by another formula or by user input, then the custom function will be triggered to recalculate. Similarly, if the custom function returns a value that is used by another formula in the spreadsheet, then the custom function will be triggered whenever the other formula is recalculated.

To determine what is triggering the custom function, you can use the following steps:

1. Open the Google Sheets spreadsheet that contains the custom function.
2. Select the cell or cells that contain the custom function.
3. In the toolbar at the top of the screen, click on the "Formulas" tab.
4. In the "Formulas" tab, click on the "Tracing" button.
5. In the "Tracing" panel, click on the "Precedents" tab to see a list of all the cells and formulas that are affecting the custom function.

This will show you all of the cells and formulas that are triggering the custom function to recalculate. If you see any unexpected cells or formulas in the list, you can investigate further to determine the cause of the unexpected recalculations.

Alternatively, you can also use the "Evaluation Order" tab in the "Tracing" panel to see the order in which the custom function and other formulas in the spreadsheet are being evaluated. This can help you identify any potential problems with the calculation order that may be causing the custom function to be triggered unexpectedly.