Products

Validations and conditional formatting

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: Object|Array) => void
@param {array} - Array with the validations.


Settings

All available properties to define a validation
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
Action: string warning | reject | format
criteria: string Selected criteria. '=' | '!=' | '>=' | '>' | '<=' | '<' | 'between | 'not between' | 'valid date' | 'valid email' | 'valid url' | 'contains' | 'not contains'
text: string Define the warning or reject message.
allowBlank: boolean Allow blank values.
format: object color, background-color, font-weight.
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

A basic example for creating a validation on the spreadsheet during initialization and adding a new one programmatically after initialization.






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('MmYxNWM3OTQ5MzEyNjllNzIxNjM4YzU0ODk0ODJjZmNhOTBlZjBhYmY2OWQ0YjQyOTA4ZmNjYTM2YmYzNjIzMDk0OTdkYTNiM2M3NGVlYzA5MmJlMWMxMWQ4YzBiNjJjYmZhYWE0ZDc3YjljYTRmM2Y4NDJjMDg2ZjQxMWZjOTIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJNREk0TmpnNU5Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

// 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('MmYxNWM3OTQ5MzEyNjllNzIxNjM4YzU0ODk0ODJjZmNhOTBlZjBhYmY2OWQ0YjQyOTA4ZmNjYTM2YmYzNjIzMDk0OTdkYTNiM2M3NGVlYzA5MmJlMWMxMWQ4YzBiNjJjYmZhYWE0ZDc3YjljYTRmM2Y4NDJjMDg2ZjQxMWZjOTIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJNREk0TmpnNU5Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

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