Documentation keyboard_arrow_right Validations

Data Validations and Conditional Formatting

This section covers data validations and conditional formatting. Data validations enforce rules on cell input to ensure entries meet defined requirements and flag invalid data for correction. Conditional formatting applies visual styles to cells that meet specified conditions, making it easier to identify patterns, errors, or key values.

What's new with Version 12

• Support validation using formulas.
• New setter for custom validations:
  • jspreadsheet.setValidationHandler(name, handler);
• New validation types:
  • Type formula to validate cells based on a formula result.
  • Type list with a formula to build dynamic lists.

Documentation

Methods

Methods available to manage validations:

Method	Description
getValidations	Get one validation by index, or all when index is omitted. getValidations(index?: number) => Validation | Validation[] | undefined
setValidations	Insert or update validations. setValidations(validations: Validations | Validations[]) => false | undefined
resetValidations	Reset validations by index or indexes. Omit the argument to remove all validations. resetValidations(index?: number | number[]) => false | undefined
loadValidations	Load validation rules for a cell. loadValidations(x: number, y: number) => Validation[] | undefined
hasErrors	Check for validation errors on the whole worksheet, a column, or a cell. hasErrors(): boolean | number[] - Returns true/false or array of error coordinates hasErrors(col: string): boolean - Check specific column hasErrors(col: number, row: number): boolean - Check specific cell

Create or update validations

You can update existing validations or create new ones using setValidations, passing an object or array of objects defined by:

Property	Description
index?: number | null	Use for updating an existing validation by index. Omit when creating a new one.
value: Validation	Validation definition object.

Events

Events are triggered when validations are created, updated, or removed.

Event	Description
onvalidation	onvalidation(worksheet: worksheetInstance, records: { index: number, value: Validation | null, oldValue: Validation | null }[]) => void

Settings

Validations are defined by an array of objects with the following structure.

Validation object

Property	Description
range: string	A cell or range of cells affected by the validation rules. Example: Sheet1!A1:A8 or a whole column as Sheet1!E:E.
type: string	One of the options: 'number' | 'text' | 'date' | 'list' | 'textLength' | 'empty' | 'notEmpty' | 'formula' | string.
action: string	One of the options: 'warning' | 'reject' | 'format'.
criteria: string	One of the options: '=' | '!=' | '>=' | '>' | '<=' | '<' | 'between' | 'not between' | 'valid date' | 'valid email' | 'valid url' | 'contains' | 'not contains' | 'begins with' | 'ends with'.
text: string	Defines the warning or rejection message.
allowBlank: boolean	Allows blank values (only valid for warning messages).
format: object	Formatting options: color, background-color, font-weight, font-style.
className: string	CSS class name to be added to the cell when the condition is met.
dropdown: boolean	Force a dropdown picker when the validation type is a list.

Custom Validations

You can create custom cell validations in the Jspreadsheet data grid by defining a handler that returns true or false based on your logic. Inside the function handler, the keyword this refers to the cell object, giving you access to:

Property	Description
this.x	column index
this.y	row index
this.w	worksheet instance

Note: Custom validations are not exported to XLSX when using the render extension. This limitation exists because custom JavaScript functions cannot be translated to Excel's native validation rules.

Example

To validate that a cell value starts with "=", you can define a custom validation handler named isFormula:

/**
 * @param {string} name - Handler name
 * @param {function} handler - Validation function
 * @return {boolean} Validation should return true or false
 */
jspreadsheet.setValidationHandler('isFormula', function(value, options) {
    // Get the raw value of the cell (this.w is the worksheet instance)
    let raw = this.w.getValueFromCoords(this.x, this.y);
    // Validate if it is a formula
    return raw && typeof raw === 'string' && raw[0] === '=';
});

You can then declare this validation in the spreadsheet configuration:

// Create the spreadsheet
const grid = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ["A1*2"],
            ["=A2*2"],
            ["A3*2"],
            ["=A4*2"],
            ["A5*2"]
        ],
        minDimensions: [6, 6],
    }],
    validations: [{
        range: 'Sheet1!A1:A6',
        action: 'warning',
        text: 'This is not a formula',
        type: 'isFormula', // Declared using setValidationHandler
    }]
});

Validations Extension

The Validation Extension lets end-users manage cell validations directly in the data grid. It adds a toolbar icon that opens an easy-to-use interface for creating new rules or editing existing ones.

Learn More

Examples

Basic Data Grid with Validations

Validations in Jspreadsheet help maintain data integrity by enforcing rules on cell input. They can be defined during initialization or added programmatically.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div><br><br>

<input type="button" value="Add new validation" id="btn1">
<input type="button" value="Remove validation" id="btn2">

<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet
const grid = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [10,"=A1*2"],
            [20,"=A2*2"],
            [30,"=A3*2"],
            [40,"=A4*2"],
            [50,"=A5*2"]
        ],
        minDimensions: [6, 6],
        worksheetName: 'Formulas',
    }],
    validations: [{
        range: 'Formulas!A1:A6',
        action: "warning",
        criteria: "between",
        type: "number",
        allowBlank: false,
        value: [10, 30],
    }]
});

const create = function() {
    // It will create on position index 1
    grid[0].setValidations([{
        value: {
            range: 'Formulas!B1:B3',
            action: "format",
            criteria: "<",
            type: "number",
            value: [500],
            format: { color: '#ff0000' },
        }
    }]);
}

const remove = function() {
    // Remove the validation by the index of the array spreadsheet[0].parent.config.validations
    grid[0].resetValidations([1]);
}

document.getElementById("btn1").onclick = create
document.getElementById("btn2").onclick = remove
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Example on how to create a new validation on React
const create = function (worksheet) {
    worksheet.setValidations([{
        index: 1,
        value: {
            range: 'Formulas!B1:B3',
            action: "format",
            criteria: "<",
            type: "number",
            value: [500],
            format: {color: '#ff0000'},
        }
    }]);
}

const remove = function (worksheet) {
    // Remove the validation by the index of the array spreadsheet[0].parent.config.validations
    worksheet.resetValidations([1]);
}

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [
        [10, "=A1*2"],
        [20, "=A2*2"],
        [30, "=A3*2"],
        [40, "=A4*2"],
        [50, "=A5*2"]
    ]
    // Validations
    const validations = [{
        range: 'Formulas!A1:A6',
        action: "warning",
        criteria: "between",
        type: "number",
        allowBlank: false,
        value: [10, 30],
    }]

    // Render component
    return (
        <>
            <Spreadsheet ref={spreadsheet} validations={validations}>
                <Worksheet data={data} minDimensions={[6, 6]} worksheetName="Formulas" />
            </Spreadsheet>
            <input type="button" value="Add new validation" onClick={() => create(spreadsheet.current[0])}/>
            <input type="button" value="Remove validation" onClick={() => remove(spreadsheet.current[0])}/>
        </>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :validations="validations">
        <Worksheet :data="data" worksheetName="Formulas" />
    </Spreadsheet>
    <input type="button" value="Add new validation" @click="create" />
    <input type="button" value="Remove validation" @click="remove" />
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    methods: {
        create() {
            // Get the first worksheet instance
            let worksheet = this.$refs.spreadsheet.current[0];

            // Add a new validation to the Sheet1
            worksheet.setValidations([{
                index: 1,
                value: {
                    range: 'Formulas!B1:B3',
                    action: "format",
                    criteria: "<",
                    type: "number",
                    value: [500],
                    format: { color: '#ff0000' },
                }
            }]);
        },
        remove() {
            // Get the first worksheet instance
            let worksheet = this.$refs.spreadsheet.current[0];

            // Destroy the validation rules index one.
            worksheet.resetValidations([1]);
        },
    },
    data() {
        // Data
        const data = [
            [10,"=A1*2"],
            [20,"=A2*2"],
            [30,"=A3*2"],
            [40,"=A4*2"],
            [50,"=A5*2"]
        ];
        // Validations
        const validations = [{
            range: 'Formulas!A1:A6',
            action: "warning",
            criteria: "between",
            type: "number",
            allowBlank: false,
            value: [10, 30],
        }];

        return {
            data,
            validations
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

@Component({
    standalone: true,
    selector: "app-root",
    template: `
        <div #spreadsheet></div>
        <input type="button" value="Add new validation" (click)="create()" />
        <input type="button" value="Remove validation" (click)="remove()" />`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    [10,"=A1*2"],
                    [20,"=A2*2"],
                    [30,"=A3*2"],
                    [40,"=A4*2"],
                    [50,"=A5*2"]
                ],
                minDimensions: [6, 6],
                worksheetName: "Formulas"
            }],
            validations: [{
                range: 'Formulas!A1:A6',
                action: "warning",
                criteria: "between",
                type: "number",
                allowBlank: false,
                value: [10, 30],
            }]
        });
    }

    create() {
        // Create or update the validation on position one in the array of validations
        this.worksheets[0].setValidations([{
            index: 1,
            value: {
                range: 'Formulas!B1:B3',
                action: "format",
                criteria: "<",
                type: "number",
                value: [500],
                format: { color: '#ff0000' },
            }
        }]);
    }

    remove() {
        // Remove the validation by the index
        this.worksheets[0].resetValidations([1]);
    }
}

Conditional Formatting

Conditional formatting allows you to visually highlight cells based on specific conditions. This example demonstrates how to apply different formatting styles using validations with the format action defined in the initial configuration.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet with conditional formatting
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [85, "Excellent", 95],
            [72, "Good", 78],
            [58, "Average", 65],
            [45, "Poor", 52],
            [92, "Outstanding", 88],
            [67, "Good", 71],
            [34, "Poor", 41],
            [88, "Excellent", 91]
        ],
        columns: [
            { title: "Score A", width: 100 },
            { title: "Grade", width: 120 },
            { title: "Score B", width: 100 }
        ],
        minDimensions: [3, 10],
        worksheetName: "Conditional",
    }],
    validations: [
        // High scores (>=80) - Green background
        {
            range: 'Conditional!A:A',
            action: "format",
            criteria: ">=",
            type: "number",
            value: [80],
            format: { 
                "background-color": "#4CAF50", 
                "color": "#ffffff", 
                "font-weight": "bold" 
            }
        },
        // Medium scores (60-79) - Orange background
        {
            range: 'Conditional!A:A',
            action: "format",
            criteria: "between",
            type: "number",
            value: [60, 79],
            format: { 
                "background-color": "#FF9800", 
                "color": "#ffffff" 
            }
        },
        // Low scores (<60) - Red background
        {
            range: 'Conditional!A:A',
            action: "format",
            criteria: "<",
            type: "number",
            value: [60],
            format: { 
                "background-color": "#F44336", 
                "color": "#ffffff", 
                "font-style": "italic" 
            }
        },
        // Excellent grades formatting
        {
            range: 'Conditional!B:B',
            action: "format",
            criteria: "contains",
            type: "text",
            value: ["Excellent"],
            format: { 
                "background-color": "#2196F3", 
                "color": "#ffffff",
                "font-weight": "bold"
            }
        },
        // Score B column - gradient for high scores
        {
            range: 'Conditional!C:C',
            action: "format",
            criteria: ">=",
            type: "number",
            value: [85],
            format: { 
                "background": "linear-gradient(45deg, #9C27B0, #E91E63)",
                "color": "#ffffff",
                "font-weight": "bold"
            }
        }
    ]
});
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [
        [85, "Excellent", 95],
        [72, "Good", 78],
        [58, "Average", 65],
        [45, "Poor", 52],
        [92, "Outstanding", 88],
        [67, "Good", 71],
        [34, "Poor", 41],
        [88, "Excellent", 91]
    ];
    // Columns configuration
    const columns = [
        { title: "Score A", width: 100 },
        { title: "Grade", width: 120 },
        { title: "Score B", width: 100 }
    ];
    // Conditional formatting validations
    const validations = [
        // High scores (>=80) - Green background
        {
            range: 'Conditional!A:A',
            action: "format",
            criteria: ">=",
            type: "number",
            value: [80],
            format: { 
                "background-color": "#4CAF50", 
                "color": "#ffffff", 
                "font-weight": "bold" 
            }
        },
        // Medium scores (60-79) - Orange background
        {
            range: 'Conditional!A:A',
            action: "format",
            criteria: "between",
            type: "number",
            value: [60, 79],
            format: { 
                "background-color": "#FF9800", 
                "color": "#ffffff" 
            }
        },
        // Low scores (<60) - Red background
        {
            range: 'Conditional!A:A',
            action: "format",
            criteria: "<",
            type: "number",
            value: [60],
            format: { 
                "background-color": "#F44336", 
                "color": "#ffffff", 
                "font-style": "italic" 
            }
        },
        // Excellent grades formatting
        {
            range: 'Conditional!B:B',
            action: "format",
            criteria: "contains",
            type: "text",
            value: ["Excellent"],
            format: { 
                "background-color": "#2196F3", 
                "color": "#ffffff",
                "font-weight": "bold"
            }
        },
        // Score B column - gradient for high scores
        {
            range: 'Conditional!C:C',
            action: "format",
            criteria: ">=",
            type: "number",
            value: [85],
            format: { 
                "background": "linear-gradient(45deg, #9C27B0, #E91E63)",
                "color": "#ffffff",
                "font-weight": "bold"
            }
        }
    ];

    // Render component
    return (
        <>
            <Spreadsheet ref={spreadsheet} validations={validations}>
                <Worksheet data={data} columns={columns} minDimensions={[3, 10]} worksheetName="Conditional" />
            </Spreadsheet>
        </>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :validations="validations">
        <Worksheet :data="data" :columns="columns" :minDimensions="[3, 10]" worksheetName="Conditional" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');


export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [
            [85, "Excellent", 95],
            [72, "Good", 78],
            [58, "Average", 65],
            [45, "Poor", 52],
            [92, "Outstanding", 88],
            [67, "Good", 71],
            [34, "Poor", 41],
            [88, "Excellent", 91]
        ];
        // Columns configuration
        const columns = [
            { title: "Score A", width: 100 },
            { title: "Grade", width: 120 },
            { title: "Score B", width: 100 }
        ];
        // Conditional formatting validations
        const validations = [
            // High scores (>=80) - Green background
            {
                range: 'Conditional!A:A',
                action: "format",
                criteria: ">=",
                type: "number",
                value: [80],
                format: { 
                    "background-color": "#4CAF50", 
                    "color": "#ffffff", 
                    "font-weight": "bold" 
                }
            },
            // Medium scores (60-79) - Orange background
            {
                range: 'Conditional!A:A',
                action: "format",
                criteria: "between",
                type: "number",
                value: [60, 79],
                format: { 
                    "background-color": "#FF9800", 
                    "color": "#ffffff" 
                }
            },
            // Low scores (<60) - Red background
            {
                range: 'Conditional!A:A',
                action: "format",
                criteria: "<",
                type: "number",
                value: [60],
                format: { 
                    "background-color": "#F44336", 
                    "color": "#ffffff", 
                    "font-style": "italic" 
                }
            },
            // Excellent grades formatting
            {
                range: 'Conditional!B:B',
                action: "format",
                criteria: "contains",
                type: "text",
                value: ["Excellent"],
                format: { 
                    "background-color": "#2196F3", 
                    "color": "#ffffff",
                    "font-weight": "bold"
                }
            },
            // Score B column - gradient for high scores
            {
                range: 'Conditional!C:C',
                action: "format",
                criteria: ">=",
                type: "number",
                value: [85],
                format: { 
                    "background": "linear-gradient(45deg, #9C27B0, #E91E63)",
                    "color": "#ffffff",
                    "font-weight": "bold"
                }
            }
        ];

        return {
            data,
            columns,
            validations
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet with conditional formatting
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    [85, "Excellent", 95],
                    [72, "Good", 78],
                    [58, "Average", 65],
                    [45, "Poor", 52],
                    [92, "Outstanding", 88],
                    [67, "Good", 71],
                    [34, "Poor", 41],
                    [88, "Excellent", 91]
                ],
                columns: [
                    { title: "Score A", width: 100 },
                    { title: "Grade", width: 120 },
                    { title: "Score B", width: 100 }
                ],
                minDimensions: [3, 10],
                worksheetName: "Conditional"
            }],
            validations: [
                // High scores (>=80) - Green background
                {
                    range: 'Conditional!A:A',
                    action: "format",
                    criteria: ">=",
                    type: "number",
                    value: [80],
                    format: { 
                        "background-color": "#4CAF50", 
                        "color": "#ffffff", 
                        "font-weight": "bold" 
                    }
                },
                // Medium scores (60-79) - Orange background
                {
                    range: 'Conditional!A:A',
                    action: "format",
                    criteria: "between",
                    type: "number",
                    value: [60, 79],
                    format: { 
                        "background-color": "#FF9800", 
                        "color": "#ffffff" 
                    }
                },
                // Low scores (<60) - Red background
                {
                    range: 'Conditional!A:A',
                    action: "format",
                    criteria: "<",
                    type: "number",
                    value: [60],
                    format: { 
                        "background-color": "#F44336", 
                        "color": "#ffffff", 
                        "font-style": "italic" 
                    }
                },
                // Excellent grades formatting
                {
                    range: 'Conditional!B:B',
                    action: "format",
                    criteria: "contains",
                    type: "text",
                    value: ["Excellent"],
                    format: { 
                        "background-color": "#2196F3", 
                        "color": "#ffffff",
                        "font-weight": "bold"
                    }
                },
                // Score B column - gradient for high scores
                {
                    range: 'Conditional!C:C',
                    action: "format",
                    criteria: ">=",
                    type: "number",
                    value: [85],
                    format: { 
                        "background": "linear-gradient(45deg, #9C27B0, #E91E63)",
                        "color": "#ffffff",
                        "font-weight": "bold"
                    }
                }
            ]
        });
    }
}

Error Checking with Validations

This example demonstrates how to check for validation errors in the spreadsheet using the hasErrors method with a simple email validation rule.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div><br><br>

<input type="button" value="Check for errors" id="checkErrors">

<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet with validation rules
const grid = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ["[email protected]"],
            ["[email protected]"],
            ["invalid-email"],
            ["[email protected]"],
            ["[email protected]"]
        ],
        columns: [
            { title: "Email Address", width: 300 }
        ],
        minDimensions: [1, 6],
        worksheetName: "Emails",
    }],
    validations: [{
        range: 'Emails!A:A',
        action: "warning",
        criteria: "valid email",
        type: "text",
        text: "Please enter a valid email address"
    }]
});

const checkForErrors = function() {
    const hasErrors = grid[0].hasErrors();
    
    if (hasErrors) {
        alert("✗ The spreadsheet contains validation errors!");
    } else {
        alert("✓ No validation errors found!");
    }
}

document.getElementById("checkErrors").onclick = checkForErrors;
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
    const spreadsheet = useRef();
    
    const data = [
        ["[email protected]"],
        ["[email protected]"],
        ["invalid-email"],
        ["[email protected]"],
        ["[email protected]"]
    ];
    
    const columns = [
        { title: "Email Address", width: 300 }
    ];
    
    const validations = [{
        range: 'Emails!A:A',
        action: "warning",
        criteria: "valid email",
        type: "text",
        text: "Please enter a valid email address"
    }];

    const checkForErrors = () => {
        const hasErrors = spreadsheet.current[0].hasErrors();
        
        if (hasErrors) {
            alert("✗ The spreadsheet contains validation errors!");
        } else {
            alert("✓ No validation errors found!");
        }
    };

    return (
        <>
            <Spreadsheet ref={spreadsheet} validations={validations}>
                <Worksheet data={data} columns={columns} minDimensions={[1, 6]} worksheetName="Emails" />
            </Spreadsheet>
            <input type="button" value="Check for errors" onClick={checkForErrors}/>
        </>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :validations="validations">
        <Worksheet :data="data" :columns="columns" :minDimensions="[1, 6]" worksheetName="Emails" />
    </Spreadsheet>
    <input type="button" value="Check for errors" @click="checkForErrors" />
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    methods: {
        checkForErrors() {
            const hasErrors = this.$refs.spreadsheet.current[0].hasErrors();
            
            if (hasErrors) {
                alert("✗ The spreadsheet contains validation errors!");
            } else {
                alert("✓ No validation errors found!");
            }
        }
    },
    data() {
        const data = [
            ["[email protected]"],
            ["[email protected]"],
            ["invalid-email"],
            ["[email protected]"],
            ["[email protected]"]
        ];
        
        const columns = [
            { title: "Email Address", width: 300 }
        ];
        
        const validations = [{
            range: 'Emails!A:A',
            action: "warning",
            criteria: "valid email",
            type: "text",
            text: "Please enter a valid email address"
        }];

        return {
            data,
            columns,
            validations
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

@Component({
    standalone: true,
    selector: "app-root",
    template: `
        <div #spreadsheet></div>
        <input type="button" value="Check for errors" (click)="checkForErrors()" />`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    ["[email protected]"],
                    ["[email protected]"],
                    ["invalid-email"],
                    ["[email protected]"],
                    ["[email protected]"]
                ],
                columns: [
                    { title: "Email Address", width: 300 }
                ],
                minDimensions: [1, 6],
                worksheetName: "Emails"
            }],
            validations: [{
                range: 'Emails!A:A',
                action: "warning",
                criteria: "valid email",
                type: "text",
                text: "Please enter a valid email address"
            }]
        });
    }

    checkForErrors() {
        const hasErrors = this.worksheets[0].hasErrors();
        
        if (hasErrors) {
            alert("✗ The spreadsheet contains validation errors!");
        } else {
            alert("✓ No validation errors found!");
        }
    }
}

Custom Validation

The following example demonstrates how to create a custom validation that checks whether a cell value is a formula.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Declare the validation
jspreadsheet.setValidationHandler('isFormula', function(value, options) {
    // Get the raw value of the cell (this.w is the worksheet instance)
    let raw = this.w.getValueFromCoords(this.x, this.y);
    // Validate if it is a formula
    return raw && typeof raw === 'string' && raw[0] === '=';
});

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ["B1*2"],
            ["=B2*2"],
            ["B3*2"],
            ["=B4*2"],
            ["B5*2"]
        ],
        minDimensions: [6, 6]
    }],
    validations: [{
        range: 'Custom!A1:A6',
        action: "warning",
        text: "This is not a formula", // Handler should be created
        type: "isFormula",
    }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet.setValidationHandler('isFormula', function(value, options) {
    // Get the raw value of the cell (this.w is the worksheet instance)
    let raw = this.w.getValueFromCoords(this.x, this.y);
    // Validate if it is a formula
    return raw && typeof raw === 'string' && raw[0] === '=';
});

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [
        ["B1*2"],
        ["=B2*2"],
        ["B3*2"],
        ["=B4*2"],
        ["B5*2"]
    ];
    // Validations
    const validations = [{
        range: 'Custom!A1:A6',
        action: "warning",
        text: "This is not a formula",
        type: "isFormula", // Handler should be created
    }];

    // Render component
    return (
        <>
            <Spreadsheet ref={spreadsheet} validations={validations}>
                <Worksheet data={data} minDimensions={[6,6]} />
            </Spreadsheet>
        </>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :validations="validations">
        <Worksheet :data="data" :minDimensions="[6, 6]" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet.setValidationHandler('isFormula', function(value, options) {
  // Get the raw value of the cell (this.w is the worksheet instance)
  let raw = this.w.getValueFromCoords(this.x, this.y);
  // Validate if it is a formula
  return raw && typeof raw === 'string' && raw[0] === '=';
});

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [
            ["B1*2"],
            ["=B2*2"],
            ["B3*2"],
            ["=B4*2"],
            ["B5*2"]
        ];

        // Validations
        const validations = [{
            range: 'Custom!A1:A6',
            action: "warning",
            text: "This is not a formula",
            type: "isFormula", // Handler should be created
        }];

        return {
            data,
            validations,
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import jSuites from "jsuites";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Define the custom validation handler
jspreadsheet.setValidationHandler('isFormula', function(value, options) {
  // Get the raw value of the cell (this.w is the worksheet instance)
  let raw = this.w.getValueFromCoords(this.x, this.y);
  // Validate if it is a formula
  return raw && typeof raw === 'string' && raw[0] === '=';
});

// Create component
@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`,
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    ["B1*2"],
                    ["=B2*2"],
                    ["B3*2"],
                    ["=B4*2"],
                    ["B5*2"]
                ],
                minDimensions: [6, 6],
                worksheetName: "Custom"
            }],
            validations: [{
                range: 'Custom!A1:A6',
                action: "warning",
                text: "This is not a formula",
                type: "isFormula", // Handler should be created
            }]
        });
    }
}

Dynamic List Validation

This example demonstrates how to use dynamic list validation where the validation list is based on values from another column range.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create the spreadsheet with dynamic list validation
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ["", "Apple"],
            ["", "Orange"],
            ["", "Banana"],
            ["", "Grape"],
            ["", "Mango"],
            ["", ""],
            ["", ""]
        ],
        columns: [
            { title: "Select Fruit", width: 150 },
            { title: "Available Fruits", width: 150 }
        ],
        minDimensions: [2, 10],
        worksheetName: "DynamicList"
    }],
    validations: [{
        range: 'DynamicList!A:A',
        action: "warning",
        type: "list",
        value: ['DynamicList!B:B'],
        text: "Please select a value from the available fruits list",
        dropdown: true,
    }]
});
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [
        ["", "Apple"],
        ["", "Orange"],
        ["", "Banana"],
        ["", "Grape"],
        ["", "Mango"],
        ["", ""],
        ["", ""]
    ];
    // Columns configuration
    const columns = [
        { title: "Select Fruit", width: 150 },
        { title: "Available Fruits", width: 150 }
    ];
    // Dynamic list validation
    const validations = [{
        range: 'DynamicList!A:A',
        action: "warning",
        type: "list",
        value: ['DynamicList!B:B'],
        text: "Please select a value from the available fruits list",
        dropdown: true,
    }];

    // Render component
    return (
        <>
            <Spreadsheet ref={spreadsheet} validations={validations}>
                <Worksheet data={data} columns={columns} minDimensions={[2, 10]} worksheetName="DynamicList" />
            </Spreadsheet>
        </>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :validations="validations">
        <Worksheet :data="data" :columns="columns" :minDimensions="[2, 10]" worksheetName="DynamicList" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [
            ["", "Apple"],
            ["", "Orange"],
            ["", "Banana"],
            ["", "Grape"],
            ["", "Mango"],
            ["", ""],
            ["", ""]
        ];
        // Columns configuration
        const columns = [
            { title: "Select Fruit", width: 150 },
            { title: "Available Fruits", width: 150 }
        ];
        // Dynamic list validation
        const validations = [{
            range: 'DynamicList!A:A',
            action: "warning",
            type: "list",
            value: ['DynamicList!B:B'],
            text: "Please select a value from the available fruits list",
            dropdown: true,
        }];

        return {
            data,
            columns,
            validations
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2ZkMWRlY2Q2YzE4OGVlZGQ3Y2JlNDFlMDNiNWViYWRkNTU2NGM1NGZlYjgwMjZmYmYwODMyZWMxY2Y2ZDE4NGM0NGZmZjAxYWE1Y2VmNzA2OTE0MjAyYjY3ZDQ2OGQ3ZmVmMjgzNWQ2NGJhYmU0YWQ3NGIzMDcyNjkzOWYzNGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNREE1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet with dynamic list validation
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
                    ["", "Apple"],
                    ["", "Orange"],
                    ["", "Banana"],
                    ["", "Grape"],
                    ["", "Mango"],
                    ["", ""],
                    ["", ""]
                ],
                columns: [
                    { title: "Select Fruit", width: 150 },
                    { title: "Available Fruits", width: 150 }
                ],
                minDimensions: [2, 10],
                worksheetName: "DynamicList"
            }],
            validations: [{
                range: 'DynamicList!A:A',
                action: "warning",
                type: "list",
                value: ['DynamicList!B:B'],
                text: "Please select a value from the available fruits list",
                dropdown: true,
            }]
        });
    }
}

More Examples

React Data Grid Validations

• Spreadsheet with date validations

Differences from Version 11

• The return value of hasErrors has changed:
  • It returns either a boolean value or an array with the coordinates of the cells with errors.
• The loadValidations method no longer accepts the includeIndexes argument.