Products

JSS Formula Premium
JSS Formula Premium is a JavaScript application for parsing and executing spreadsheet-like formulas. It handles ranges, variables, worksheets and a great number of formulas available in other spreadsheet software such as Excel or Google Sheets. It deals with the JavaScript precision issues and is compatible with Jspreadsheet Pro plugins.Formula Basic | Formula Premium | |
---|---|---|
License | MIT | Requires a license |
Scope | window | restricted scope |
Parser | new Function | Custom parser |
JavaScript precision issues | No | Yes |
Date operations | No | Yes |
Cross worksheet/spreadsheet calculations | No | Yes * |
Defined names | No | Yes ** |
Matrix calculations | No | Yes ** |
Number of implemented formulas | 403 | 455 |
Available formulas
You can find a list of the available formulas here.Compatibility
The plugin can be used stand-alone or integrated with Jspreadsheet CE, BASE, and PRO.* Only available on stand-alone and with the PRO distributions.
** Only available on stand-alone and with the PRO v8 distributions.
License
This plugin requires a license that should be associated with one specified domain. If you need a license for redistribution or SaaS, please contact contact@jspreadsheet.comPrecision
JSS Formula Premium has an adjustPrecision flag to help with JS rounding problems. The solution is based on an adaptative toFixed depending on the number of decimals.// Activate the precision adjust formula.adjustPrecision = true; formula('37.02 + 2.56'); // Without adjustPrecision: 39.580000000000005 // With adjustPrecision: 39.58 formula('185.32 - 84.78'); // Without adjustPrecision: 100.53999999999999 // With adjustPrecision: 100.54 formula('25.92 * 3.33'); // Without adjustPrecision: 86.31360000000001 // With adjustPrecision: 86.3136 formula('9.15 / 6'); // Without adjustPrecision: 1.5250000000000001 // With adjustPrecision: 1.525
NOTE: When this option is enabled, the results will be rounded to a maximum of ten decimal places.
Usage
The formula method receives the expression and the variables that will support the calculations.@param {string} expression - the formula to be calculated @param {object} variables - the variables and values necessary to parse the expression @param {number=} x - a optional coordinate reference @param {number=} y - a optional coordinate reference formula(expression: String, variables: Object, [x: Number], [y: Number]) : string|array
Initialization
The basic standalone usage.<html> <script src="https://jspreadsheet.com/v8/plugins/formula-pro.js"></script> <script> // Activate the precision adjust formula.adjustPrecision = true; // Define the license just once formula.license('YjQzMzdlOTRiOGY3ZTQ0ZDQ4ZTI1YWU3MDFjMDI0ZWJmOTNjODA'); </script> </html>
Examples
You can run the following tests in the browser's console when initialized as above.Range operations
formula('A1:B1*2', { A1: 2, B1: 4 }); // Returns Array[ 4, 8 ] formula('SUM(A1:B1*2)', { A1: 1, B1: 'A2+B2', A2: 3, B2: 4 }) // Returns 16 formula('SUM(A1:A6)', { A1: 2, A2: 4, A3: 5, A4: 1, A5: 5, A6: 1 }); // Returns 18 formula('AVERAGE(CALCULATION*10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 }) // Returns 20 formula('SUM(B:B)', { B1: 1, B2: 1, B3: 14 }); // Returns 16
Conditional calculations
formula('IF(B1=0,0,B9/B1)', { B1:0, B9: 3 }); // Returns zero when B1 is zero formula('IF(true, CALCULATION, 10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 }) // Returns [[1], [2], [3]] formula('IF(C16+C15!=0,C13+C14,false)', { C16: 0, C15: 0, C14: 3, C13: 12 }) // Returns false
Comparisons
formula('1*2<1^4'); // Returns false formula('(1==1)<>(2>2)') // Returns true
Date calculations
formula('NOW()+1'); // Today + one day formula('DATE(2021,1,1) > DATE(2021,2,1)'); // false
Cross worksheets
formula('SHEET1!A1*A1', { 'SHEET1!A1': 2, 'A1': 3 }); // Returns 6 formula('SUM(SHEET3!B1:B3)', { 'SHEET3!B1': 3, 'SHEET3!B2': 3, 'SHEET3!B3': 4 }); // Returns 10 formula('SUM(B:B)', { 'B1': 1, 'B2': 2, 'B3': 4 }); // Returns 7
Worksheet operations
formula('SHEET1!A1*10', { SHEET1: [[1,2,3],[4,5,6]] }); // Returns 1 * 10 formula('SHEET1!B1*SHEET2!B1', { SHEET1: [[1,2,3],[4,5,6]], SHEET2: [[10,20,30]] }); // Returns 2 * 20
Live calculator
For testing, you can use the following form, or use the browser’s console.{{self.results}}
Integrating with JSS spreadsheet
How to integrate JSS spreadsheet and the formula-pro plugin.Using CDN
Using NPM
<html> <script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script> <script src="https://jsuites.net/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" /> <script src="https://jspreadsheet.com/v8/plugins/formula-pro.js"></script> <div id='spreadsheet'></div> <script> // License for Formula Plugin jspreadsheet.setLicense('YWYwOWM1NjYzNzRiNWYzOWU5N2RmOTkzMzdhOGI4MWM4ZGZlZDk4ZmQwNWM4ZmE1ZmZjZWE0MTk1YWY4ZTQxNzhlZGYwNTdjNTg1OTRmZTY1YTMzY2M0M2IwNWM2MGRmMmMzNTkwMWZmMjFiZTBkMjA5MDVlY2RhNDVhYWY0ZmIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTRNelF4TkRBd01Dd2laRzl0WVdsdUlqcGJJbXB6YUdWc2JDNXVaWFFpTENKcWMzQnlaV0ZrYzJobFpYUXVZMjl0SWl3aVkzTmlMbUZ3Y0NJc0luVmxMbU52YlM1aWNpSXNJblZ1YVhSbFpDNWxaSFZqWVhScGIyNGlMQ0p6WVc5eWIyTnJMbU52YlNJc0luVmxMbU52YlM1aWNpSXNJblZ1YVhSbFpDNWxaSFZqWVhScGIyNGlMQ0pzYjJOaGJHaHZjM1FpWFN3aWNHeGhiaUk2SWpNaUxDSnpZMjl3WlNJNld5SjJOeUlzSW5ZNElpd2lkamtpTENKd1lYSnpaWElpTENKemFHVmxkSE1pTENKbWIzSnRjeUlzSW5KbGJtUmxjaUlzSW1admNtMTFiR0VpTENKamFHRnlkSE1pTENKcGJYQnZjblJsY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpWm05eWJYTWlMQ0ptYjNKdGRXeGhJaXdpY21WdVpHVnlJaXdpY0dGeWMyVnlJaXdpYVcxd2IzSjBaWElpWFgwPQ=='); // Add-on for Jspreasheet jspreadsheet.setExtensions({ formula }); // Create the spreadsheets jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [ { minDimensions: [10, 10] }, { minDimensions: [10, 10] }, ], }); </script> </html>
// Jspreadsheet Pro import jspreadsheet from 'jspreadsheet'; // Formula Premium Plugin import formula from '@jspreadsheet/formula-pro'; // License for Formula Plugin jspreadsheet.setLicense('YWYwOWM1NjYzNzRiNWYzOWU5N2RmOTkzMzdhOGI4MWM4ZGZlZDk4ZmQwNWM4ZmE1ZmZjZWE0MTk1YWY4ZTQxNzhlZGYwNTdjNTg1OTRmZTY1YTMzY2M0M2IwNWM2MGRmMmMzNTkwMWZmMjFiZTBkMjA5MDVlY2RhNDVhYWY0ZmIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTRNelF4TkRBd01Dd2laRzl0WVdsdUlqcGJJbXB6YUdWc2JDNXVaWFFpTENKcWMzQnlaV0ZrYzJobFpYUXVZMjl0SWl3aVkzTmlMbUZ3Y0NJc0luVmxMbU52YlM1aWNpSXNJblZ1YVhSbFpDNWxaSFZqWVhScGIyNGlMQ0p6WVc5eWIyTnJMbU52YlNJc0luVmxMbU52YlM1aWNpSXNJblZ1YVhSbFpDNWxaSFZqWVhScGIyNGlMQ0pzYjJOaGJHaHZjM1FpWFN3aWNHeGhiaUk2SWpNaUxDSnpZMjl3WlNJNld5SjJOeUlzSW5ZNElpd2lkamtpTENKd1lYSnpaWElpTENKemFHVmxkSE1pTENKbWIzSnRjeUlzSW5KbGJtUmxjaUlzSW1admNtMTFiR0VpTENKamFHRnlkSE1pTENKcGJYQnZjblJsY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpWm05eWJYTWlMQ0ptYjNKdGRXeGhJaXdpY21WdVpHVnlJaXdpY0dGeWMyVnlJaXdpYVcxd2IzSjBaWElpWFgwPQ=='); // Add-on for Jspreasheet jspreadsheet.setExtensions({ formula }); // Create a spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [ { minDimensions: [10, 10] }, { minDimensions: [10, 10] }, ] });