Spreadsheet Validations

Jspreadsheet validations enforce data entry rules, flagging cells for corrections and ensuring inputs match specific requirements. This boosts data integrity by minimizing errors and streamlining data management.

Documentation

Methods

Below are the methods available to manage validations in Jspreadsheet.

Method	Description
`getValidations`	Retrieves validation rules for a given index or null for all validations. `getValidations(index?: Number \| null) => Object`
`setValidations`	Create new or change exiting validation rules. `setValidations(validations: Object[]) => void`
`resetValidations`	Resets specific validation rules by index or reset all if no parameters are provided. `resetValidations(indexes?: Number[]) => void`
`loadValidations`	Retrieves all validation rules for a specific cell given its coordinates. `loadValidations(x: Number, y: Number) => Object[]`
`hasErrors`	Checks if a specific worksheet cell fails the validation rules. `hasErrors(col?: Number\|String, row?: Number) => Boolean`

Events

Events related to validations.

Method	Description
onvalidation	`onvalidation(worksheet: worksheetInstance, records: Validations[]) => void`

Validations[]

All available properties to define a validation

Property	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 or a whole column as Sheet1!E:E
type: string	'number' \| 'text' \| 'date' \| 'list' \| 'textLength' \| 'empty' \| 'notEmpty' or 'your-custom-valication'
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.

Custom Validations

You can create custom cell validations in the Jspreadsheet data grid by defining a method that returns true or false based on your validation logic inside this function. The keyword this refers to the cell object, giving you access to its coordinates (this. x, this.y) and the worksheet instance (this.w).

Note: Custom validations are not exported to XLSX when using the render extension.

Example

For instance, to validate that a cell value starts with an '=', you can define a validation method like isFormula, which checks the first character of the cell's value.

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

Now, you can declare your cell validations within the configuration of your Jspreadsheet data grid.

// 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", // This should be declared as jSuites.validations.isFormula
    }]
});

Examples

Basic Data Grid with Validations

Validations in Jspreadsheet ensure data integrity by enforcing rules either initially or programmatically.

	A	B
1	10	20
2	20	40
3	30	60
4	40	80
5	50	100
6

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/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('ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

// 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],
    }],
    validations: [{
        range: 'Sheet1!A1:A6',
        action: "warning",
        criteria: "between",
        type: "number",
        allowBlank: false,
        value: [10, 30],
    }]
});

const create = function() {
    grid[0].setValidations([{
        index: 1,
        value: {
            range: 'Sheet1!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 } from "@jspreadsheet/react";
import jspreadsheet from "jspreadsheet";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

const license = 'ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

// Example on how to create a new validation on React
const create = function(worksheet) {
    worksheet.setValidations([{
        index: 1,
        value: {
            range: 'Sheet1!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: 'Sheet1!A1:A6',
        action: "warning",
        criteria: "between",
        type: "number",
        allowBlank: false,
        value: [10, 30],
    }]

    // Render component
    return (
        <>
            <Spreadsheet ref={spreadsheet} license={license} validations={validations}>
                <Worksheet data={data} minDimensions={[6,6]} />
            </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" :license="license" :validations="validations">
        <Worksheet :data="data" />
    </Spreadsheet>
    <input type="button" value="Add new validation" @click="create" />
    <input type="button" value="Remove validation" @click="remove" />
</template>

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

const license = 'ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

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: 'Sheet1!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 validations 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: 'Sheet1!A1:A6',
            action: "warning",
            criteria: "between",
            type: "number",
            allowBlank: false,
            value: [10, 30],
        }];

        return {
            data,
            validations,
            license,
        };
    }
}
</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('ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

@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],
            }],
            validations: [{
                range: 'Sheet1!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: 'Sheet1!B1:B3',
                action: "format",
                criteria: "<",
                type: "number",
                value: [500],
                format: { color: '#ff0000' },
            }
        }]);
    }

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

Custom Validation

The follow example validate if a cell contains a formula

	A	B	C	D	E	F
1	A1*2
2	0
3	A3*2
4	0
5	A5*2
6

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/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('ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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

// Create the spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            ["A1*2"],
            ["=A2*2"],
            ["A3*2"],
            ["=A4*2"],
            ["A5*2"]
        ],
        minDimensions: [6, 6],
        worksheetName: 'Custom',
    }],
    validations: [{
        range: 'Custom!A1:A6',
        action: "warning",
        text: "This is not a formula",
        type: "isFormula", // This should be declared as jSuites.validations.isFormula
    }]
});
</script>
</html>

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

jspreadsheet.setLicense('ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [
        ["A1*2"],
        ["=A2*2"],
        ["A3*2"],
        ["=A4*2"],
        ["A5*2"]
    ];
    // Validations
    const validations = [{
        range: 'Custom!A1:A6',
        action: "warning",
        text: "This is not a formula",
        type: "isFormula", // This should be declared as jSuites.validations.isFormula
    }];

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

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

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

jspreadsheet.setLicense('ZjM2NGZmYWI1NDY4MGVjODhhNTZjOWJiMjY1OTMzY2YxZWI1ZTM3YTJhZDk3MDdjMjMwNWUyZjJiMzBmYzljNjNmYTMyZGZlMTVlMmEyODNlMTJiMTQyYmY5ZjAzZGI1ZDM1ODJmZDUxMTUxYWJjMmZiZjViYzdhMTg4Y2VkNTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRek56TXdPRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Data
        const data = [
            ["A1*2"],
            ["=A2*2"],
            ["A3*2"],
            ["=A4*2"],
            ["A5*2"]
        ];

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

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

// 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() {
        // Declare the validation
        jSuites.validations.isFormula = function(value, options) {
            // Get the raw value of the cell (this.w is the instance of the worksheet)
            let raw = this.w.getValueFromCoords(this.x, this.y);
            // Validate if is a formula
            return raw && typeof(raw) === 'string' && raw[0] === '=';
        }

        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            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", // This should be declared as jSuites.validations.isFormula
            }]
        });
    }
}

Validations Extension

The Validations Extension allows end-users to oversee cell validations within the data grid. It enables the creation of custom rules through an intuitive interface accessible via a toolbar icon.

Learn More

Click here to Learn More

More examples

React Data Grid Validations

Spreadsheet with date validations_

Edit this page on GitHub