Custom formulas
Create custom spreadsheet-like formulas in JavaScript and explore the power of this feature. The return from the method can be a string or a DOM element.Special properties
When the spreadsheet invokes any method, the cell coordinates and the worksheet instance are available, and can be used for conditional operations, as shown below:This feature is only available in Formula Premium
<html> <script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <div id="spreadsheet"></div> <script> // Set the JSS spreadsheet license jspreadsheet.setLicense('ZWY5Y2FjMDNmNzBmNzdhY2RkOTBlNDA2NzMxYzMwZDJkMmY2ZjUzMTE2Nzc0YmM3NDI0NDdmZjMwOWI2MDQ0MTk1YmI0MjUzMzEzOGQwNWFhN2U3OTAyOTY2Yjk1MGRjZmVjYjliZDJjZTczNDQxN2FkNDhmMGU5MTA5NTU5NDksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalV3TkRVNE1Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create a custom javascript method (capital case) var SUMCOL = function() { var total = 0; // Sum all values in the column up to where the formula was called. for (var j = 0; j < this.y; j++) { total += parseInt(this.instance.options.data[j][this.x]); } return total; } // Send custom formula to the correct scope formula.setFormula({ SUMCOL }) // Create the spreadsheet jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ [ 'Apple', 931 ], [ 'Google', 431 ], [ 'Amazon', 534 ], [ 'Total', '=SUMCOL()' ], ], minDimensions: [8,4], }] }); </script> </html>
DOM Elements
The cells accept a DOM element returned from a formula, as below:Source code
<html> <script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <div id="spreadsheet"></div> <script> // Set the JSS spreadsheet license jspreadsheet.setLicense('ZWY5Y2FjMDNmNzBmNzdhY2RkOTBlNDA2NzMxYzMwZDJkMmY2ZjUzMTE2Nzc0YmM3NDI0NDdmZjMwOWI2MDQ0MTk1YmI0MjUzMzEzOGQwNWFhN2U3OTAyOTY2Yjk1MGRjZmVjYjliZDJjZTczNDQxN2FkNDhmMGU5MTA5NTU5NDksZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalV3TkRVNE1Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create a custom javascript method (capital case) var COLORIZE = function(v) { var d = document.createElement('span'); d.style.color = v; d.innerText = v.toUpperCase(); return d; } // Send custom formula 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> </html>