Data validation

Validate and show errors to users if they enter invalid data

Validation rules can be added as part of your cellData. Below is an example of using getDataValidation with CanvasGrid to dynamically validate cells.

import React, { useState } from "react";
import { SpreadsheetProvider, CanvasGrid } from "@rowsncolumns/spreadsheet";
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";

const MySpreadsheet = () => {
  const [dataValidations, onChangeDataValidations] = useState([
    {
      id: "validation1",
      condition: {
        type: "ONE_OF_LIST",
        values: [
          { userEnteredValue: "Singapore" },
          { userEnteredValue: "USA" },
          { userEnteredValue: "UK" },
        ],
      },
    },
  ]);

  const { getDataValidation } = useSpreadsheetState({
    dataValidations,
    onChangeDataValidations,
  });

  return (
    <CanvasGrid
      getCellData={(sheetId, rowIndex, columnIndex) => {
        if (rowIndex === 2 && columnIndex === 2) {
          return {
            dataValidation: "validation1", // Reference the validation ID
          };
        }
      }}
      getDataValidation={getDataValidation} // Pass the getDataValidation function to CanvasGrid
    />
  );
};

const App = () => (
  <SpreadsheetProvider>
    <MySpreadsheet />
  </SpreadsheetProvider>
);

The following validation types are supported:

// Supported condition types
export const CONDITION_TYPES = [
  "NUMBER_GREATER",
  "NUMBER_GREATER_THAN_EQ",
  "NUMBER_LESS",
  "NUMBER_LESS_THAN_EQ",
  "NUMBER_EQ",
  "NUMBER_NOT_EQ",
  "NUMBER_BETWEEN",
  "NUMBER_NOT_BETWEEN",
  "TEXT_CONTAINS",
  "TEXT_NOT_CONTAINS",
  "TEXT_STARTS_WITH",
  "TEXT_ENDS_WITH",
  "TEXT_EQ",
  "TEXT_IS_EMAIL",
  "TEXT_IS_URL",
  "DATE_EQ",
  "DATE_BEFORE",
  "DATE_AFTER",
  "DATE_ON_OR_BEFORE",
  "DATE_ON_OR_AFTER",
  "DATE_BETWEEN",
  "DATE_NOT_BETWEEN",
  "DATE_IS_VALID",
  "ONE_OF_RANGE",
  "ONE_OF_LIST",
  "BLANK",
  "NOT_BLANK",
  "CUSTOM_FORMULA",
  "BOOLEAN",
  "TEXT_NOT_EQ",
  "DATE_NOT_EQ",
] as const;

Data Validation Editor