Validations 
This extensions allow the implementation of cell validations and conditional formatting on cells.Documentation
Methods
The following methods can be used to get or set comments in one or multiple cells.Method | Description |
---|---|
getValidations | Get validations by index.getValidations(index: Number) => Object @Param number - validation index. |
setValidations | Add or edit a new validation.setValidations(validations: Validations[]) => void
@param {array} - Array with the validations. |
resetValidations | Add or edit a new validation.setValidations(validations: Array) => void
@param {array} - Array with the validation indexes to be reset. |
Validations
All available properties to define a validationProperty | Description |
---|---|
index: number | Index of an array of validations. |
value: Validation[] | Array of validation objects |
Validation object
Property | Description |
---|---|
range: string | A cell or a range of cells affect by the validation rules. Example: Sheet1!A1:A8 |
type: string | number | text | date | list | textLength | empty | notEmpty |
Action: string | warning | reject | format |
criteria: string | '=' | '!=' | '>=' | '>' | '<=' | '<' | 'between | 'not between' | 'valid date' | 'valid email' | 'valid url' | 'contains' | 'not contains' | 'begins with' | 'ends with' |
text: string | Define the warning or reject message. |
allowBlank: boolean | Allow blank values. Only valid for warning messages |
format: object | color, background-color, font-weight, font-style. |
className: string | Class name to be added to the cell when the condition is match. |
Author
Jspreadsheet Pro Team.License
Available on the premium edition only.Installation
Please choose one of the following options// From NPM npm install @jspreadsheet/validations // From CDN https://cdn.jsdelivr.net/npm/@jspreadsheet/validations/dist/index.min.js
Example
How to create validations on your spreadsheet during initialization or programmatically.See a React implementation of this example on codesandbox
Browser
NPM
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <script src="https://cdn.jsdelivr.net/npm/lemonadejs/dist/lemonade.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/validations/dist/index.min.js"></script> <div id="spreadsheet"></div> <script> // Set the license for both plugin and the spreadsheet jspreadsheet.setLicense('NTJiYTgyMmQ3NTM1ODU3NjM0NzM0YzRiNWNhMTFmZmQyNTFiOTc2ZTdlYjVmNzdjOWQzM2M2MTc1ZWY3NDkxZDVjNGQwOGRjYTE4YTBhZDIzYzUxZTVhNTQxZDZiZTczODI4ZjJmYjQ1YjIyNmJmMGViMWFhYzFhY2U3MzhhNWQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTRNVEF3TURJek5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0='); // Set the extensions jspreadsheet.setExtensions({ validations }); // Create the spreadsheet var spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), { toolbar: true, worksheets: [{ data: [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ], minDimensions: [6, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); var create = function() { spreadsheet[0].setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } var remove = function() { // Remove the validation by the index of the array spreadsheet[0].parent.config.validations spreadsheet[0].resetValidations([1]); } </script> </html>
// Import the JSS library import jspreadsheet from 'jspreadsheet'; // Import the JSS validation extension import validations from '@jspreadsheet/validations'; // Set the license for both plugin and the spreadsheet jspreadsheet.setLicense('NTJiYTgyMmQ3NTM1ODU3NjM0NzM0YzRiNWNhMTFmZmQyNTFiOTc2ZTdlYjVmNzdjOWQzM2M2MTc1ZWY3NDkxZDVjNGQwOGRjYTE4YTBhZDIzYzUxZTVhNTQxZDZiZTczODI4ZjJmYjQ1YjIyNmJmMGViMWFhYzFhY2U3MzhhNWQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTRNVEF3TURJek5Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0='); // Set the extensions jspreadsheet.setExtensions({ validations }); // Create the spreadsheet let spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), { toolbar: true, worksheets: [{ data: [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ], minDimensions: [6, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); var create = function() { spreadsheet[0].setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } var remove = function() { // Remove the validation by the index of the array spreadsheet[0].parent.config.validations spreadsheet[0].resetValidations([1]); }