Information about nesting of custom function

Information about nesting of custom function

Posted by: david.gurshumov on 8 March 2023, 4:51 am EST

• 

  david.gurshumov

  • Post Options:
  • Link
    Copy

  Posted 8 March 2023, 4:51 am EST - Updated 8 March 2023, 4:56 am EST

  Hello!

  I need to determine whenever a custom function is only by it self in the formula (example: “=CUSTOMFUNC(1)”) or when it nested in another function (example: “=CONCAT(CUSTOMFUNC(1), “sample”)”)

  I’m seeing some private field about parent function in the custom function context, but I didn’t find a way to receive it with TypeScript typing.

  Is there a way to retrieve this information?

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 10 March 2023, 3:28 am EST

  Hello David,

  You can find if the custom function is nested inside another function or not by getting the formula inside the evaluate method of the custom function. Then you can get the expression from the formula using GC.Spread.Sheets.CalcEngine.formulaToExpression() method. On the expression object, you can use the expression.forEachNodes() methods to find the first node of the formula. If the first node is custom function, then it is not nested otherwise it is nested.

  Please refer to the code snippet and attached sample for further understanding.

  CustomFunction.prototype.evaluate = function (context) {
      let formula = sheet.getFormula(context.row, context.column);
      let expression = GC.Spread.Sheets.CalcEngine.formulaToExpression(sheet, formula, 0, 0);
      let firstNode;
      expression.forEachNodes((node) => {
          if (!firstNode) {
              firstNode = node;
          }
      });
  
      if (firstNode.type === GC.Spread.CalcEngine.ExpressionType.function && firstNode.functionName === 'CUSTOMFUNC') {
          console.log('custom function is not nested in another function');
      } else {
          console.log('custom function is nested in another function');
      }
  
      return 100;
  }

  Sample: https://jscodemine.grapecity.com/share/URpBKUdx0kyKFFxOg7brKw/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Please let us know if you still face any difficulties.

  Doc reference

  GC.Spread.CalcEngine.Functions.Function class:https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.CalcEngine.Functions.Function

  GC.Spread.Sheets.CalcEngine.formulaToExpression():https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#formulatoexpression

  GC.Spread.CalcEngine.Expression class: https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.CalcEngine.Expression

  Regards,

  Ankit