Function Reference

The Function Reference or eta-reduced lambda simplifies a function by eliminating unnecessary abstractions when it has no further operations to perform on its argument. This allows you to use an abbreviated syntax when possible.

SpreadJS enables you to use Function Reference in custom name, LET function’s “name_value” argument, LAMBDA function, and as a LAMBDA function parameter in MAP, BYCOL, BYROW, MAKEARRAY, REDUCE, SCAN, and GROUPBY functions. It also allows you to reference custom functions in formula functions.

Function Reference in Custom Name

You can define a custom name with the content as the function name and use it in the formula.

// Add values to cells.
sheet.setValue(0, 2, 10);
sheet.setValue(1, 2, 20);
sheet.setValue(2, 2, -5);
sheet.setValue(3, 2, 0);
sheet.setValue(4, 2, 20);
sheet.setValue(0, 0, "Formula Result: ->");
sheet.setValue(1, 0, "Formula Text: ->");

// Set color of the cell B1.
sheet.getCell(0, 1).backColor('yellow');
sheet.setValue(7, 0, "Added the custom name 'op' with content as 'SUM' function name.");

// Add FORMULATEXT function to cell B2.
sheet.setFormula(1, 1, "=FORMULATEXT(B1)");

// Add custom name with content as the function name.
spread.addCustomName("op", "SUM");

// Use custom name as formula.
sheet.setFormula(0, 1, "op(C1:C5)");

Function Reference in LET Function

You can define the “name_value” argument of LET function as a function, and use it for calculation.

// Add values to cells.
sheet2.setValue(0, 2, 10);
sheet2.setValue(1, 2, 20);
sheet2.setValue(2, 2, -5);
sheet2.setValue(3, 2, 0);
sheet2.setValue(4, 2, 20);
sheet2.setValue(0, 0, "Formula Result: ->");
sheet2.setValue(1, 0, "Formula Text: ->");

// Set color of the cell B1.
sheet2.getCell(0, 1).backColor('yellow');
sheet2.setValue(7, 0, "The 'SUM' function has been defined in the name_value and used in the 'LET' function");

// Add FORMULATEXT function to cell B2.
sheet2.setFormula(1, 1, "=FORMULATEXT(B1)");

// Add “name_value” as SUM function and use it to calculate.
sheet2.setFormula(0, 1, "=LET(op,SUM,op(C1:C5))");

Function Reference in LAMBDA Function Parameter

You can use the function name as a parameter in a LAMBDA function and use it for calculation. You can also use the function name as a LAMBDA function parameter in the MAP, BYCOL, BYROW, MAKEARRAY, REDUCE, SCAN, and GROUPBY functions.

// LAMBDA Function
// Add values to cells.
sheet3.setValue(0, 0, "Formula Result: ->");
sheet3.setValue(1, 0, "Formula Text: ->");

// Set color of the cell B1.
sheet3.getCell(0, 1).backColor('yellow');

// Add LAMBDA function.
sheet3.setFormula(0, 1, "=LAMBDA(op,op(123,231))(SUM)");

// Add FORMULATEXT function to cell B2.
sheet3.setFormula(1, 1, "=FORMULATEXT(B1)");


// MAP Function
// Add values to cells.
sheet3.setValue(5, 0, "Formula Result: ->");
sheet3.setValue(4, 0, "Formula Text: ->");

// Set color of the cells B6:C9.
sheet3.getRange("B6:C9").backColor('lightblue');

// Set color of the cell B6.
sheet3.getCell(5, 1).backColor('yellow');

// Add table to worksheet.
sheet3.tables.add('table1', 4, 4, 5, 2);

// Add values to table.
sheet3.setArray(4, 4, [
    ['Col1', 'Col2'],
    [20.33, -19],
    [-20.3, 0],
    [-12, -15.3],
    [17, 2]
]);

// Add FORMULATEXT function to cell B5.
sheet3.setFormula(4, 1, "=FORMULATEXT(B6)");

// Add MAP function with function name as a LAMBDA function parameter.
sheet3.setFormula(5, 1, "=MAP(E6:F9,ABS)");


// BYCOL Function
// Add values to cells.
sheet3.setValue(10, 0, "Formula Text: ->");
sheet3.setValue(11, 0, "Formula Result: ->");

// Set color of the cells B12:F12.
sheet3.getRange("B12:F12").backColor('lightblue');

// Set color of the cell B12.
sheet3.getCell(11, 1).backColor('yellow');

// Add table to worksheet.
sheet3.tables.add('table2', 10, 7, 3, 5);

// Add values to table.
sheet3.setArray(10, 7, [
    ['Col1', 'Col2', 'Col3', 'Col4', 'Col5'],
    [1, 2, 9, 10, -5],
    [20, 0, 5, -3, 30]
]);

// Add FORMULATEXT function to cell B11.
sheet3.setFormula(10, 1, "=FORMULATEXT(B12)");

// Add BYCOL function with function name as a LAMBDA function parameter.
sheet3.setFormula(11, 1, "=BYCOL(H12:L13,MAX)");


// GROUPBY Function
// Add values to cells.
sheet3.setValue(15, 0, "Formula Text: ->");
sheet3.setValue(16, 0, "Formula Result: ->");

// Set color of the cells B17:D29.
sheet3.getRange("B17:D29").backColor('lightblue');

// Set color of the cell B17.
sheet3.getCell(16, 1).backColor('yellow');

// Add table to worksheet.
sheet3.tables.add('table3', 15, 7, 13, 7);

// Add values to table.
sheet3.setArray(15, 7, [
    ['Year', 'Category', 'Product', 'Status', 'Sales', 'Rating', 'Profit'],
    [2023, 'Electronics', 'Smart TV', 'Active', 15000, 4.5, 250],
    [2023, 'Fashion', 'Designer Jeans', 'Discontinued', 8000, 4.7, 150],
    [2024, 'Food', 'Organic Granola', 'Active', 5000, 4.8, 100],
    [2023, 'Books', 'Bestseller Novel', 'Active', 12000, 4.6, 180],
    [2024, 'Electronics', 'Laptop', 'Active', 20000, 4.9, 300],
    [2023, 'Beauty', 'Skincare Set', 'Active', 7000, 4.4, 120],
    [2023, 'Home & Garden', 'Garden Tools', 'Active', 6500, 4.3, 130],
    [2024, 'Health', 'Fitness Tracker', 'Active', 9500, 4.6, 190],
    [2023, 'Toys', 'Action Figure', 'Active', 4800, 4.7, 95],
    [2024, 'Automotive', 'Car Accessories', 'Active', 3200, 4.5, 65],
    [2023, 'Sports', 'Basketball', 'Active', 7600, 4.8, 150],
    [2024, 'Office Supplies', 'Notebooks', 'Active', 11000, 4.4, 220]
]);

// Add FORMULATEXT function to cell B16.
sheet3.setFormula(15, 1, "=FORMULATEXT(B17)");

// Add GROUPBY function with function name as a LAMBDA function parameter.
sheet3.setFormula(16, 1, "=GROUPBY(H16:I28,L16:L28,SUM)");

Reference Custom Functions

You can define custom functions and use them in formula functions.

// Add values to worksheet.
sheet4.setArray(0, 0, [
    [5, -3, 0],
    [23, -1, -4],
    [-13, 0, -12]
]);
sheet4.setValue(4, 0, "Formula Text: ->");
sheet4.setValue(5, 0, "Formula Result: ->");

// Set color of the cells B6:D8.
sheet4.getRange('B6:D8').backColor('lightblue');

// Set color of the cell B6.
sheet4.getCell(5, 1).backColor('yellow');

// Define a custom function to check if value is less than 0.
function IsNegativeFunction() {
    this.name = "ISNEGATIVE";
    this.maxArgs = 1;
    this.minArgs = 1;
}
IsNegativeFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
IsNegativeFunction.prototype.evaluate = function (arg) {
    if (parseInt(arg) < 0) {
return true;
    }
    return false;
};

// Add custom function to workbook.
spread.addCustomFunction(new IsNegativeFunction());

// Add MAP function with custom function name as a LAMBDA function parameter.
sheet4.setFormula(5, 1, "=MAP(A1:C3,ISNEGATIVE)");

// Add FORMULATEXT function to cell B5.
sheet4.setFormula(4, 1, "=FORMULATEXT(B6)");

Note: You must enable the allowDynamicArray property for this functionality to be enabled.