Filter on worksheet columns re-triggering excel office.js custom functions even when it is not...

Excel re-triggers custom functions created in office.js api addin, but from documentation it should only re-trigger the formulas which are volatile function and in office.js api to specify custom function as volatile it should be specified as @volatile which is not there in this case. what my guess is it is because of invocation object but still it should not re-trigger as filter only hides the row and not changes the value but still it is getting re-triggered. the strange part is it only re-triggers when there is cell-reference passed as arguments in custom function. I am attaching how we have setup our custom function. Need to know if this can be fixed ? if yes how and if not then what is the root cause ?

@customfunction Q.GET
@param en
* @param attri
* @param [date]
* @param [period]
* @param [fu]
* @param [Up]
* @param [uploadDate]
* @param [ver]
* @param [curr]
* @param [uni]
* @param [seg]
* @param [sec]
* @param [fx]
* @param {CustomFunctions.Invocation} invocation Invocation object.
* @returns {string[][]} A dynamic array with multiple results.
*/
async function GET(
en,
attri,
date,
period,
fu,
Up,
uploadDate,
ver,
curr,
uni,
seg,
sec,
fx,
invocation,
) {
var result = await getData(
[
entity,
attributeName,
date,
periodType,
fund,
trueUp,
uploadDate,
version,
currency,
units,
segment,
security,
fx,
invocation.address,
],
batchObject,
);
return [[result]]

}


functions.json declaration file

"functions": [
{
"description": "",
"id": "GET",
"name": "GET",
"options": {
"requiresAddress": true
},
"helpUrl": "",
"parameters": [
{
"type": "string"
},
{
"type": "string"
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{

"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
},
{
"type": "string",
"optional": true
}
],
"result": {
"dimensionality": "matrix",
"type": "string"
}
}]


removed the name and description for security reasons from json file

office version Version 2510 Build 16.0.19328.20244) 64-bit platform : Desktop

Continue reading...