# Custom Functions

In formulas, you can create your own custom JavaScript functions (primitives) by calling the `kendo.spreadsheet.defineFunction(name, func)`.

The first argument (string) is the name for your function in formulas (case-insensitive), and the second one is a JavaScript function (the implementation).

## Synchronous Primitives

The following example demonstrates how to define a function that calculates the distance between two points.

``````kendo.spreadsheet.defineFunction("distance", function(x1, y1, x2, y2){
var dx = Math.abs(x1 - x2);
var dy = Math.abs(y1 - y2);
var dist = Math.sqrt(dx*dx + dy*dy);
return dist;
}).args([
[ "x1", "number" ],
[ "y1", "number" ],
[ "x2", "number" ],
[ "y2", "number" ]
]);
``````

If you include the above JavaScript code, you can then use `DISTANCE` in formulas. For example, to find the distance between coordinate points `(2,2)` and `(5,6)`, type in a cell `=DISTANCE(2, 2, 5, 6)`. Optionally, you can use the function in combined expressions such as `=DISTANCE(0, 0, 1, 1) + DISTANCE(2, 2, 5, 6)`.

In the above example, `defineFunction` returns an object that has an `args` method. You can use it to specify the expected types of arguments. If the function is called with mismatching argument types, the runtime of the Spreadsheet automatically returns an error and your implementation is not called. This spares the time for manually writing code that does argument type checking and provides a nice declarative syntax instead.

## Asynchronous Primitives

To retrieve currency information from a remote server, define a primitive to make this information available in formulas. To define an asynchronous function, call `argsAsync` instead of `args`.

``````kendo.spreadsheet.defineFunction("currency", function(callback, base, curr){
// A suggested fetchCurrency function.
// The way it is implemented is not relevant to the goal of the demonstrated scenario.
fetchCurrency(base, curr, function(value){
callback(value);
});
}).argsAsync([
[ "base", "string" ],
[ "curr", "string" ]
]);
``````

The `argsAsync` passes a callback as the first argument to your implementation function, which you need to call with the return value.

It is possible to use new approaches in formulas such as `=CURRENCY("EUR", "USD")` and `=A1 * CURRENCY("EUR", "USD")`. Note that the callback is invisible in formulas. The second formula shows that even though the implementation itself is asynchronous, it can be used in formulas in a synchronous way—that is, the result yielded by `CURRENCY` is multiplied by the value in A1.

## Argument Types

As can be seen in the examples above, both `args` and `argsAsync` expect a single array argument. It contains one definition for each argument. Each definition is in turn an array where the first element is the argument name that has to be a valid JavaScript identifier and the second element is a type specifier.

### Basic Type Specifiers

The Spreadsheet supports the following type specifiers.

BASIC SPECIFIER ACTION
`"number"` Requires a numeric argument.
`"number+"` Requires a number bigger than or equal to zero.
`"number++"` Requires a non-zero positive number.
`"integer"`/`"integer+"`/`"integer++"` Similar to `number`-s, but requires integer argument. Note that these may actually modify the argument value: if a number is specified and it has a decimal part, it will silently be truncated to integer, instead of returning an error. This is similar to Excel.
`"divisor"` Requires a non-zero number. Produces a `#DIV/0!` error if the argument is zero.
`"string"` Requires a string argument.
`"boolean"` Requires a boolean argument. Most times you may want to use `"logical"` though.
`"logical"` Requires a `logical` argument. That is, Booleans `true` or `false`, but `1` and `0` are also accepted. It gets converted to an actual Boolean.
`"date"` Requires a date argument. Internally, dates are stored as numbers (the number of days since December 31 1899), so this works the same as `"integer"`. It was added for consistency.
`"datetime"` This is like `"number"`, because the time part is represented as a fraction of a day.
`"anyvalue"` Accepts any value type.
`"matrix"` Accepts a matrix argument. This is either a range, for example, `A1:C3`, or a literal matrix (see the Matrices section below).
`"null"` Requires a null (missing) argument. The reason for this specifier will be clarified in the Optional Arguments section.

Some specifiers actually modify the value that your function receives. For example, you can implement a function that truncates the argument to integer.

``````defineFunction("truncate", function(value){
return value;
}).args([
[ "value", "integer" ]
]);
``````

If you call `=TRUNCATE(12.634)`, the result is `12`. You can also call `=TRUNCATE(TRUE)`, it returns `1`. All numeric types silently accept a Boolean, and convert `true` to `1` and `false` to `0`.

### Error Values

By default, if an argument is an error, your function is not called and that error is returned.

``````defineFunction("iserror", function(value){
}).args([
[ "value", "anyvalue" ]
]);
``````

With this implementation, when you type `=ISERROR(1/0)`, `#DIV/0!` instead of `true` is returned—the error is passed over and aborts the computation. To allow the passing of errors, append a `!` to the type.

``````...args([
[ "value", "anyvalue!" ]
]);
``````

The result is that `true` is returned.

### Referencing Type Specifiers

All above-mentioned type specifiers force references. FBecasues of this, `=TRUNCATE(A5)` also works. The function gets the value in the `A5` cell. If `A5` contains a formula, the runtime library verifies you get the current value—that is, `A5` is evaluated first. All of this goes under the hood and you need not worry about it.

Sometimes you might need to write functions that receive a reference instead of a resolved value. Such an example is the `ROW` function of Excel. In its basic form, it takes a cell reference and returns its row number, as demonstrated in the following example. The actual `ROW` function is more complicated.

``````defineFunction("row", function(cell){
// Add one because internally row indexes are zero-based.
return cell.row + 1;
}).args([
[ "reference", "cell" ]
]);
``````

If you now call `=ROW(A5)`, you get `5` as a result, regardless of the content in the `A5` cell—it might be empty or it is possible that this very formula sits in the `A5` cell and there must be no circular reference error in such a case.

The following table lists the related type specifiers:

TYPE SPECIFIER ACTION
`"ref"` Allows any reference argument and your implementation gets it as such.
`"area"` Allows a cell or a range argument (`CellRef` or `RangeRef` instance).
`"cell"` Allows a cell argument (`CellRef` instance).
`"anything"` Allows any argument type. The difference to `anyvalue` is that this one does not force references—that is, if a reference is passed, it remains a reference instead of being replaced by its value.

### Compound Type Specifiers

In addition to the basic type specifiers that are strings, you can also use the following forms of type specifications:

`[ "null", DEFAULT ]` Validates a missing argument and makes it take the given `DEFAULT` value. This can be used in conjunction with `"or"` to support optional arguments.
`[ "not", SPEC ]` Requires an argument which does not match the specification.
`[ "or", SPEC, SPEC, ... ]` Validates an argument that passes any of the specifications.
`[ "and", SPEC, SPEC, ... ]` Validates an argument that passes all the specifications.
`[ "values", VAL1, VAL2, ... ]` The argument must strictly equal one of the listed values.
`[ "[between]", MIN, MAX ]` Validates an argument between the given values inclusive. Note that it does not require numeric argument. The "between" value is an alias.
`[ "(between)", MIN, MAX ]` This is similar to "[between]" but is exclusive.
`[ "[between)", MIN, MAX ]` Requires an argument greater than or equal to `MIN`, and strictly less than `MAX`.
`[ "(between]", MIN, MAX ]` Requires an argument strictly greater than `MIN`, and less than or equal to `MAX`.
`[ "assert", COND ]` Inserts an arbitrary condition literally into the code (see the Assertions section below).
`[ "collect", SPEC ]` Collects all remaining arguments that pass the specification into a single array argument. This only makes sense at top level and cannot be nested in `"or"`, `"and"`, etc. Arguments not matching the `SPEC` are silently ignored, except errors. Each error aborts the calculation.
`[ "#collect", SPEC ]` This is similar to "collect", but ignores errors as well.

### Previous Arguments References

In certain clauses you might need to refer to values of previously type-checked arguments. For example, if you want to write a primitive that takes a minimum, a maximum, and a value that must be between them, and should return as a fraction the position of that value between min and max.

``````defineFunction("my.position", function(min, max, value){
return (value - min) / (max - min);
}).args([
[ "min", "number" ],
[ "max", "number" ],
[ "value", [ "and", "number",
[ "[between]", "\$min", "\$max" ] ] ]
]);
``````

Note the type specifier for `"value"`:

``````[ "and", "number",
[ "[between]", "\$min", "\$max" ] ]
``````

The code requires that the parameter is a number and that it has to be between `min` and `max`. To refer to a previous argument, prefix the identifier with a `\$` character. This approach works for arguments of `"between"` (and friends), `"assert"`, `"values"` and `"null"`.

### Arbitrary Assertions

The above function is not quite correct because it does not check that `max` is actually greater than `min`. To do that, use `"assert"`, as demonstrated in the following example.

``````defineFunction("my.position", function(min, max, value){
return (value - min) / (max - min);
}).args([
[ "min", "number" ],
[ "max", "number" ],
[ "value", [ "and", "number",
[ "[between]", "\$min", "\$max" ] ] ],
[ "?", [ "assert", "\$min < \$max", "N/A" ] ]
]);
``````

The `"assert"` type specification allows you to introduce an arbitrary condition into the JavaScript code of the type-checking function. An argument name of `"?"` does not actually introduce a new argument, but provides a place for such assertions. The third argument to `"assert"` is the error code that it should produce if the condition does not stand (and `#N/A!` is actually the default).

### Optional Arguments

As hinted above, you can use the `"null"` specifier to support optional arguments.

The following example demonstrates the actual definition of the `ROW` function.

``````defineFunction("row", function(ref){
if (!ref) {
return this.formula.row + 1;
}
if (ref instanceof CellRef) {
return ref.row + 1;
}
return this.asMatrix(ref).mapRow(function(row){
return row + ref.topLeft.row + 1;
});
}).args([
[ "ref", [ "or", "area", "null" ]]
]);
``````

The code requires that the argument can either be an area (a cell or a range) or `null` (that is, missing). By using the `"or"` combiner, you make it accept either of these. If the argument is missing, your function gets `null`. In such cases, it has to return the row of the current formula that you get by `this.formula.row`. For more details, refer to the section on context objects.

In most cases, “optional” means that the argument takes some default value if one is not provided. For example, the `LOG` function computes the logarithm of the argument to a base, but if the base is not specified, it defaults to 10.

The following example demonstrates this implementation.

``````defineFunction("log", function(num, base){
return Math.log(num) / Math.log(base);
}).args([
[ "*num", "number++" ],
[ "*base", [ "or", "number++", [ "null", 10 ] ] ],
[ "?", [ "assert", "\$base != 1", "DIV/0" ] ]
]);
``````

The type specification for `base` is: `[ "or", "number++", [ "null", 10 ] ]`. This says it should accept any number greater than zero, but if the argument is missing, defaults to 10. The implementation does not have to deal with the case that the argument is missing — it will get 10 instead. Note that it uses an assertion to make sure the `base` is not 1. If the `base` is 1, a `#DIV/0!` error is returned.

### Return Errors

To return an error code, return a `spreadsheet.CalcError` object.

``````defineFunction("tan", function(x){
// If x is sufficiently close to PI, "tan" will return
// infinity or some really big number.
// The example will error out instead.
if (Math.abs(x - Math.PI/2) < 1e-10) {
}
return Math.tan(x);
}).args([
[ "x", "number" ]
]);
``````

For convenience, you can also `throw` a `CalcError` object for synchronous primitives—that is, if you use `args` and not `argsAsync`.

It is possible to do the above through an assertion as well.

``````defineFunction("tan", function(x){
return Math.tan(x);
}).args([
[ "x", [ "and", "number",
[ "assert", "1e-10 < Math.abs(\$x - Math.PI/2)", "DIV/0" ] ] ]
]);
``````

### Variable Argument List

The type checking mechanism errors out when your primitive receives more arguments than specified. There are a few ways to receive all remaining arguments without errors.

#### The `"rest"` Type Specifier

The simplest way is to use the `"rest"` type specifier. In such cases, the last argument is an array that contains all remaining arguments, whatever types they might be.

The following example demonstrates how to use a function that joins arguments with a separator producing a string.

``````defineFunction("join", function(sep, list){
return list.join(sep);
}).args([
[ "sep", "string" ],
[ "list", "rest" ]
]);
``````

This allows for `=JOIN("-", 1, 2, 3)` which returns `1-2-3` and for `=JOIN(".")` which returns the empty string because the list will be empty.

#### The `"collect"` Clauses

The `"collect"` clauses collect all remaining arguments that match a certain type specifier, ignoring all others except for the errors. You can use them in functions like `SUM` that sums all numeric arguments, but does not care about empty or text arguments.

The following example demonstrates the definition of `SUM`.

``````defineFunction("sum", function(numbers){
return numbers.reduce(function(sum, num){
return sum + num;
}, 0);
}).args([
[ "numbers", [ "collect", "number" ] ]
]);
``````

The `"collect"` clause aborts when it encounters an error. To ignore errors as well, use the `"#collect"` specification. Note that `"collect"` and `"#collect"` only make sense when either is the first specifier—that is, they cannot be nested in `"or"`, `"and"`, and the like.

#### Other Type-Checked Arguments

There are functions that allow an arbitrary number of arguments of specific types. For example, the `SUMPRODUCT` function takes an arbitrary number of arrays, multiplies the corresponding numbers in these arrays, and then returns the sum of the products. In this case, you need at least two arrays.

The following example demonstrates the argument specification.

``````[
[ "a1", "matrix" ],
[ "+",
[ "a2", [ "and", "matrix",
[ "assert", "\$a2.width == \$a1.width" ],
[ "assert", "\$a2.height == \$a1.height" ] ] ] ]
]
``````

The `"+"` in the second definition means that one or more arguments are expected to follow and that the `a2` argument, defined there, can repeat. Notice how you can use assertions to make sure the matrices have the same shape as the first one (`a1`).

For another example, look at the `SUMIFS` function (see Excel documentation). It takes a `sum_range`, a `criteria_range`, and a `criteria`. These are the required arguments. Then, any number of `criteria_range` and `criteria` arguments can follow. In particular, criteria ranges must all have the same shape (width/height). Here is the argument definition for `SUMIFS`:

``````[
[ "range", "matrix" ],
[ "m1", "matrix" ],
[ "c1", "anyvalue" ],
[ [ "m2", [ "and", "matrix",
[ "assert", "\$m1.width == \$m2.width" ],
[ "assert", "\$m1.height == \$m2.height" ] ] ],
[ "c2", "anyvalue" ] ]
]
``````

The repeating part now is simply enclosed in an array, not preceded by `"+"`. This indicates to the system that any number might follow, including zero, while `"+"` requires at least one argument.

## Date and Time Representation

Dates are stored as the number of days since 1899-12-31 that is considered to be the first date. In Excel, the first day is 1900-01-01, but for historical reasons Excel assumes that 1900 is a leap year. For more information, refer to the article on the leap year bug. In Excel, day 60 yields an invalid date (1900-02-29), which means that date calculations involving dates before and after 1900-03-01 produce wrong results.

To be compatible with Excel and to avoid the unwilling implementation of this bug, the Spreadsheet uses 1899-12-31 as the base date. Dates that are greater than or equal to 1900-03-31 have the same numeric representation as in Excel, while dates before 1900-03-31 are smaller by 1.

Time is kept as a fraction of a day—that is, 0.5 means 12:00:00. For example, the date and time `Sep 27 1983 12:35:59` is numerically stored as `30586.524988425925`. To verify that in Excel, paste this number in a cell and then format it as a date or time.

Functions to pack or unpack dates are available in `spreadsheet.calc.runtime`.

``````var runtime = kendo.spreadsheet.calc.runtime;

// Unpacking
var date = runtime.unpackDate(28922.55);
console.log(date); // { year: 1979, month: 2, date: 8, day: 4 }

var time = runtime.unpackTime(28922.55);
console.log(time); // { hours: 13, minutes: 12, seconds: 0, milliseconds: 0 }

var date = runtime.serialToDate(28922.55); // produces JavaScript Date object
console.log(date.toISOString()); // 1979-03-08T13:12:00.000Z

// Packing
console.log(runtime.packDate(2015, 5, 25)); // year, month, date
console.log(runtime.packTime(13, 35, 0, 0)); // hours, minutes, seconds, ms
console.log(runtime.dateToSerial(new Date()))
``````

Note that the serial date representation does not carry any timezone information, so the functions involving `Date` objects (`serialToDate` and `dateToSerial`) use the local components and not UTC—as Excel does.

## References

As mentioned earlier, certain type specifiers allow you to get a reference in your function rather than the resolved value. Note that when you do so, you cannot rely on the values in those cells to be calculated. As a result, if your function might need the values as well, you have to compute them. Because the function which does this is asynchronous, your primitive has to be defined in an asynchronous style as well.

``````defineFunction("test", function(callback, x){
this.resolveCells([ x ], function(){
console.log("So we have a cell:");
console.log(x.sheet, x.row, x.col);
console.log("And its value is:");
console.log(this.getRefData(x));
callback("Cell value: " + this.getRefData(x));
});
}).argsAsync([
[ "x", "cell" ]
]);
``````

This function accepts a cell argument and you can only call it like `=test(B4)`. It calls `this.resolveCells` from the context object to verify that the cell value is calculated. Without this step and if the cell actually contains a formula, the value returned by `this.getRefData` could be outdated. Then it prints some information about that cell.

The following list explains the types of references that your primitive can receive:

• `spreadsheet.Ref`—A base class only. All references inherit from it, but no direct instance of this object should ever be created. The class is exported just to make it easier to check whether something is a reference: `x instanceof spreadsheet.Ref`.
• `spreadsheet.NULLREF`—An object (a singleton) and not a class. It represents the `NULL` reference, and could occur, for example, when you intersect two disjoint ranges, or when a formula depends on a cell that has been deleted. For example, when you put in some cell `=test(B5)` and then right-click on column `B` and delete it. To test when something is the `NULL` reference, just do `x === spreadsheet.NULLREF`.
• `spreadsheet.CellRef`—Represents a cell reference. Note that the references here follow the same programming language concept. They do not contain data. Instead they just point to where the data is. So a cell reference contains 3 essential properties:
• `sheet` — the name of the sheet that this cell points to (as a string)
• `row` — the row number, zero-based
• `col` — the column number, zero-based
• `spreadsheet.RangeRef`—A range reference. It contains `topLeft` and `bottomRight`, which are `CellRef` objects.
• `spreadsheet.UnionRef`—A union. It contains a `refs` property, which is an array of references (it can be empty). A `UnionRef` can be created by the union operator, which is the comma.

The following example demonstrates how to use a function that takes an arbitrary reference and returns its type of reference.

``````  defineFunction("refkind", function(x){
return "null";
}
return "cell";
}
return "range";
}
return "union";
}
return "unknown";
}).args([
[ "x", "ref" ]
]);
``````

The following example demonstrates how to use a function that takes an arbitrary reference and returns the total number of cells it covers.

``````  defineFunction("countcells", function(x){
var count = 0;
count++;
} else if (x instanceof spreadsheet.RangeRef) {
count += x.width() * x.height();
} else if (x instanceof spreadsheet.UnionRef) {
} else {
// unknown reference type.
throw new CalcError("REF");
}
}
return count;
}).args([
[ "x", "ref" ]
]);
``````

You can now say:

• `=COUNTCELLS(A1)` — returns 1.
• `=COUNTCELLS(A1:C3)` — returns 9.
• `=COUNTCELLS( (A1,A2,A1:C3) )` — returns 11. This is a union.
• `=COUNTCELLS( (A1:C3 B:B) )` — returns 3. This is an intersection between the `A1:C3` range and the `B` column.

Here is a function that returns the background color of some cell:

``````  defineFunction("backgroundof", function(cell){
var workbook = this.workbook();
var sheet = workbook.sheetByName(cell.sheet);
return sheet.range(cell).background();
}).args([
[ "cell", "cell" ]
]);
``````

It uses `this.workbook()` to retrieve the workbook, and then uses the Workbook/Sheet/Range APIs to fetch the background color of the given cell.

## Matrices

Matrices are defined by `spreadsheet.calc.runtime.Matrix`. Your primitive can request a `Matrix` object by using the `"matrix"` type specification. In this case, it can accept a cell reference, a range reference, or a literal array. You can type literal arrays in formulas like in Excel, e.g., `{ 1, 2; 3, 4 }` (rows separated by semicolons).

Matrices were primarily added to deal with the “array formulas” concept in Excel. A function can return multiple values, and those will be in a `Matrix` object.

The following example demonstrates how to use a function that doubles each number in a range and returns a matrix of the same shape.

``````defineFunction("doublematrix", function(m){
return m.map(function(value){
return value * 2;
});
}).args([
[ "m", "matrix" ]
]);
``````

To use this formula:

1. Select a range—for example `A1:B2`.
2. Press `F12` and type `=doublematrix(C3:D4)`.
3. Press `Ctrl`+`Shift`+`Enter` (same as in Excel). As a result, cells `A1:B2` get the doubles of the values from `C3:D4`.

The following table lists some of the methods and properties the `Matrix` objects provide.

METHOD OR PROPERTY DESCRIPTION
`width` and `height` These properties indicate the dimensions of this matrix.
`clone()` Returns a new matrix with the same data.
`get(row, col)` Returns the element at a given location.
`set(row, col, value)` Sets the element at a given location.
`each(func, includeEmpty)` Iterates through elements of the matrix, calling your `func` for each element (first columns, then rows) with 3 arguments: `value`, `row` and `column`. If `includeEmpty` is `true`, it will call your function for empty (`null`) elements as well. Otherwise, it only calls it where a value exists.
`map(func, includeEmpty)` This is similar to `each`, but produces a new matrix of the same shape as the original one with the values returned by your functions.
`transpose()` Returns the transposed matrix. The rows of the original matrix become columns of the transposed one.
`unit(n)` Returns the unit square matrix of size `n`.
`multiply(m)` Multiplies the current matrix by the given matrix, and returns a new matrix as the result.
`determinant()` Returns the determinant of this matrix. The matrix should contain only numbers and be square. Note that there are no checks for this.
`inverse()` Returns the inverse of this matrix. The matrix should contain only numbers and be square. Note that there are no checks for this. If the inverse does not exist—the determinant is zero—then it returns `null`.

## Context Object

Every time a formula is evaluated, a special `Context` object is created and each involved primitive function is invoked in the context of that object—that is, it is accessible as `this`.

The following table demonstrates some of the methods the `Context` object provides.

METHOD DESCRIPTION
`resolveCells(array, callback)` Verifies that all references in the given array are resolved before invoking your callback—that is, executes any formula. If this array turns out to include the cell where the current formula lives, it returns a `#CIRCULAR!` error. Elements that are not references are ignored.
`cellValues(array)` Returns as a flat array the values in any reference that exist in the given array. Elements that are not references are copied over.
`asMatrix(arg)` Converts the given argument to a matrix, if possible. It accepts a `RangeRef` object or a plain JavaScript non-empty array. Additionally, if a `Matrix` object is provided, it is returned as is.
`workbook()` Returns the `Workbook` object where the current formula is evaluated.
`getRefData(ref)` Returns the data—that is the value—in the given reference. If a `CellRef` is given, it returns a single value. For a `RangeRef` or `UnionRef`, it returns a flat array of values.

Additionally, there is a `formula` property, an object representing the current formula. Its details are internal, but you can rely on it having the `sheet` (sheet name as a string), `row` and `col` properties, the location of the current formula.

## Missing `args` or `argsAsync`

This section explains what happens if you do not invoke `args` or `argsAsync`. It is recommended that you do not use that form.

If `args` or `argsAsync` are not called, the primitive function receives exactly two arguments:

• A callback to be invoked with the result.
• An array that contains the arguments passed in the formula.

The following example demonstrates how to use a function that adds two things.

``````defineFunction("add", function(callback, args){
callback(args[0] + args[1]);
});
``````

Results:

• `=ADD(7, 8)``15`
• `=ADD()``NaN`
• `=ADD("foo")``fooundefined`
• `=ADD(A1, A2)``A1A2`

In other words, if you use this raw form, you are responsible for type-checking the arguments and your primitive is always expected to be asynchronous.