How to create a custom function with two arguments passed

How to create a custom function with two arguments passed

Posted by: 19311a0572 on 12 June 2023, 3:34 am EST

• 

  19311a0572

  • Post Options:
  • Link
    Copy

  Posted 12 June 2023, 3:34 am EST

  I need to create the custom function that takes two arguments and evaluates the function based on the two arguments.
  
  For example i passed the two arguments 10,20 the custom funtions takes the two values and return the double of minimum value of those two arguments

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 13 June 2023, 6:49 am EST

  Hello,

  You can create a custom function by inheriting the GC.Spread.CalcEngine.Functions.Function class. You need to define/override the evaluate() method of the custom function to perform the calculation of the function.

  The evaluate() method gets the arguments and returns the result as per the logic implemented. To add the custom function to the sheet, you can use sheet.addCustomFunction().

  Please refer to the code snippet and attached sample which explains the same.

  function CustomFunction() {
      this.name = 'CUSTOMFUNC';
      this.maxArgs = 2;
      this.minArgs = 2;
  }
  CustomFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
  CustomFunction.prototype.evaluate = function (arg1, arg2) {
      // your own evaluate content
      let min = arg1 > arg2 ? arg2 : arg1;
      return 2 * min;
  }
  CustomFunction.prototype.description = function (arg1, arg2) {
      return {
          description: "Returns the double of the minimum of the two values",
      };
  }
  
  let customFunction = new CustomFunction();
  // adds the custom function to the sheet
  sheet.addCustomFunction(customFunction);
  
  sheet.setFormula(1, 1, 'CUSTOMFUNC(10, 20)');
  

  Sample: https://jscodemine.grapecity.com/share/XKbXkXUMYkSLm1jBqmTMbA/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Please let us know if you face any problems. We would help you accordingly.

  Doc references:

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

  Custom function demo: https://www.grapecity.com/spreadjs/demos/features/calculation/custom-functions/purejs

  sheet.addCustomFunction(): https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#addcustomfunction

  Regards,

  Ankit

• 

  19311a0572

  • Post Options:
  • Link
    Copy

  Posted 13 June 2023, 7:32 am EST

  suppose i need to pass more than two arguments it can be done in two ways.
  
  1)CUSTOMFUNC(A1,B1,C1,D1,E1)
  
  for this evaluate function will be function(…args){
  
  var x = args; //this x now will be an array of the values
  
  //logic here
  
  }
  
  2)CUSTOMFUNC(A1:E1)
  
  for this type how will be the evaluate function written and how are the arguments specified.
  
  This second type is like =SUM(A1:H1)

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 14 June 2023, 11:57 pm EST

  Hello,

  You can override the acceptsReference() method of the function. This method takes argument index and returns true or false for whether the function argument accepts references or not. If it returns true for an argument, then instead of value, a reference is passed in the evaluate() method of the function.
  
  You can use toArray() method on the reference of argument to get the value array and according implement your logic.
  
  Please refer to the code snippet and attached sample for more understaning.

  function CustomFunction() {
      this.name = 'CUSTOMFUNC';
      this.minArgs = 1;
      this.maxArgs = 255;
  }
  CustomFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
  CustomFunction.prototype.evaluate = function () {
      // your own evaluate content
      let minValue = Number.MAX_SAFE_INTEGER;
      for (let arg of arguments) {
          if (arg.toArray) {
              arg.toArray().forEach((val) => {
                  if (val < minValue) {
                      minValue = val;
                  }
              });
          } else if (Number.isInteger(arg) && minValue > arg) {
              minValue = arg;
          }
      }
      return 2 * minValue;
  }
  CustomFunction.prototype.acceptsReference = function (index) {
      return true;
  }
  // r

  eturns the description for the function
  
  CustomFunction.prototype.description = function (arg1, arg2) {
  
  return {
  
  description: “Returns the double of the minimum of the two values”,
  
  };
  
  }
  
  nction.evaluate(): https://www.grapecity.com/spreadjs/api/classes/GC.Spread.CalcEngine.Functions.Function#evaluate

  regards,
  
  Avinash