Custom function and array formulas

Custom function and array formulas

Posted by: ricardo on 14 November 2018, 2:58 am EST

• 

  ricardo

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 14 November 2018, 2:58 am EST

  Hi there,

  Is there any example (or directions) of how do define a custom function to be used in an array formula? What is the expected result type of the custom function? I’ve tried something like the code below, with no success.

  MyCustomFunction.prototype.evaluate = function() {
  
  return new Array(“1st”, “2nd”, “3rd”);
  
  // or return [“1st”, “2nd”, “3rd”];
  
  }

  Regards,
  
  Ricardo

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 15 November 2018, 9:12 pm EST

  Hello,

  There is no specific return type of custom function because it returns the result of the function applied to the arguments.

  Please have a look at attached sample which shows how you can define a custom function to be applied to array formulas.

  Hope it helps.

  Thanks,
  
  Reeva
  
  CustomFunction_ArrayFormulaV12.zip

• 

  ricardo

  • Post Options:
  • Link
    Copy

  Posted 15 November 2018, 9:40 pm EST

  Reeva,

  It does not work. It repeats all array values in all selected cells (see attachment).

  What worked for me was to return a CalcArray, as shown below.

  
          MyCustomFunction.prototype.evaluate = function () {
  
              function MyArray(array) { this.array = array; }
              MyArray.prototype = new GC.Spread.CalcEngine.CalcArray();
              MyArray.prototype.getRowCount = function () { return this.array.length; }
              MyArray.prototype.getColumnCount = function () { return this.array[0].length; }
              MyArray.prototype.getValue = function (row, col) { return this.array[row][col];}
  
              var myArray = new MyArray([["1st","2nd","3rd"]]);
              return myArray;
          }
  
  
  

  Best regards,
  
  Ricardo

• 

  ricardo

  • Post Options:
  • Link
    Copy

  Posted 15 November 2018, 9:41 pm EST - Updated 3 October 2022, 2:03 am EST

  

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 15 November 2018, 9:53 pm EST

  Hello,

  Thanks for correcting us. Here is the complete working sample and code for future reference:-

     <script type="text/javascript">
          $(document).ready(function () {
              var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
              initSpread(spread);
          });
          function initSpread(spread) {
              var spreadNS = GC.Spread.Sheets;
              var sheet = spread.getSheet(0);
              function Sample2ValueFunction() {
              }
              Sample2ValueFunction.prototype = new GC.Spread.CalcEngine.Functions.Function("SAMPLE", 0, 0);
              Sample2ValueFunction.prototype.evaluate = function () {
                  function MyArray(array) { this.array = array; }
                  MyArray.prototype = new GC.Spread.CalcEngine.CalcArray();
                  MyArray.prototype.getRowCount = function () { return this.array.length; }
                  MyArray.prototype.getColumnCount = function () { return this.array[0].length; }
                  MyArray.prototype.getValue = function (row, col) { return this.array[row][col]; }
  
                  var myArray = new MyArray([["1st", "2nd", "3rd", "4th"]]);
                  return myArray;
              }
              var samplefunc = new Sample2ValueFunction();
              $("#addCustomFunction").click(function () {
                  sheet.addCustomFunction(samplefunc);
                  sheet.setArrayFormula(0, 0, 1, 4, "=sample()");
              });
          };
  
      </script>
  

  Thanks,
  
  Reeva

  CustomFunction_ArrayFormulaV12(Modified).zip

• 

  ricardo

  • Post Options:
  • Link
    Copy

  Posted 21 November 2018, 9:41 pm EST

  Reeva,

  There seems to be an issue with custom functions used in array formulas. The function is called as many times as the number of cells in the array. I expected it to be called only once and the resulting array values used to fill the cells… Am I missing something?

  Ricardo

• 

  reeva.dhingra

  

  • Post Options:
  • Link
    Copy

  Posted 27 November 2018, 6:02 pm EST

  Hello Ricardo,

  An array formula is a formula that can perform multiple calculations on one or more of the items in an array.

  Hence, this is the design behavior because the custom function will be called as many times as the number of items in an array as the formula which gets calculated refers to a different cellrange everytime.

  Hope it clarifies.

  Thanks,
  
  Reeva