Passing an object into new instance of hyperformula that can be accessed from Custom Functions

[sturichardson]

What I am trying to do is... (within handsontable)

Create many different instances of Hyperformula.
this.hfInstance = HyperFormula.buildEmpty();

When I create these instances I want to somehow attach some data (that I want to be different for different instances) that will be accessible in the custom functions.

So far I have tried:
let plugin = this.hfInstance.getFunctionPlugin(‘HYPER’);
this.hfInstance.getConfig().ctrl = this;
plugin.ctrl = this;

In the custom function:
hyper(ast, state) {
let ctrl = Object.getPrototypeOf(this).constructor.ctrl;
console.log(ctrl.key);
return ‘Hyperformula’.length;
}

Unfortunately this will not work for me as there is only one instance of the custom formula so each instance of HyperFormula shares the same instance of the custom formula.

Is there someway to achieve what I want...? I thought I might be able to add in a new custom config property that would be accessible from custom functions but cannot see a way to achieve this?

[wojciechczerniak]

You can't modify the config. I have an idea but it's not tested.

---

 this.hfInstance.getConfig().ctrl = this;

getConfig will return an object copy. Don't set any data on it.

---

Object.getPrototypeOf(this).constructor.ctrl;

constructor is a reference to class so you're setting and getting a static property that is shared between instances.

---

this.hfInstance.getFunctionPlugin(‘HYPER’);

Will return plugin instance. You may add private values pool to the plugin class, Map will work fine, and methods to access it.

class HyperPlugin extends BasePlugin {
  #data = new Map<string, any>()

  setData(key: string, value: any): void {
    this.#data.set(key, value)
  }

  getData(key: string): any {
    return this.#data.get(key)
  }

   hyper(ast, state) {
      let ctrl = this.getData('ctrl')

      // ... rest of the function
   }
}

Usage:

let plugin = this.hfInstance.getFunctionPlugin(‘HYPER’);
this.hfInstance.setData('ctrl', 12);

I was wrong. See: #808 (reply in thread)

[sturichardson]

Thanks for your assistance but I can't get this to work...

class CountHF extends HyperFormula.FunctionPlugin {
    constructor(args) {
        super(args);
        this.data = new Map();
    }
    setData(key, value) {
      this.data.set(key, value);
    }
    getData(key) {
      return this.data.get(key);
    }
    hyper(ast, state) {
        let ctrl = this.getData('ctrl');
        return 'Hyperformula'.length;
    }
}

and when attempting to use it:

        this.hfInstance = HyperFormula.buildEmpty();
        let plugin = this.hfInstance.getFunctionPlugin('HYPER');
        plugin.setData('ctrl', this);

setData isn't defined when trying to use it. If I access the plugin.prototype then I can see the function.

Any other ideas or if you can see what I might be doing wrong?

[wojciechczerniak]

Oh, right. getFunctionPlugin does not return an instance 🤔 We don't have access to those. Sorry

[BrianHung]

@sequba Running into this issue as well: I have some external state that I would like to use per hyperformula instance in a custom function. Specifically, I'm trying to support column headers which are stored outside of hyperformula, but would like =COLH() to resolve to them. Would you have any suggestions? Thanks!

[sequba]

@BrianHung HyperFormula does not support storing custom data inside an instance object, but I suspect it might be possible with some "hacky" solution e.g. passing custom data as a config parameter that is intended for another purpose.

Can you give us more context about this use case? We might consider adding such a feature. Why the external state cannot be passed to a custom function as an argument? Do you need to use multiple instances of HyperFormula or a single instance with multiple sheets would be enough? I'd love to see some code snippets ;)

[BrianHung]

Can you give us more context about this use case? We might consider adding such a feature. Why the external state cannot be passed to a custom function as an argument?

@sequba Sorry for the late response! A good example of integrating hyperformula to work with external data sources is with implementing a REF function that takes ids and resolves them into cell references.

=REF([rowId]/[colId]) // should be equivalent to something like =A1

This function itself is useful when trying to make hyperformula multiplayer, since cell references are now synced by resolving them to ids first. That's how liveblocks implements moving rows and cols without relying on their calculation engine needing a moveRow or moveCol api.

https://twitter.com/liveblocks/status/1560613111316418562
https://liveblocks.io/examples/collaborative-spreadsheet-advanced/nextjs
https://github.com/liveblocks/liveblocks/blob/5d76f0d1491d2b84a19832b815bfd7528e6281ae/examples/nextjs-spreadsheet-advanced/src/spreadsheet/index.ts#L127

However, to convert something like rowId and colId into row and col indices, you need a reactive mapping somewhere. Changing row and col order is updating that mapping.

// row order 1, col order 2
let mapping = { '111b9258-aae3-4436-87b0-0b40dc674693': 1, '8dd3b5a1-7d0a-4ad8-95d6-7b483d3fcf8a': 2 }
// row order 5, col order 8
mapping = { '111b9258-aae3-4436-87b0-0b40dc674693': 5, '8dd3b5a1-7d0a-4ad8-95d6-7b483d3fcf8a': 8 }

Do you need to use multiple instances of HyperFormula or a single instance with multiple sheets would be enough?

I think a single instance suffices; haven't encountered an app needing different hyperformula instances on one page just yet. However, I think the hyperformula plugin needs to be reactive to changes in the external data source, so it would be useful to be able to hook into the data source using per-plugin listener.

[sequba]

I have some external state that I would like to use per hyperformula instance in a custom function. Specifically, I'm trying to support column headers which are stored outside of hyperformula.

I think a single instance suffices; haven't encountered an app needing different hyperformula instances on one page just yet

If you need only a single instance of HyperFormula, you may store the external state globally. It's not the most elegant solution, but it's going to work: https://codesandbox.io/s/lucid-thompson-v3upti?file=/src/MyCustomPlugin.js:48-107

I believe the same approach can be used to implement REF function.

[BrianHung]

Is there a way for a formula plugin to cause hyperformula to recalculate values which are dependent on that formula when the external state changes?

[sequba]

There is no automatic way of watching changes in the external state. You need to refresh HyperFormula manually every time you detect a change.

You can call rebuildAndRecalculate() to recalculate all formulas. A slightly more efficient solution would be to make your custom function volatile and cause recalculation by triggering one of the volatile actions.

[michaelbridge]

+1 here. It's very hacky to rely on a global singleton to store external state, since this precludes the possibility of ever instantiating more than one instance of hyperformula. Likewise, the suggestion made elsewhere to resolve custom functions before creating a a hyperformula instance is infeasible if there is any type of non-constant dependencies within the custom function parameters.

Most libraries solve this by virtue of allowing a flexible context object to be passed via options. For example:

const options = {
  context: {
    resolveData: (key) => {
       // Custom logic to resolve data for the particular key
    }
  },
}
const hfInstance = HyperFormula.buildFromArray(data, options)

Then, in the custom function, we can make use as follows:

funcMethod(ast, state) {
  return this.runFunction(
    ast.args,
    state,
    this.metadata('...'),
    (key) => {
      return this.config.resolveData(key)
    },
  )
}

I've tried various approaches to modify options, but any additional properties specified in options are not passed through to the config object available in the custom function method.

All that's required to solve this issue is to allow an untyped context option to be passed through.

[michaelbridge]

I'm willing to open the ~few line PR if it can be merged.

[sequba]

All that's required to solve this issue is to allow an untyped context option to be passed through.

@michaelbridge good idea. We discussed it with the team and we think, that HyperFormula will benefit from the proposed untyped context configuration parameter. I created a task for this feature and we will implement it in one of the upcoming releases.

I'm willing to open the ~few line PR if it can be merged.

Feel free to open such a PR. It will certainly help us release this feature earlier. I'll be happy to review it and merge once it's finished.

[AMBudnik]

@michaelbridge I'm happy to announce that @sequba 's PR was just released under Hyperformula 2.7.0.

[michaelbridge]

Great, I have completed the contributor agreement. I'll open a PR once I can push to the repo.

[AMBudnik]

Thank you @michaelbridge we got your CLA signed.

[AMBudnik]

@michaelbridge @sturichardson @BrianHung

Do we have anything else to cover (you all have different projects and needs so I would like to confirm that) or with the merge of #1398 and #1396 in v2.7.0 we can close this issue as solved?