SpreadJS 14
Formula Reference / Formula Overview / Dynamic Arrays in a Formula
In This Topic
    Dynamic Arrays in a Formula
    In This Topic

    Dynamic array formulas are useful especially when users want to implement effective utilization of data cache in the spreadsheets. This is possible because these formulas allow random access with low memory footprints.

    When a cell contains a dynamic array formula, multiple values are returned because the elements of the array spill into the adjacent empty cells. Unlike generic arrays, dynamic arrays automatically resize when the data is inserted or removed from the source range.

    Spilled Array Formulas

    Dynamic array formulas that return more than one result and spill successfully to the nearby cells with values spanning to a cell range containing multiple rows and columns are known as spilled array formulas.

    Note: Spilled array formulas are not supported in Tables. However, while working with dynamic array formulas that spill to a number of rows and columns, the cell ranges used in the spreadsheets can be formatted explicitly to appear like tables.

    Built-in Dynamic Array Formulas

    SpreadJS supports the following built-in dynamic array formulas. For more information about them and their examples, refer to the following topics:

    1. UNIQUE
    2. SORT
    3. SORTBY
    6. FILTER

    Custom Dynamic Array Formulas

    Along with built-in dynamic array formulas, SpreadJS also supports custom dynamic array formulas where a user can create a custom function and use it into a dynamic array formula. 

    Using Code

    This example creates a custom dynamic array formula wherein a custom function "Selection" spills over a range of cells depending on the range of selected cells.

    Copy Code
        // initializing Spread
        var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
        // allowing DynamicArray
        spread.options.allowDynamicArray = true;
        // get the activesheet
        var sheet = spread.getActiveSheet();
        // adding a custom function "SelectionFunction"
        spread.addCustomFunction(new SelectionFunction());
        // set value
        sheet.setArray(6, 6, [
            [1, 2, 3, 4],
            [5, 6, 7, 8],
            [9, 10, 11, 12]
        // set columns width
        sheet.setColumnWidth(0, 120);
        sheet.setColumnWidth(1, 120);
        // add selection
        sheet.setSelection(6, 6, 3, 4);
        sheet.setValue(0, 0, "DynamicRange:");
        // add "Selection" formula
        sheet.setFormula(0, 1, "=Selection()");
        spread.bind(GC.Spread.Sheets.Events.SelectionChanging, function (sendar, args) {
    // Custom function
    function SelectionFunction() {
        this.name = "SELECTION";
        this.minArgs = 0;
        this.maxArgs = 0;
    SelectionFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
    SelectionFunction.prototype.evaluate = function (context) {
        var sheet = context.source.getSheet();
        var sel = sheet.getSelections()[0];
        var dataArr = sheet.getArray(sel.row, sel.col, sel.rowCount, sel.colCount);
        return new GC.Spread.CalcEngine.CalcArray(dataArr);
    SelectionFunction.prototype.isContextSensitive = function () {
        return true;