Dynamic Arrays in a Formula

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 of 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.

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

4. RANDARRAY

5. SEQUENCE

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 in a dynamic array formula.

The following code sample creates a custom dynamic array formula wherein a custom function "Selection" spills over a range of cells depending on the range of selected cells.

    // 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());
    spread.suspendPaint();
    // 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.resumePaint();
    spread.bind(GC.Spread.Sheets.Events.SelectionChanging, function (sendar, args) {
        args.sheet.recalcAll();
    });
});

// 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;
}