Custom functions

Expand the function library of your application by adding custom functions.

Add a simple custom function

As an example, let’s create a custom function GREET that accepts a person’s first name as a string argument and returns a personalized greeting.

1. Create a function plugin

Import FunctionPlugin, and extend it with a new class. For example:

import { FunctionPlugin } from 'hyperformula';

// let's call the function plugin `MyCustomPlugin`
export class MyCustomPlugin extends FunctionPlugin {}

2. Define your function’s ID, method, and metadata

In your function plugin, in the static implementedFunctions property, define an object that declares the functions provided by this plugin.

The name of that object becomes the ID by which translations, aliases, and other elements reference your function. Make the ID unique among all HyperFormula functions (built-in and custom).

In your function’s object, you can specify:

• A method property (required), which maps your function to the implementation method (we’ll define it later on),
• A parameters array that describes the arguments accepted by your function and validation options for each argument,
• Other custom function options.

import { FunctionPlugin, FunctionArgumentType } from 'hyperformula';

MyCustomPlugin.implementedFunctions = {
  // let's define the function's ID as `GREET`
  GREET: {
    method: 'greet',
    parameters: [{ argumentType: FunctionArgumentType.STRING }],
  },
};

Tip

To define multiple functions in a single function plugin, add them all to the implementedFunctions object.

MyCustomPlugin.implementedFunctions = {
  FUNCTION_A: {
    //...
  },
  FUNCTION_B: {
    //...
  },
};

3. Add your function’s names

In a separate object, define your function’s names in every language that you want to support.

Tip

Even if you support just a single language, you still need to define a translation for it.

export const MyCustomPluginTranslations = {
  enGB: {
    GREET: 'GREET',
  },
  enUS: {
    GREET: 'GREET',
  },
  // repeat for all languages used in your system
};

4. Implement your function’s logic

In your function plugin, add a method that implements your function’s calculations. Your method needs to:

• Take two optional arguments: ast and state.
• Return the results of your calculations.

Wrap your implementation in the built-in runFunction() method, which:

• Evaluates the arguments of your custom function.
• Validates the number of arguments against the parameters array.
• Coerces the argument values to types set in the parameters array.
• Handles optional arguments and default values according to options set in the parameters array.
• Validates the arguments of your custom function against the argument validation options.
• Duplicates the arguments according to the repeatLastArgs option.
• Handles the array arithmetic mode.
• Performs function vectorization.
• Performs argument broadcasting.

export class MyCustomPlugin extends FunctionPlugin {
  greet(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('GREET'),
      (firstName) => {
        return `👋 Hello, ${firstName}!`;
      }
    );
  }
}

5. Register your function plugin

Register your function plugin and its translations so that HyperFormula can recognize it. You need to do this before you create your HyperFormula instance.

Use the registerFunctionPlugin() method:

HyperFormula.registerFunctionPlugin(MyCustomPlugin, MyCustomPluginTranslations);

6. Use your custom function in a formula

Now, you’re ready to use your GREET function in a formula.

// build a HyperFormula instance where you can use your function directly
const hfInstance = HyperFormula.buildFromArray([['Anthony', '=GREET(A1)']]);

// read the value of cell B1
const result = hfInstance.getCellValue({ sheet: 0, col: 1, row: 0 });

// cell B1 should evaluate to 'Anthony'
console.log(result);

Full example

The complete implementation of this custom function is also included in the demo.

import { FunctionPlugin, FunctionArgumentType } from 'hyperformula';

export class MyCustomPlugin extends FunctionPlugin {
  greet(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('GREET'),
      (firstName) => {
        return `👋 Hello, ${firstName}!`;
      }
    );
  }
}

MyCustomPlugin.implementedFunctions = {
  GREET: {
    method: 'greet',
    parameters: [{ argumentType: FunctionArgumentType.STRING }],
  },
};

export const MyCustomPluginTranslations = {
  enGB: {
    GREET: 'GREET',
  },
  enUS: {
    GREET: 'GREET',
  },
};

HyperFormula.registerFunctionPlugin(MyCustomPlugin, MyCustomPluginTranslations);

Advanced custom function example

In a more advanced example, we’ll create a custom function DOUBLE_RANGE that takes a range of numbers and returns the range of the same size with all the numbers doubled.

Accept a range argument

To accept a range argument, declare it in the parameters array:

MyCustomPlugin.implementedFunctions = {
  DOUBLE_RANGE: {
    method: 'doubleRange',
    parameters: [{ argumentType: FunctionArgumentType.RANGE }],
  },
};

The range arguments are passed to the implementation method as instances of the SimpleRangeValue class:

export class MyCustomPlugin extends FunctionPlugin {
  doubleRange(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('DOUBLE_RANGE'),
      (range) => {
        const rangeData = range.data;
        // ...
      }
    );
  }
}

Return an array of data

A function can return multiple values in the form of an array. To do that, use SimpleRangeValue class:

export class MyCustomPlugin extends FunctionPlugin {
  doubleRange(ast, state) {
    return this.runFunction(
      ast.args,
      state,
      this.metadata('DOUBLE_RANGE'),
      (range) => {
        const resultArray = //...
        return SimpleRangeValue.onlyValues(resultArray);
      },
    );
  }
}

A function that returns an array will cause the VALUE! error unless you also declare a companion method for the array size. To do that, provide the sizeOfResultArrayMethod that calculates the size of the result array based on the function arguments and returns an instance of the ArraySize class.

Tip

When you use your custom function in a formula, sizeOfResultArrayMethod is triggered every time the formula changes, but not when the dependencies of the formula change. This can cause unexpected behavior if the size of the result array depends on the values in the referenced cells.

export class MyCustomPlugin extends FunctionPlugin {
  doubleRangeResultArraySize(ast, state) {
    const arg = ast?.args?.[0];

    if (arg?.start == null || arg?.end == null) {
      return ArraySize.scalar();
    }

    const width = arg.end.col - arg.start.col + 1;
    const height = arg.end.row - arg.start.row + 1;

    return new ArraySize(width, height);
  }
}

MyCustomPlugin.implementedFunctions = {
  DOUBLE_RANGE: {
    method: 'doubleRange',
    sizeOfResultArrayMethod: 'doubleRangeResultArraySize',
    parameters: [{ argumentType: FunctionArgumentType.RANGE }],
  },
};

Validate the arguments and return an error

To handle invalid inputs, the custom function should return an instance of the CellError class with the relevant error type. Errors are localized according to your language settings.

if (rangeData.some((row) => row.some((val) => typeof rawValue !== 'number'))) {
  return new CellError(
    'VALUE',
    'Function DOUBLE_RANGE operates only on numbers.'
  );
}

Tip

All HyperFormula error types support optional custom error messages. Put them to good use: let your users know what caused the error and how to avoid it in the future.

Test your function

To make sure your function works correctly, add unit tests. Use a JavaScript testing library of your choice.

it('works for a range of numbers', () => {
  HyperFormula.registerFunctionPlugin(
    MyCustomPlugin,
    MyCustomPluginTranslations
  );

  const engine = HyperFormula.buildFromArray(
    [[1, '=DOUBLE_RANGE(A1:A3)'], [2], [3]],
    { licenseKey: 'gpl-v3' }
  );

  expect(engine.getCellValue({ sheet: 0, row: 0, col: 1 })).toEqual(2);
  expect(engine.getCellValue({ sheet: 0, row: 1, col: 1 })).toEqual(4);
  expect(engine.getCellValue({ sheet: 0, row: 2, col: 1 })).toEqual(6);
});

it('returns a VALUE error if the range argument contains a string', () => {
  HyperFormula.registerFunctionPlugin(
    MyCustomPlugin,
    MyCustomPluginTranslations
  );

  const engine = HyperFormula.buildFromArray(
    [[1, '=DOUBLE_RANGE(A1:A3)'], ['I should not be here'], [3]],
    { licenseKey: 'gpl-v3' }
  );

  expect(engine.getCellValueType({ sheet: 0, row: 0, col: 1 })).toEqual(
    'ERROR'
  );
  expect(engine.getCellValue({ sheet: 0, row: 0, col: 1 }).value).toEqual(
    '#VALUE!'
  );
});

Working demo

Explore the full working example on Stackblitz.

This demo contains the implementation of both the GREET and DOUBLE_RANGE custom functions.

Function options

You can set the following options for your function:

Option	Type	Description
method (required)	String	Name of the method that implements the custom function logic.
parameters	Array	Specification of the arguments accepted by the function and their validation options.
sizeOfResultArrayMethod	String	Name of the method that calculates the size of the result array. Not required for functions that never return an array.
returnNumberType	String	If the function returns a numeric value, this option indicates how to interpret the returned number. Possible values: NUMBER_RAW, NUMBER_DATE, NUMBER_TIME, NUMBER_DATETIME, NUMBER_CURRENCY, NUMBER_PERCENT. Default: NUMBER_RAW
repeatLastArgs	Number	For functions with a variable number of arguments: sets how many last arguments can be repeated indefinitely. Default: 0
expandRanges	Boolean	true: ranges in the function’s arguments are inlined to (possibly multiple) scalar arguments. Default: false
isVolatile	Boolean	true: the function is volatile. Default: false
isDependentOnSheetStructureChange	Boolean	true: the function gets recalculated with each sheet shape change (e.g., when adding/removing rows or columns). Default: false
doesNotNeedArgumentsToBeComputed	Boolean	true: the function treats reference or range arguments as arguments that don’t create dependency (other arguments are properly evaluated). Default: false
enableArrayArithmeticForArguments	Boolean	true: the function enables the array arithmetic mode in its arguments and nested expressions. Default: false
vectorizationForbidden	Boolean	true: the function will never get vectorized. Default: false
arraySizeMethod	String	Deprecated; Use sizeOfResultArrayMethod instead.
arrayFunction	Boolean	Deprecated; Use enableArrayArithmeticForArguments instead.

You can set the options in the static implementedFunctions property of your function plugin:

MyCustomPlugin.implementedFunctions = {
  MY_FUNCTION: {
    method: 'myFunctionMethod',
    parameters: [
      {
        // your argument validation options
      },
    ],
    sizeOfResultArrayMethod: 'myArraySizeMethod',
    returnNumberType: 'NUMBER_RAW',
    repeatLastArgs: 0,
    expandRanges: false,
    isVolatile: false,
    isDependentOnSheetStructureChange: false,
    doesNotNeedArgumentsToBeComputed: false,
    enableArrayArithmeticForArguments: false,
    vectorizationForbidden: false,
  },
};

Argument validation options

You can set the following argument validation options:

Option	Type	Description
argumentType (required)	FunctionArgumentType	Expected type of the function argument. See possible values.
defaultValue	InternalScalarValue or RawScalarValue	If set: if an argument is missing, its value defaults to defaultValue.
passSubtype	Boolean	true: arguments are passed with full type information (e.g., for numbers: Date or DateTime or Time or Currency or Percentage). Default: false
optionalArg	Boolean	true: if an argument is missing, and no defaultValue is set, the argument defaults to undefined (instead of throwing an error). Default: false Setting this option to true is the same as setting defaultValue to undefined.
minValue	Number	If set: numerical arguments need to be greater than or equal to minValue.
maxValue	Number	If set: numerical arguments need to be less than or equal to maxValue.
lessThan	Number	If set: numerical argument needs to be less than lessThan.
greaterThan	Number	If set: numerical argument needs to be greater than greaterThan.
emptyAsDefault	Boolean	true: an empty argument (e.g., =FUNC(1,,3)) is treated as missing and falls back to defaultValue. By default (false), empty arguments are coerced to the zero-value for their type (0, FALSE, or ""). Requires defaultValue to be set.

In your function plugin, in the static implementedFunctions property, add an array called parameters:

MyCustomPlugin.implementedFunctions = {
  MY_FUNCTION: {
    method: 'myFunctionMethod',
    parameters: [
      {
        argumentType: FunctionArgumentType.STRING,
        defaultValue: 10,
        passSubtype: false,
        optionalArg: false,
        minValue: 5,
        maxValue: 15,
        lessThan: 15,
        greaterThan: 5,
      },
    ],
  },
};

Types of the function argument

Type	Description	Example
NUMBER	A general numeric value such as floating-point number, date/time value, currency value or percent value.	3, 3.14, $100, 1939/09/01, 4:45 AM
INTEGER	An integer.	42
COMPLEX	A text representing a complex value.	"-3+4i"
STRING	A text value.	"aaa"
BOOLEAN	A logical value.	=TRUE()
NOERROR	Any non-range and non-error value.	All of the above
SCALAR	Any non-range value.	All of the above
RANGE	Multiple values as a range of cells or an inline array.	A1:B100, {1, 2}
ANY	Any value.	All of the above

Handling missing arguments

Both the defaultValue and optionalArg options let you decide what happens when a user doesn’t pass enough valid arguments to your custom function.

Setting a defaultValue for an argument always makes that argument optional. But, the defaultValue option automatically replaces any missing arguments with defaultValue, so your custom function is unaware of the actual number of valid arguments passed.

If you don’t want to set any defaultValue (because, for example, your function’s behavior depends on the number of valid arguments passed), use the optionalArg setting instead.

Function name translations

You can add translations of your function’s name in multiple languages. Your end users use the translated names to call your function inside formulas.

In a separate object, define the translations of your custom functions’ names in every language you want to support. Function names are case-insensitive, as they are all normalized to uppercase.

Tip

Even if you support just a single language, you still need to define a translation for it.

export const MyCustomPluginTranslations = {
  enGB: {
    // formula in English: `=MY_FUNCTION()`
    MY_FUNCTION: 'MY_FUNCTION',
  },
  deDE: {
    // formula in German: `=MEINE_FUNKTION()`
    MY_FUNCTION: 'MEINE_FUNKTION',
  },
  // repeat for all languages used in your system
};

// register your function plugin and translations
HyperFormula.registerFunctionPlugin(MyCustomPlugin, MyCustomPluginTranslations);

Tip

Before using a translated function name, remember to register and set the language.

Function aliases

You can also assign multiple aliases to a single custom function.

In your function plugin, in the static aliases property, add aliases for your function:

MyCustomPlugin.aliases = {
  // `=MY_ALIAS()` will work the same as `=MY_FUNCTION()`
  MY_ALIAS: 'MY_FUNCTION',
};

Tip

For each alias of your function, define a translation, even if you want to support only one language.

MyCustomPlugin.translations = {
  enGB: {
    MY_FUNCTION: 'MY_FUNCTION',
    MY_ALIAS: 'MY_ALIAS',
  },
};