Products

Back to the Documentation

Spreadsheet formulas

Jspreadsheet implements spreadsheet-like formulas, providing great compatibility with major spreadsheet software, such as Excel or Google Sheets. In this section, you can find which formulas are available, how to create custom formulas and other features.

  • Automatic formula update on copy, paste and corner dragging.
  • The formula returns DOM objects to cells.
  • Cross worksheet calculations.
  • Custom Excel-like formulas

Formula extension distribution

The formulas are available as Basic and Premium. The Basic is the default for all Jspreadsheet distributions, while Premium is distributed as an extension for the Enterprise and Premium plans and provides some extra features such as:

  • Matrix calculations;
  • Range Variables;
  • Extended formulas;
  • Private scope;
  • Special internal properties, x, y and instance;
  • It runs in stand-alone applications;

Special properties for Formulas Premium

When the Jspreadsheet Pro invokes the method from a valid cell, you will have access to the coordinates of the cell and the instance of the worksheet.

let YOUR_METHOD = function() {
    // Return the coordinates to the cell
    return this.x + ',' + this.y;
}

Available formulas

We are working to bring as many formulas as possible. Meanwhile, you can check for the existing implementation. For security reasons, all references should use capital letters, including the implementation of custom methods.

Settings

A summary of configurations related to the use of formulas.

Configuration Description
Global configuration for the spreadsheet.
secureFormulas?: boolean Enable formula security. Default: true
editorFormulas?: boolean Enable the formula editor. Default: true
parseFormulas?: boolean Enable formula calculations. Default: true
debugFormulas?: boolean Enable the formula debug notices. Default: true
autoIncrement?: boolean Formula variable increment on cloning or copying. Default: true
Configuration for columns
shiftFormula?: boolean Update the formula on cloning (used for custom columns). Default: true

Spreadsheet formula events

All events related to formulas.

Event Description
onbeforeformula?: Function Intercept and parse a formula just before the execution.
onbeforeformula(worksheet: Object, expression: String, x: Number, y: Number) => String
onformulachain?: Function Get the information about the expressions executed from the formula chain.
onformulachain(worksheet: Object, executions: Object) => void

Spreadsheet methods

All methods related to formulas in the JSS context.

Method Description
executeFormula?: Function Execute a formula.
executeFormula(expression: string, x?: number, y?: number, caching?: boolean, basic?: boolean) => String

Special formulas

To support calculations, Jspreadsheet has a few special formulas listed below:

Method Example
=TABLE() Return the Jspreadsheet table instance.
=COLUMN() Return the column number where the formula has been executed. The argument is only available on the Formula Pro version.
=COLUMN(col?: Number)
=ROW() Return the row number where the formula has been executed. The argument is only available on the Formula Pro version.
=ROW(row?: Number)
=CELL() Return the cell string identification.
=VALUE() Return the cell value based on the colNumber and rowNumber. You can get the raw or processed value.
=VALUE(col?: Number, row?: Number, processedValue?: Boolean)

Roadmap for the formulas

We are constantly adding new methods and features to bring Jspreadsheet in line with modern software.

Custom formulas

It is possible to create custom methods that will be available in the spreadsheet. The custom method should return the appropriate content for the cell. From version 7 on, Jspreadsheet accepts a DOM element as a return of a function. IMPORTANT : All method names should be created using capital letters.

<div id="spreadsheet"></div>
<script>
// New custom formula
let COLORIZE = function(v) {
    let d = document.createElement('div');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Add to the correct scope
formula.setFormula({ COLORIZE });

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300' },
            { type: 'text', width:'200' },
        ]
    }]
});
</script>

Example

Basic spreadsheet with formulas.

A basic spreadsheet example using formulas, including currency, percentage and mask.

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
let style = 'background-color:orange; font-weight: bold;';

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjM3OGEyYjliMzk3YWQ0MmQ5MWFjMzlhMWQ5MzNiYjQyMTliZWRmZmI0NWNhOTg4OTZiZGFlNzRhMjgyMTA5MjdiYzcyNTVlNmFiZDZiNjA0Mjg5YzgwNjA3ZjAxM2Y3NTdhMThhNDZlYmIxYWIxYzcwZTA5Yjk0M2U2NmE0ZTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1Ea3lNVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    worksheets: [{
        data: [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
            [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
            [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
            [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
            [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
        ],
        columns: [
            { type: 'text', title:'Product', width:'300' },
            { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
            { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
            { type: 'text', title:'Discount', mask:'0.00%' },
            {
                type: 'number',
                title: 'Total',
                width: '100px',
                format: 'US #.##0,00;[Red](#.##0,00)',
            },
        ],
        style: {
            A9: style,
            B9: style,
            C9: style,
            D9: style,
            E9: style,
        },
        columnSorting:false,
        worksheetName: 'Calculations',
    }]
});
</script>
</html>

Cross worksheet formulas

Cross-worksheet formulas have been implemented from version 7 on. The following example has cross-worksheet and cross-spreadsheet formula calculations.

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjM3OGEyYjliMzk3YWQ0MmQ5MWFjMzlhMWQ5MzNiYjQyMTliZWRmZmI0NWNhOTg4OTZiZGFlNzRhMjgyMTA5MjdiYzcyNTVlNmFiZDZiNjA0Mjg5YzgwNjA3ZjAxM2Y3NTdhMThhNDZlYmIxYWIxYzcwZTA5Yjk0M2U2NmE0ZTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1Ea3lNVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
            data: [
                ['Cheese', 10, 6.00, "=B1*C1"],
                ['Apples', 5, 4.00, "=B2*C2"],
                ['Carrots', 5, 1.00, "=B3*C3"],
                ['Oranges', 6, 2.00, "=B4*C4"],
                ['Reference from the spreadsheet above: ', '=Calculations!C9', '', '=Calculations!E9'],
            ],
            worksheetName: 'Example1',
            minDimensions: [5,5],
            defaultColWidth: '50px',
            columns: [{
                width: '300px'
            }],
            cells: {
                B5: { mask:'#.##0' },
                D5: { mask:'#.##0' },
            }
        },
        {
            data: [
                ['20%', "=Example1!D1"],
                ['20%', "=Example1!D2"],
                ['20%', "=Example1!D3"],
                ['20%', "=Example1!D4"],
            ],
            worksheetName: 'Example2',
            minDimensions: [5,5],
        },
    ]
});
</script>
</html>

Defined names Premium edition

From version 8 on, defined names are available on the premium edition.

This feature is only available in with the JSS Formula Premium plugin.

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjM3OGEyYjliMzk3YWQ0MmQ5MWFjMzlhMWQ5MzNiYjQyMTliZWRmZmI0NWNhOTg4OTZiZGFlNzRhMjgyMTA5MjdiYzcyNTVlNmFiZDZiNjA0Mjg5YzgwNjA3ZjAxM2Y3NTdhMThhNDZlYmIxYWIxYzcwZTA5Yjk0M2U2NmE0ZTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1Ea3lNVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            data: [
                [ 1, '=SUM(TEST*10)' ],
                [ 2, '' ],
                [ 3, '' ],
                [ 4, '' ],
            ],
            minDimensions: [5,5],
            worksheetName: 'Named ranges',
        }
    ],
    // On JSS the defined names must be uppercase
    definedNames: {
        'TEST': 'A1:A4',
    },
});
</script>
</html>

Custom methods

All custom methods should be declared in capital letters.

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
let COLORIZE = function(v) {
    let d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Add formula to the necessary scope
formula.setFormula({ COLORIZE });

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjM3OGEyYjliMzk3YWQ0MmQ5MWFjMzlhMWQ5MzNiYjQyMTliZWRmZmI0NWNhOTg4OTZiZGFlNzRhMjgyMTA5MjdiYzcyNTVlNmFiZDZiNjA0Mjg5YzgwNjA3ZjAxM2Y3NTdhMThhNDZlYmIxYWIxYzcwZTA5Yjk0M2U2NmE0ZTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNMU1Ea3lNVGs1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300px' },
            { type: 'text', width:'200px' },
        ]
    }]
});
</script>
</html>

Edit this page on GitHub