Is there any way to get functions/variables used by a formula.

[makarandp0]

Description

I am looking for a way to get all functions/variables referenced in a formula - Is it possible with the current API?

basically given a formula like this

=SUM(SUM(foo,1), SUM(foo, bar))

we want to know that ["foo", "bar"] are the variables referenced, and the functions referenced are ["SUM"]

[sequba]

Hi @makarandp0, thanks for reaching out to us.

You may take a look at method getCellPrecedents() which returns all cells contained in the formula, but with the current API, there is no way to get the functions and named expressions.

[makarandp0]

Thank you for your response @sequba.
Looks like getCellDependents does not work for us - our formulas only have named expressions, and I need way to get to the list of the named expressions used by formula.
Please consider this feature request.

[AMBudnik]

Hi @makarandp0

I have good news. We just released HyperFormula v2.7.0, where within this PR #1393 we added a new method called getNamedExpressionsFromFormula. I hope it will meet your expectations.

[sequba]

Hi, @makarandp0 we discussed your request with the team. Would you mind giving us more context about it?

• What type of application are you building with HyperFormula?
• What do you need this function for and how are you going to use it?
• Do you want it to work on incomplete (invalid) formulas, e.g. =SUM(SUM(foo,1), SUM(foo, , or just the valid ones?
• Any other details that will help us understand your use-case and the specification of the feature requested.

[AKjeld]

Hi @sequba,

I am in a similar situation. In my application I use HyperFormula to calculate values in quite big forms. I want to show my user the values of the inputs used in a formula. Would be a useful feature to be able to obtain referenced variables for a formula.
Is there anyway to achieve this?

Thanks.

[sequba]

Hi @AKjeld, Can you answer this question to help us understand your request?

Do you want it to work on incomplete (invalid) formulas, e.g. =SUM(SUM(foo,1), SUM(foo, , or just the valid ones?

[makarandp0]

Hello @sequba,

Sorry for being super late responding on this.
Our financial software allows customization by using excel formulas as a micro language. These formula's are authored and stored in our db - they reference some fixed set of named expressions, and are evaluated at runtime by providing those named expressions.

Our problem was how do we ensure that the formulas are valid when they are authored. That is they are not referencing some named expression or functions that are not available?

For example, we allow only fixed number of named expressions say: ["color", "size", "height"], and we want to ensure that we do not allow authoring formula like IF(weight<110, "small", "heavy") since weight is not one of the named expression available at runtime.

We found a workaround for this, but now ran into another problem. We also allow some subset of formula function. For example we allow formula FLOOR(size, 10), but we would like prevent somebody from entering formula like FLOOR(size) and mark it invalid since FLOOR requires two parameters, and the formula isn't passing them.

hope that gives some context. Please let me know if you need more information
Thanks,
Makarand

[sequba]

Thank you for your reply.

Currently, the only way to validate the number of parameters passed to a function is by executing the formula (e. g. by calling calculateFormula). If the number of parameters is wrong, it will return CellError(ErrorType.NA, 'Wrong number of arguments.').

If you need the static validation, it could be implemented based on the function metadata (e.g. FLOOR). The metadata includes information about the number of parameters, their types, whether or not they are optional, and other information.

[AMBudnik]

Hey all!

Here https://hyperformula.handsontable.com/api/classes/hyperformula.html#getnamedexpressionsfromformula we have a reference to the new method we've introduced in v2.7.0 called getNamedExpressionsFromFormula.

I hope you'll find it useful.