How can I return a Matrix/Array from a custom function which flows into other cells?

[MartinDawson]

Hi, Apologies if I'm too early with this and it's not actually possible yet as I understand this isn't even released yet in the npm package but I'm using your develop branch to get the Matrix features as it really is an excellent feature that was added.

This PR added that capability I think: #671

Basically I want to type in something like this: =FIN("incomeStatement") and for it to flow into other cells.

Here's what I have tried:

class FinancialPlugin extends FunctionPlugin {
    financial({ args }) {
      if (!args.length) {
        return new InvalidArgumentsError(1);
      }

      const attribute = args[0].value;

      if (args.length === 1) {
        // incomeStatement should be a Matrix/Array which flows into multiple cells.
        if (attribute === "incomeStatement") {
          return [
            ["revenue", 1500],
            ["operating income", 3000],
          ]
        };
      }

The above example with the nested array just return it in a single cell though.

So something like this: ={1,2;3,4} works well directly setting it in the cellContents but I want to do it in my own custom plugin.

Thanks!

[izulin]

Currently the only way to do it is to wrap result in internal type SimpleRangeValue, e.g.

return SimpleRangeValue.onlyValues([
            ["revenue", 1500],
            ["operating income", 3000],
          ])

this type of course is treated as internal an is currently not exported, though.

Whether we might want to streamline this is a matter of discussion, cc @wojciechczerniak

[izulin]

No, it means that as long as your function is not handled internally by the ArraySize class, it can only return scalars.

[MartinDawson]

ah okay. I'll wait for the release for it then instead of trying to hack it cause it's over my head.

Thanks!

[wojciechczerniak]

So this is blocked by #760? AFAIK it's not scheduled @izulin ?

[izulin]

Yes, I created an issue. Let us move the discussion to #760

[MartinDawson]

This discussion is now resolved by this PR: #766. I just manually exported ArraySize myself for now from hyperformula until it's fully done internally.

I just tried it with returning an array of numbers from the custom function and it's correctly working as it fills out adjacent cells.

This file helped me understand how to do it: https://github.com/handsontable/hyperformula/blob/4b491c1ba83051493939885eaef44eb0fae17c27/test/custom-functions.spec.ts

Thanks!