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('NjdlOWU4ZDdkOGIyNDI5YjI4NWZiN2E1YWNlNzExYjg3ZGVmZGMyNGM2YjUwNTUyZjQ3ZGY3ZjNhYmRkMzE0NDE0YzdjYTQ4OTYxNjU0N2MwNzliZGQ4MmNhMTMzMTcyNmEwNWFhNDJlZmNhNjkwMTg2ZmY3NTRkZGNlZTEyNDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpJNE16QXdNelE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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('NjdlOWU4ZDdkOGIyNDI5YjI4NWZiN2E1YWNlNzExYjg3ZGVmZGMyNGM2YjUwNTUyZjQ3ZGY3ZjNhYmRkMzE0NDE0YzdjYTQ4OTYxNjU0N2MwNzliZGQ4MmNhMTMzMTcyNmEwNWFhNDJlZmNhNjkwMTg2ZmY3NTRkZGNlZTEyNDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpJNE16QXdNelE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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('NjdlOWU4ZDdkOGIyNDI5YjI4NWZiN2E1YWNlNzExYjg3ZGVmZGMyNGM2YjUwNTUyZjQ3ZGY3ZjNhYmRkMzE0NDE0YzdjYTQ4OTYxNjU0N2MwNzliZGQ4MmNhMTMzMTcyNmEwNWFhNDJlZmNhNjkwMTg2ZmY3NTRkZGNlZTEyNDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpJNE16QXdNelE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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('NjdlOWU4ZDdkOGIyNDI5YjI4NWZiN2E1YWNlNzExYjg3ZGVmZGMyNGM2YjUwNTUyZjQ3ZGY3ZjNhYmRkMzE0NDE0YzdjYTQ4OTYxNjU0N2MwNzliZGQ4MmNhMTMzMTcyNmEwNWFhNDJlZmNhNjkwMTg2ZmY3NTRkZGNlZTEyNDAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpJNE16QXdNelE0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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>