[Bug]: Consistent Range Handling and Formula Text Access in Custom Function Arguments

[Esmail-Rahmani]

Description

I'm implementing a custom function(eg. AGGREGATE) in a HyperFormula plugin that needs both the data and the formula text of each argument range to determine if certain functions should be ignored. However, I’m encountering two issues:

1. Range Undefined for Single Cells: When a single cell is passed as an argument, the range property is undefined. For multi-cell ranges, range provides the expected values. For consistency, it would be helpful if range was defined for single cells as well.

2. Request for Formula Text: In addition to the data, I need access to the formula text used in each range. Currently, I have no straightforward way to access the formula text directly in the custom function. This would allow me to handle certain behaviors based on the specific formula used.

Here is the code I’m using:

export class MyCustomPlugin extends FunctionPlugin {
  aggregate(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('AGGREGATE'),
      (args) => {
        console.log(args);
      }
    );
  }
}

Expected Behavior

• The range property should not be undefined for single-cell arguments.
• Ideally, HyperFormula could provide formula text alongside the data for each argument range, allowing custom functions to process ranges based on the formula used.

Video or screenshots

Demo

https://codesandbox.io/p/devbox/exciting-cloud-cd9j7s

HyperFormula version

2.7.1

Your framework

Vue 3

Your environment

Chrome 90 Macos 15.0.1

[sequba]

Hi @Esmail-Rahmani, thank you for reaching out!

https://codesandbox.io/p/devbox/exciting-cloud-cd9j7s

Unfortunately, I'm unable to access the demo you provided. Please make sure the link is valid. Consider using https://stackblitz.com/, as recently I've found it working more reliably.

1. Range Undefined for Single Cells

I agree; it would make more sense. Please provide a working demo and a code example of the expected behavior. We'll consider implementing this feature.

2. Request for Formula Text

Custom functions can access many helpers yet to be described in our documentation. To read the formula in the cell provided as an argument, you can use Serialization.getCellFormula or Serialization.getCellSerialized method. E.g.:

export class MyCustomPlugin extends FunctionPlugin {
  aggregate(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('AGGREGATE'),
      (args) => {
        ast.args.forEach((arg) => {
          const relativeAddr = arg.reference;
          const absoluteAddr = relativeAddr.toSimpleCellAddress(state.formulaAddress);
          const cellFormula = this.serialization.getCellSerialized(absoluteAddr);
          console.log(cellFormula);
        });
      }
    );
  }
}

[JKhaledJ]

Hi dear @sequba, Thanks for your hint. I have the same issue and I used your code. Here is the error that I have:

As you can see, no argument for the args has the reference property, therefore, the relativeAddr is undefined. I would really appreciate to have your comment.

NOTE: Here is the format of the aggregate function: AGGREGATE(function_num, options, ref1, [ref2], …)

[sequba]

@JKhaledJ, please provide a working demo on https://stackblitz.com/. Then I could analyze the issue.