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.

Method	Description
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

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
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.

See this example on React at 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('MzQ2NmNlMzNiOGEzZjBhYzM2NjE4ZDZkYzVjNjhjYzQ1NWFiMzA1OWJkMDlkZGY4ODgzNThiNjA2ZWYwOGE2NThjNjY0YmNlYjk1YjA4YjMyY2I1YWRlNmIzYmUxNWVmNWZmNTc4NDU5MDg4M2MyNjA3MWZiMWRhNWIzYmY1MTMsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNNRFkyT0RBNU1Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

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

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