Create Custom Formulas

If you have functions that you use on a regular basis that are not in the built-in functions or if you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions.

These custom functions can return either specified values or arrays. This is explained in the sections below:

Return Single Value in Custom Function

The following code sample creates a custom function that returns a value.

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var activeSheet = spread.getActiveSheet();
// Add Custom function
// Type =myfunc(1)
// in a cell to see the result
function myfunc() {}
myfunc.prototype = new GC.Spread.CalcEngine.Functions.Function("myfunc", 0, 0, {name: "myfunc",description: "This is my first function"});
myfunc.prototype.evaluate = function (args) {
return 100;}
spread.addCustomFunction(new myfunc());

Return Array in Custom Function

The following code sample creates a custom function that returns an array.

$(document).ready(function () {
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });

    // Get the activesheet
    var sheet = spread.getSheet(0);

    // Allow dynamic array
    spread.options.allowDynamicArray = true;

    // Create a factorial function
    function FactorialFunction() {
        this.name = "FACTORIAL";
        this.maxArgs = 1;
        this.minArgs = 1;
    }
    FactorialFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();

    // Evaluating the result of the formula
    FactorialFunction.prototype.evaluate = function (arg) {
        var t = 1;
        var result = [];
        if (arguments.length === 1 && !isNaN(parseInt(arg))) {
            for (var i = 1; i <= arg; i++) {
                t = i * t;
                result.push(t);
            }

            // Returning the CALCArray Object as a result of the function
            return new GC.Spread.CalcEngine.CalcArray([result]);
        }
        return "#VALUE!";
    };

    // Adding a description to the custom Function
    FactorialFunction.prototype.description = function () {
        return {
            name: "FACTORIAL",
            description:
                "This is a function which calculates the factorial from 1 and shows in different cells of a row",
            parameters: [
			   { name: "number" }
            ]
        };
    };
    var factorial = new FactorialFunction();
    sheet.addCustomFunction(factorial);

    // Merge three columns with origin at cell(0,0)
    sheet.addSpan(0, 0, 1, 7, GC.Spread.Sheets.SheetArea.viewport);
    sheet.setValue(0, 0, "This custom factorial function calculates the factorial from 1 to 7 ");
    for (var i = 1; i < 8; i++) {
        sheet.setText(2, i - 1, i + "!");
    }

    // Set custom formula
    sheet.setFormula(3, 0, "=Factorial(7)");
    var style = new GC.Spread.Sheets.Style();
    style.font = "bold 12px Arial";
    style.foreColor = "white";
    style.backColor = "#5B9BD5";
    style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    sheet.setStyle(0, 0, style, GC.Spread.Sheets.SheetArea.viewport);
});