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/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script> <div id="spreadsheet"></div> <script> // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('ZWI0MTQ1MDA0ZDVkMTI2MmIxYjk5ZTczMmFkZjNlZjJhZTU3OTY1MGRkMWJmNDUxMjI0MWY5NTU3ZmVlNTVhZjZiMjNjZmQxYjQxYTMzNTE3NmZiODE0MWMzMmZhMGI3NDIwZDY5Zjg5NjY1OWUwZjhmMzkzNjljNGYzZmU1ZDUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRBM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5'); // Set the formula pro extension jspreadsheet.setExtensions({ formula }); // Create a custom javascript method (capital case) var SUMCOL = function() { // Which cell is this? var affectedCell = this.instance.helpers.getColumnNameFromCoords(this.x, this.y); // Total 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]); // Variable var cellName = this.instance.helpers.getColumnNameFromCoords(this.x, j); // Formula chain if (! this.instance.formula[affectedCell]) { this.instance.formula[affectedCell] = []; } this.instance.formula[affectedCell].push(cellName); } 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/v9/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.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('ZWI0MTQ1MDA0ZDVkMTI2MmIxYjk5ZTczMmFkZjNlZjJhZTU3OTY1MGRkMWJmNDUxMjI0MWY5NTU3ZmVlNTVhZjZiMjNjZmQxYjQxYTMzNTE3NmZiODE0MWMzMmZhMGI3NDIwZDY5Zjg5NjY1OWUwZjhmMzkzNjljNGYzZmU1ZDUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRBM05pd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5'); // 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>