Reading array parameter in custom function

Reading array parameter in custom function

Posted by: jk on 19 July 2019, 9:10 pm EST

• 

  jk

  • Post Options:
  • Link
    Copy

  Posted 19 July 2019, 9:10 pm EST

  Hi,

  I wonder if I could read items of arrays like below for custom processing:
  
  =CUSTOMFUNCTION({1,2,3;4,5,6})
  
  =CUSTOMFUNCTION({"A","B","C";"D","E","F"})
  
  =CUSTOMFUNCTION({B2,B3,B4;C2,C3,C4})

  in:
  
  CustomFunction.prototype.evaluate = function () {…}

  Thanks a lot!

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 22 July 2019, 2:06 am EST

  Hi,

  We are sorry but parsing JSON value as formula parameters are not supported. This behaviour is the same as in MS Excel. Could you please let us know about your use case in detail so that we may suggest the best possible alternative.

  Regards

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 29 July 2019, 7:02 am EST

  Hi Jacek,

  I’m sorry but my understanding was not correct. Upon further investigation, we have found that the array parameters are actually supported in SpreadJS too. For enabling array input we need to override the “acceptsArray” method on the custom function and set the formula using the setArrayFormula method, please refer to the following sample which demonstrates the same:
  
  https://codesandbox.io/s/spread-js-starter-99smp

  With the above sample, we could achieve the following two scenarios:
  
  =CUSTOMFUNCTION({1,2,3;4,5,6})
  
  =CUSTOMFUNCTION({"A","B","C";"D","E","F"})
  
  But for the third scenario, =CUSTOMFUNCTION({B2,B3,B4;C2,C3,C4}), we have asked the dev team if it is supported. Internal tracking ID for issue is SJS-1163.

  Regards

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 7 August 2019, 3:06 am EST

  Hi Jacek,

  Regarding the third scenario, i.e. =CUSTOMFUNCTION({B2,B3,B4;C2,C3,C4}).
  
  DevTeam has confirmed that it is not supported, the reason being that the curly braces surrounding an array constant signify “constant” to Excel’s formula parsing engine. As a result, you can’t include references or functions in an array constant, since doing so would make an array constant variable (i.e. not a constant).
  
  Please refer to the following link for more info:
  
  https://exceljet.net/glossary/array-constant

  Regards