Products

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.
MethodDescription
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 validation
PropertyDescription
index: number Index of an array of validations.
value: Validation[] Array of validation objects

Validation object

PropertyDescription
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]);
}



Create applications with spreadsheet-like controls