# Custom functions

HyperFormula enables you to create custom functions you may want to use in your application.

This guide explains step-by-step how to create a custom function that returns the number of letters in the word 'HyperFormula'. It will be invoked by typing "=HYPER()" or "=HAJPER()" (localized to Polish).

HyperFormula doesn't enforce a naming convention of the function. However, all names will be normalized to the upper-case, so they are not case-sensitive.

# Custom class definition

First, you need to import FunctionPlugin and extend it with your own class. Here is how you can do that:

// import FunctionPlugin
import { FunctionPlugin } from 'hyperformula';

// start creating a class
export class CountHF extends FunctionPlugin {

}

# implementedFunction property

Your newly created class should have a static implementedFunctions property that defines functions this plugin contains. This will keep a set of function names that call corresponding methods.

The keys are canonical function IDs which are also used to find corresponding translations in translation packages. Inside of them, there is also an object which contains the corresponding method.

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  // define functions inside this plugin
  public static implementedFunctions = {
    'HYPER': {
      // this method's functionality will be defined below
      method: 'hyper',
    }
  }
}

# Optional parameters

Using optional parameters, you can configure your function to:

Use the array arithmetic mode
Treat reference or range arguments as arguments that don't create dependency
Inline range arguments to scalar arguments
Get recalculated with each sheet shape change
Be a volatile function
Repeat indefinitely a specified number of last arguments
Never get vectorized

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
      // set your optional parameters
      arrayFunction: false,
      doesNotNeedArgumentsToBeComputed: false,
      expandRanges: false,
      isDependentOnSheetStructureChange: false,
      isVolatile: true,
      repeatLastArgs: 4,
    }
  }
}

You can set the following optional parameters:

Option	Type	Description
`arrayFunction`	Boolean	If set to `true`, the function enables the array arithmetic mode in its arguments and nested expressions.
`doesNotNeedArgumentsToBeComputed`	Boolean	If set to `true`, the function treats reference or range arguments as arguments that don't create dependency. Other arguments are properly evaluated.
`expandRanges`	Boolean	If set to `true`, ranges in the function's arguments are inlined to (possibly multiple) scalar arguments.
`isDependentOnSheetStructureChange`	Boolean	If set to `true`, the function gets recalculated with each sheet shape change (e.g. when adding/removing rows or columns).
`isVolatile`	Boolean	If set to `true`, the function is volatile.
`repeatLastArgs`	Number	For functions with a variable number of arguments: sets how many last arguments can be repeated indefinitely.

# Argument validation options

In an optional parameters object, you can set rules for your function's argument validation.

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
      // set your argument validation options
      parameters: {
        passSubtype: false,
        defaultValue: 10,
        optionalArg: false,
        minValue: 5,
        maxValue: 15,
        lessThan: 15,
        greaterThan: 5
      },
    }
  }
}

You can set the following argument validation options:

Parameter	Type	Description
`passSubtype`	Boolean	If set to `true`, arguments are passed with full type information. (e.g. for numbers: `Date` or `DateTime` or `Time` or `Currency` or `Percentage`)
`defaultValue`	`InternalScalarValue` \| `RawScalarValue`	If set to any value: if an argument is missing, its value defaults to `defaultValue`.
`optionalArg`	Boolean	If set to `true`: if an argument is missing, and no `defaultValue` is set, the argument defaults to `undefined` (instead of throwing an error). 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 need to be less than `lessThan`.
`greaterThan`	Number	If set, numerical argument need to be greater than `greaterThan`.

# Handling missing arguments

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 not aware 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), you can use the optionalArg setting.

# Aliases

Aliases are available since the v0.4.0 version.

If you want to include aliases (multiple names to a single implemented function) inside the plugin, you can do this with the static aliases property.

The property is keyed with aliases IDs, and with values being aliased functions IDs.

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
    }
  }
  
  public static aliases = {
    'HYPER.ALIAS': 'HYPER'
    // HYPER.ALIAS is now an alias to HYPER
  }
}

# Translations

There are two ways of adding a translation of the custom function.

In the first one, you can define translations in your function plugin as a static.

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
    }
  }
  
  public static aliases = {
    'HYPER.ALIAS': 'HYPER'
  }
  
  // add your translations
  public static translations = {
    'enGB': {
      'HYPER': 'HYPER'
    },
    'plPL': {
      'HYPER': 'HAJPER'
    }
  }
}

In the second one, you can keep your translation in any file you want as a constant and import it upon registering the plugin (or with a whole translation package).

// inside your translations file
export const myTranslations = {
  'enGB': {
    'HYPER': 'HYPER'
  },
  'plPL': {
    'HYPER': 'HAJPER'
  }
}

# Implementing your custom function

For the simplicity of a basic example, you will not pass any arguments. However, this method imposes a particular structure to be used; there are two optional arguments, ast and state, and the function must return the results of the calculations.

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
    }
  }
  
  public static aliases = {
    'HYPER.ALIAS': 'HYPER'
  }
  
  public static translations = {
    'enGB': {
      'HYPER': 'HYPER'
    },
    'plPL': {
      'HYPER': 'HAJPER'
    }
  }
  
  // implement your custom function
  // arguments here are displayed just to show the structure
  public hyper(ast, state) {
    return 'Hyperformula'.length;
  }
}

# `runFunction()`

Wrap your custom function in the built-in runFunction() method.

The runFunction() method wraps your function's calculations and:

Validates the optional parameter settings
Validates the arguments against your argument validation options
Checks if values returned by your function are in the right format

import { FunctionPlugin } from 'hyperformula';

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
    }
  }
  
  public static aliases = {
    'HYPER.ALIAS': 'HYPER'
  }
  
  public static translations = {
    'enGB': {
      'HYPER': 'HYPER'
    },
    'plPL': {
      'HYPER': 'HAJPER'
    }
  }
  
  // wrap your custom function in `runFunction()`
  public hyper(ast, state) {
    return this.runFunction(ast, state, this.metadata('HYPER'),
    () => 'Hyperformula'.length)
  }
}

# Returning errors

If you want your custom function to return an error, check the API reference for the HyperFormula error types.

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.

For example, if you want to return a #DIV/0! error with your custom error message, use the CellError class, and the DIV_BY_ZERO error type:

// import `CellError` and `ErrorType`
import { FunctionPlugin, CellError, ErrorType } from "hyperformula";

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
    }
  }
  
  public static aliases = {
    'HYPER.ALIAS': 'HYPER'
  }
  
  public static translations = {
    'enGB': {
      'HYPER': 'HYPER'
    },
    'plPL': {
      'HYPER': 'HAJPER'
    }
  }

public hyper({ args }) {
  if (!args.length) {
    // create a `CellError` instance with an `ErrorType` of `DIV_BY_ZERO`
    // with your custom error message (optional)
    return new CellError(ErrorType.DIV_BY_ZERO, 'Sorry, cannot divide by zero!');
  }
  
  return this.runFunction(() => 'Hyperformula'.length)
}

The error displays as #DIV/0, and gets properly translated.

# Error localization

Errors returned by methods such as getCellValue are wrapped in the DetailedCellError type.

DetailedCellError localizes the error based on your internationalization settings.

# A complete example of the class definition

To sum up, here is a complete example of a custom CountHF class:

import { FunctionPlugin, CellError, ErrorType } from "hyperformula";

export class CountHF extends FunctionPlugin {

  public static implementedFunctions = {
    'HYPER': {
      method: 'hyper',
    }
  }
  
  public static aliases = {
    'HYPER.ALIAS': 'HYPER'
  }
  
  public static translations = {
    'enGB': {
      'HYPER': 'HYPER'
    },
    'plPL': {
      'HYPER': 'HAJPER'
    }
  }
  
  public hyper({ args }) {
    if (!args.length) {
      return new CellError(ErrorType.DIV_BY_ZERO, 'Sorry, cannot divide by zero!');
    }
    
    return this.runFunction(() => 'Hyperformula'.length)
  }
}

# Registering a custom function

Before you can use the newly created function, you need to register it by using registerFunctionPlugin like so:

HyperFormula.registerFunctionPlugin(CountHF, CountHF.translations);

# Using a custom function

This is a short snippet that sums up how to use the custom function along with translations.

import Hyperformula, { plPL } from 'hyperformula';
import { CountHF } from './file_with_your_custom_function';

// register the language
HyperFormula.registerLanguage('plPL', plPL);

// register your custom plugin and the translation
HyperFormula.registerFunctionPlugin(CountHF, CountHF.translations);

// build HF instance where you can use the function directly
const hfInstance = HyperFormula.buildFromArray([['=HAJPER()']]);

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

// open the browser's console to see the results
console.log(A1Value);

# Demo

← Testing Performance →