Is there a way to access hot instance inside Hyperformula custom function?

Tags: #<Tag:0x00007f8b1d5e7108>
#1

Hello!

I have my implementation of custom functions using HyperFormula and inside my function which gets the arguments I need to check how many rows/cols there are in my hot instance.

There is a hot variable inside there, but it seems to point to another hot instance, because it is always destroyed, while my hot instance isn’t.

Is there a way for me to pass my hot instance there or access in anyway?

#2

Hi @thiagarajan

Would it meet your requirements to use a variable in your custom formula (for example as a namedExpression) to a countRows() and countCols() methods’ results?

#3

I think this would fit. I’ve create a new named expression following the docs, but now I have a doubt, how can I access my named expression inside my custom function class? Is there a easy way to get my hyperformula instance?

#4

Question: do you plan to use the reference to get any non-data-related information? As in most of the cases we use namedExpressions to work on the data (example https://jsfiddle.net/jd62ykcv/)

#5

Ok, so it is not what I want =(

I intend to set a max row number and max column number when solving a formula, like this:

image
image.png927×244 39.9 KB

Is it possible?

#6

I forgot to mention before, but I’m using HyperFormula 0.6.2 and Handsontable 9.0.1

#7

To confirm, let’s say you have 10 rows so your formulas should look like =SUM(A1: maximum) where maximum is A + hot.countRows()? Or do I understand that wrong?

#8

I think is simpler than that, would be like this:

User types this formula =SUM(A1:A999999999), but my sheet only have 10 row, so I would limit this value to 10 with that code I showed before

#9

That is an interesting topic you have mentioned @thiagarajan
Please let me consult my colleague on that subject and come back to you after the weekend.

#10

Sure, thank you!

#11

Sorry for keeping you waiting @thiagarajan
I did not get my colleague’s reply yet but I will remind him about the case today.

#12

Thank you for letting me know, I’ll be waiting

#13

Thank you @thiagarajan for waiting.

In general, it is a bad idea to try accessing Handsontable instance from HyperFormula. My reasons are:

  1. The HyperFormula instance is created before the Handsontable instance
  2. In our setup, HyperFormula is employed by Handsontable. Handsontable keeps an instance of HyperFormula. It would be bad, circular architecture to try accessing the “parent” instance of Handsontable from your instance of HyperFormula.

The naive (and bad) solution would be to access the data object that you passed to Handsontable by reference to count your rows: https://jsfiddle.net/warpech/fh25rvg8/

HyperFormula, being a calculation engine, actually keeps the whole representation of your data. Meaning: It already knows how many rows you have!

Here’s a good solution - example of a custom function volatile function with 0 parameters that returns the number of rows:

export class TmpPlugin extends FunctionPlugin implements FunctionPluginTypecheck<TmpPlugin> {
  public static implementedFunctions = {
    'SHEET_HEIGHT': {
      method: 'sheetheight',
      parameters: [
      ],
      isVolatile: true,
    },
  }

  public sheetheight(ast: ProcedureAst, state: InterpreterState): InterpreterValue {
    return this.runFunction(ast.args, state, this.metadata('SHEET_HEIGHT'),
      () => this.dependencyGraph.getSheetHeight(state.formulaAddress.sheet)
    )
  }
}

Here’s the same presented using the Handsontable formula plugin: https://jsfiddle.net/warpech/2t5ewodx/5/

The best news for you is that you don’t really have to care about huge ranges as =SUM(A1:A999999999) or even infinity ranges when you supply just a column range (=SUM(A:A)), because they are automatically normalized by the engine to the size of your data graph.

You would need to dig into our implementation of this function to see how that works: https://github.com/handsontable/hyperformula/blob/cca0887f21c10e56b723d1a5efb98780cc1cd89e/src/interpreter/plugin/NumericAggregationPlugin.ts#L525-L526

Further reading:

1 Like
#14

Thank you so much for the answer, I’m going to implement in my project :smiley:
I’m aware of the A:A feature, but some users just keep typing large numbers, which are being bad for my sheet performance, that’s way I need this extra check.

Also, thank you @aleksandra_budnik for the whole support.

1 Like
#15

I’m always glad to help. Let me know if you would need anything else :slight_smile:

1 Like
#16