ExcelScript.DataValidationRule interface

Package:

ExcelScript

A data validation rule contains different types of data validation. You can only use one of them at a time according the ExcelScript.DataValidationType.

Properties

custom	Custom data validation criteria.
date	Date data validation criteria.
decimal	Decimal data validation criteria.
list	List data validation criteria.
textLength	Text length data validation criteria.
time	Time data validation criteria.
wholeNumber	Whole number data validation criteria.

Property Details

custom

Custom data validation criteria.

custom?: CustomDataValidation;

Property Value

ExcelScript.CustomDataValidation

date

Date data validation criteria.

date?: DateTimeDataValidation;

Property Value

ExcelScript.DateTimeDataValidation

decimal

Decimal data validation criteria.

decimal?: BasicDataValidation;

Property Value

ExcelScript.BasicDataValidation

list

List data validation criteria.

list?: ListDataValidation;

Property Value

ExcelScript.ListDataValidation

Examples

/**
 * This script creates a dropdown selection list for a cell.
 * It uses the existing values of the selected range as the choices for the list.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the values for data validation.
    const selectedRange = workbook.getSelectedRange();
    const rangeValues = selectedRange.getValues();

    // Convert the values into a comma-delimited string.
    let dataValidationListString = "";
    rangeValues.forEach((rangeValueRow) => {
        rangeValueRow.forEach((value) => {
            dataValidationListString += value + ",";
        });
    });

    // Clear the old range.
    selectedRange.clear(ExcelScript.ClearApplyTo.contents);

    // Apply the data validation to the first cell in the selected range.
    const targetCell = selectedRange.getCell(0, 0);
    const dataValidation = targetCell.getDataValidation();

    // Set the content of the dropdown list.
    let validationCriteria : ExcelScript.ListDataValidation = {
        inCellDropDown: true,
        source: dataValidationListString
    };
    let validationRule: ExcelScript.DataValidationRule = {
        list: validationCriteria
    };
    dataValidation.setRule(validationRule);
}

textLength

Text length data validation criteria.

textLength?: BasicDataValidation;

Property Value

ExcelScript.BasicDataValidation

time

Time data validation criteria.

time?: DateTimeDataValidation;

Property Value

ExcelScript.DateTimeDataValidation

wholeNumber

Whole number data validation criteria.

wholeNumber?: BasicDataValidation;

Property Value

ExcelScript.BasicDataValidation

Examples

/**
 * This script creates a data validation rule for the range B1:B5.
 * All values in that range must be a positive number.
 * Attempts to enter other values are blocked and an error message appears.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range B1:B5 in the active worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const positiveNumberOnlyCells = currentSheet.getRange("B1:B5");

  // Create a data validation rule to only allow positive numbers.
  const positiveNumberValidation: ExcelScript.BasicDataValidation = {
    formula1: "0",
    operator: ExcelScript.DataValidationOperator.greaterThan
  };
  const positiveNumberOnlyRule: ExcelScript.DataValidationRule = {
    wholeNumber: positiveNumberValidation
  };

  // Set the rule on the range.
  const rangeDataValidation = positiveNumberOnlyCells.getDataValidation();
  rangeDataValidation.setRule(positiveNumberOnlyRule);

  // Create an alert to appear when data other than positive numbers are entered.
  const positiveNumberOnlyAlert: ExcelScript.DataValidationErrorAlert = {
    message: "Positive numbers only",
    showAlert: true,
    style: ExcelScript.DataValidationAlertStyle.stop,
    title: "Invalid data"
  };
  rangeDataValidation.setErrorAlert(positiveNumberOnlyAlert);
}