Define the problem
My Excel add-in has custom functions which call external APIs. These API calls are invoking complex models running in the cloud which can sometimes take minutes to complete and therefore return data to Excel.
My users build large workbooks with potentially thousands of these functions. Often the user makes a change in their workbook which triggers all of these calculations again even though there's no change in the input data. An example would be insertion of a row or column.
This causes huge pain for the end user.
Solution
Microsoft to update the Office JS API so that when a function is invoked it can check the contents of the cell that invoked the function. I seem to remember this was possible in the old COM add-ins but it's not possible in the Office JS add-ins.
Why does this help
My custom functions store both the API request and response in the data card (Excel.EntityCellValue) format.
Since I effectively store the original API request in the cell, if the cell gets inadvertently invoked I can check the arguments of the functions (the input data) against the original API request and if nothing has changed then I can return the original API response stored in the cell.
This is like cancelling a function but being able to return the original data rather than anything new.
Alternatives
I can implement caching, e.g. using IndexedDB, but that's not the ideal solution. The ideal is the above.