In custom formulas, how can I get CELL_RANGE, COLUMN_RANGE, etc values?

[nathipg]

Hello!

I’m starting to create custom formulas and I’m kind of confused with how to get the values.

By now I have this simple code:

min(ast, state) {
  console.log(ast, state);
}

Being called like this:

=MIN(1,2,A1:A6, A:A)

Which outputs that:

My question is: How do I get the value of those ranges? I looked at HyperFormula documentation, but there’s only a sample that doesn’t use the incoming parameters

[MartinDawson]

I also have a similar problem which I think may be related to the same as @nathipg issue above. Although I'm not sure it's actually a supported feature of custom functions yet. As far as I can tell there is no way to get these values from non simple args?

I.e our formula looks like this:

=FIN("revenue", =FIN("date", -1))

Produces this:

I would expect the inner formulas to be executed first by hyperformula and passed as the value in through the args param but that doesn't seem to be the case.

[wojciechczerniak]

All our plugins and the docs go through runFunction, you can check this function body how arguments are coerced:

hyperformula/src/interpreter/plugin/FunctionPlugin.ts

Line 352 in 413e199

protected runFunction = (

or use it to do the coercion for you that will be based on function meta your provide

[MartinDawson]

I see, thanks. It's not obvious in the docs that this function can do that.

https://handsontable.github.io/hyperformula/guide/custom-functions.html#implementing-your-custom-function

The example has this:

// wrap your custom function in `runFunction()`
  public hyper(ast, state) {
    return this.runFunction(() => 'Hyperformula'.length)
  } 

I think would be good to cover the above common scenario's as well. It just says it covers validation at the moment but not that you can actually coerce arguments.

[wojciechczerniak]

True. Current tutorial has a simple, introductory example. We should think about more advanced guide. CC @kirszenbaum

[nathipg]

All our plugins and the docs go through runFunction, you can check this function body how arguments are coerced:

hyperformula/src/interpreter/plugin/FunctionPlugin.ts

Line 352 in 413e199

protected runFunction = (

or use it to do the coercion for you that will be based on function meta your provide

Thanks for reply!
I have a question, how is this done in 0.6.2?
I tried to make a sample but it gives an error

Sample 1 (With one argument):
https://codesandbox.io/embed/priceless-water-z0r3l?fontsize=14&hidenavigation=1&theme=dark

Sample 2 (With three arguments as I saw in source code):
https://codesandbox.io/s/cool-dust-sx8bz?file=/src/countHFFunctionPlugin.js

In 0.6.2 docs, I didn't find an example

[wojciechczerniak]

Well, you would have to switch to 0.6.2 tag and go from there:

hyperformula/src/interpreter/plugin/FunctionPlugin.ts

Lines 270 to 277 in 43071aa

	protected runFunction = (
	args: Ast[],
	formulaAddress: SimpleCellAddress,
	functionDefinition: FunctionArguments,
	fn: (...arg: any) => InternalScalarValue
	) => {
	return this.runFunctionTemplate(args, formulaAddress, functionDefinition, fn)
	}

As you can see the logic was in runFunctionTemplate function back then. A lot has changed.

[nathipg]

Thank you for helping me! I didn't mention here, but I managed to parse the arguments after some discussions with @taknb2nch

[izulin]

One thing: function is responsible for calling Interpreter on its arguments. So e.g. this.evaluateAst(ast, state) for ast iterating over its arguments ast should be called.

[nathipg]

I'm in simlar situation.
I set =HYPER(1, 2, A4:B5, A:A) and tried the code below.

public hyper(ast, formulaAddress) {
  ast.args.forEach(e => {
    let v = this.evaluateAst(e, formulaAddress);

    if (v['valuesFromTopLeftCorner']) {
      let vs = v['valuesFromTopLeftCorner']();

      vs.forEach(e1 => {
        console.info('RANGE', e1);
      });
    } else {
      console.info('NUMBER', v);
    }
  });

  return 'Hyperformula'.length;.
}

I think I've got the value I expected (except Symbol(Empty value)), but I'm not sure if it's the right way to go.

Thank you very much!
Not sure if you had this issue, but If you need to check if cell is empty, you can import EmptyValue from 'hyperformula/es/interpreter/InterpreterValue', not sure if is the best way, but I'm doing it

[taknb2nch]

Thank you for your suggestion.

I changed to import to hyperformula/es/... and tried to implement it like existing plugin (I read MedianPlugin).

import { FunctionPlugin, ErrorType } from 'hyperformula';
import { RawScalarValue } from 'hyperformula/typings/interpreter/InterpreterValue'
import { CellError } from 'hyperformula/es/Cell'
import { ErrorMessage } from 'hyperformula/es/error-message'
import { ArgumentTypes } from 'hyperformula/es/interpreter/plugin/FunctionPlugin';

export class CustomFunctions extends FunctionPlugin {
  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
      expandRanges: true,
      parameters: [
        {argumentType: ArgumentTypes.ANY},
      ],
      repeatLastArgs: 1,
    }
  };

  public hyper(ast, formulaAddress) {
    return this.runFunction(ast.args, formulaAddress, this.metadata('HYPER'), (...args: RawScalarValue[]) => {
      const values = this.interpreter.arithmeticHelper.coerceNumbersExactRanges(args);

      if(values instanceof CellError) {
        return values;
      }

      const vs = values as number[];

      if (vs.length === 0) {
        return new CellError(ErrorType.NUM, ErrorMessage.OneValue)
      }

      console.info(vs);
      
      return 'Hyperformula'.length;
    });
  }
}

It seems to be working fine.

I'm not very familiar with TypeScript.

import { CellError } from 'hyperformula/typings/Cell';

get a build error, but

import { CellError } from 'hyperformula/es/Cell';

dose not.

I don't know why.
Please let me know.

[nathipg]

I gave a look at the files, but I couldn't find a reason to not work properly. I'm new with typescript either, so I might be missing something.
Not sure why, but my VSCode does not autocomplete to "typings", only to "es", thats why I've imported the other file right away.

[wojciechczerniak]

CellError is exported as impot { CellError } from 'hyperformula' and Typescript should choose /es dir automatically based on package.json. Also, types and autocomplete will use definition file that is generated for those named exports.

Full list you can see here:

hyperformula/src/index.ts

Lines 113 to 173 in 413e199

	export {
	AlwaysDense,
	AlwaysSparse,
	DenseSparseChooseBasedOnThreshold,
	CellValue,
	NoErrorCellValue,
	ConfigParams,
	ExportedChange,
	RawCellContent,
	FormatInfo,
	Sheet,
	Sheets,
	SheetDimensions,
	SimpleCellAddress,
	SimpleCellRange,
	ColumnRowIndex,
	RawTranslationPackage,
	FunctionPluginDefinition,
	FunctionArgument,
	NamedExpression,
	NamedExpressionOptions,
	HyperFormula,
	CellType,
	CellValueType,
	CellValueDetailedType,
	ErrorType,
	ExportedCellChange,
	ExportedNamedExpressionChange,
	DetailedCellError,
	CellError,
	ConfigValueTooBigError,
	ConfigValueTooSmallError,
	EvaluationSuspendedError,
	ExpectedOneOfValuesError,
	ExpectedValueOfTypeError,
	FunctionPlugin,
	FunctionPluginValidationError,
	InvalidAddressError,
	InvalidArgumentsError,
	LanguageAlreadyRegisteredError,
	LanguageNotRegisteredError,
	MissingTranslationError,
	NamedExpressionDoesNotExistError,
	NamedExpressionNameIsAlreadyTakenError,
	NamedExpressionNameIsInvalidError,
	NoOperationToRedoError,
	NoOperationToUndoError,
	NoRelativeAddressesAllowedError,
	NoSheetWithIdError,
	NoSheetWithNameError,
	NotAFormulaError,
	NothingToPasteError,
	ProtectedFunctionTranslationError,
	SimpleRangeValue,
	SheetNameAlreadyTakenError,
	SheetSizeLimitExceededError,
	SourceLocationHasArrayError,
	TargetLocationHasArrayError,
	UnableToParseError,
	SerializedNamedExpression,
	}

If something is missing pls let us know

[nathipg]

Thank you! I'll update my imports

[sequba]

Hi @nathipg, @taknb2nch

I'm more than happy to announce that we just released a new custom functions guide with an advanced example. You should check it out ;)

[taknb2nch]

Thank you @sequba.
I'll check it out.