Excel.DataValidationRule interface

Package:

excel

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

Remarks

[ API set: ExcelApi 1.8 ]

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?: Excel.CustomDataValidation;

Property Value

Excel.CustomDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

date

Date data validation criteria.

date?: Excel.DateTimeDataValidation;

Property Value

Excel.DateTimeDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

decimal

Decimal data validation criteria.

decimal?: Excel.BasicDataValidation;

Property Value

Excel.BasicDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

list

List data validation criteria.

list?: Excel.ListDataValidation;

Property Value

Excel.ListDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Decision");
    const nameRange = 
        sheet.tables.getItem("NameOptionsTable").columns.getItem("Baby Name").getDataBodyRange();

    // When you are developing, it is a good practice to
    // clear the dataValidation object with each run of your code.
    nameRange.dataValidation.clear();

    const nameSourceRange = context.workbook.worksheets.getItem("Names").getRange("A1:A3");

    let approvedListRule = {
        list: {
            inCellDropDown: true,
            source: nameSourceRange
        }
    };
    nameRange.dataValidation.rule = approvedListRule;

    await context.sync();
});

textLength

Text length data validation criteria.

textLength?: Excel.BasicDataValidation;

Property Value

Excel.BasicDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

time

Time data validation criteria.

time?: Excel.DateTimeDataValidation;

Property Value

Excel.DateTimeDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

wholeNumber

Whole number data validation criteria.

wholeNumber?: Excel.BasicDataValidation;

Property Value

Excel.BasicDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Decision");
    const rankingRange = sheet.tables.getItem("NameOptionsTable").columns.getItem("Ranking").getDataBodyRange();

    // When you are developing, it is a good practice to
    // clear the dataValidation object with each run of your code.
    rankingRange.dataValidation.clear();

    let greaterThanZeroRule = {
        wholeNumber: {
            formula1: 0,
            operator: Excel.DataValidationOperator.greaterThan
        }
    };
    rankingRange.dataValidation.rule = greaterThanZeroRule;

    rankingRange.dataValidation.prompt = {
        message: "Please enter a positive number.",
        showPrompt: true,
        title: "Positive numbers only."
    };

    rankingRange.dataValidation.errorAlert = {
        message: "Sorry, only positive numbers are allowed",
        showAlert: true,
        style: "Stop",
        title: "Negative Number Entered"
    };

    await context.sync();
});