Array of values in custom function

Array of values in custom function

Posted by: khauam.cardoso on 9 November 2023, 1:25 pm EST

• 

  khauam.cardoso

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 9 November 2023, 1:25 pm EST

  I need to pass several lists of values ​​as parameters and the function receives the cell values
  
  ex: =CUSTOM(A1:A8,B1:B5)

  Today I can only receive the list of values ​​using acceptsArray = true but I always need to pass which sheet I’m using
  
  ex: =CUSTOM(Sheet1!A1:Sheet1!A8,Sheet1!B1:Sheet1!B5)
  
  even when using just one sheet or inserting the formula in the same sheet as the cells

  my function:
  
  var customFunction = function () {
  
  };
  
  customFunction.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction(“CUSTOM”, 1, 255, null);
  
  customFunction.prototype.defaultValue = function () {
  
  return “Loading…”;
  
  };
  
  customFunction.prototype.acceptsArray = function () {
  
  return true;
  
  };
  
  customFunction.prototype.evaluate = function (context, …parametros:any) {

          console.log(parametros)
  }
  

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 10 November 2023, 4:16 am EST

  Hi,

  For this, you may need to use the acceptsReference method to return true. Please refer to the following code snippet and let me know if you face any issues.

  customFunction.prototype.acceptsReference = function () {
  
  return true;
  
  };

  API: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.CalcEngine.Functions.Function#acceptsreference

  Regards,
  
  Avinash

• 

  khauam.cardoso

  • Post Options:
  • Link
    Copy

  Posted 10 November 2023, 7:14 am EST

  Hello, I tried to use acceptsReference but when I send several arrays from different sheets I cannot identify where the value comes from to create the array based on the rows and columns.

  Is there a way to get which sheet the values ​​come from based on the reference? and not in the context or current sheet

  EX: =CUSTOM(A1:E1,Sheet2!A1:E,Sheet3!A1:E1)

  in angular:

  function Custom() {
      this.name = "CUSTOM";
      this.minArgs = 1;
      this.maxArgs = 10;
  }
  
  Custom.prototype = new GC.Spread.CalcEngine.Functions.Function();
  
  Custom.prototype.acceptsReference = function (index) {
      return true;
  }
  Custom.prototype.isContextSensitive = function () {
          return true;
  }
  Custom.prototype.evaluate = function (context, reference) {
      let row = reference.getRow();
      let col = reference.getColumn();
      let rowCount = reference.getRowCount();
      let colCount = reference.getColumnCount();
  
      var sheet = context.source.getSheet();
      var dataArr = sheet.getArray(row, col, rowCount, colCount);
  
      return dataArr.toString();
  }
  

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 13 November 2023, 1:44 am EST

  HI,

  For this, you may use getSheet method from which you can get the sheet object of the parameter. Please refer to the following code snippet and let me know if you face any issues.

  FactorialFunction.prototype.evaluate = function (arg, arg2, b) {
    // your own evaluate content
    console.log("arg 1 sheet name is :", arg.getSource().getSheet().name());
    console.log("arg 2 sheet name is :", arg2.getSource().getSheet().name());
  };

  Regards,
  
  Avinash