Custom function with range arguments

Custom function with range arguments

Posted by: dean.kinnear on 14 March 2023, 8:05 am EST

• 

  dean.kinnear

  • Post Options:
  • Link
    Copy

  Posted 14 March 2023, 8:05 am EST - Updated 14 March 2023, 9:08 am EST

  When a custom function has a range for an argument, I just get the argument’s value. Is there a way to get the argument’s range object (and then the worksheet object from range)?

  Thanks!

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 16 March 2023, 12:29 am EST

  Hello,

  You can get the references to cell ranges in the evaluate() method of the custom function by overriding the acceptReference() method of the custom function. The acceptReference(index) method takes an argument for argument index of function and returns true if a particular formula argument can take references to cells otherwise it returns false. You can specify which arguments of custom function can take references.
  
  Please refer to the code snippet and attached sample.
  
  Custom.prototype = new GC.Spread.CalcEngine.Functions.Function();

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

  Custom.prototype.evaluate = function (reference) {
  
  let row = reference.getRow();
  
  let col = reference.getColumn();
  
  let rowCount = reference.getRowCount();
  
  let colCount = reference.getColumnCount();

  // creates a range object
  let range = new GC.Spread.Sheets.Range(row, col, rowCount, colCount);
  
  return JSON.stringify(range);
  

  }
  
  Sample:https://jscodemine.grapecity.com/share/vUFVSMRAsk6z877sQRhs8w/?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.accepsReference(): https://www.grapecity.com/spreadjs/api/classes/GC.Spread.CalcEngine.Functions.Function#acceptsreference

  GC.Spread.Sheets.Range class: https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Range#class-range

  regards