Excel.DataValidationRule interface

Package:

excel

Warning

This API is now deprecated.

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

Warning

This API is now deprecated.

Custom data validation criteria.

custom?: Excel.CustomDataValidation;

Property Value

Excel.CustomDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

date

Warning

This API is now deprecated.

Date data validation criteria.

date?: Excel.DateTimeDataValidation;

Property Value

Excel.DateTimeDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

decimal

Warning

This API is now deprecated.

Decimal data validation criteria.

decimal?: Excel.BasicDataValidation;

Property Value

Excel.BasicDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

list

Warning

This API is now deprecated.

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

Warning

This API is now deprecated.

Text length data validation criteria.

textLength?: Excel.BasicDataValidation;

Property Value

Excel.BasicDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

time

Warning

This API is now deprecated.

Time data validation criteria.

time?: Excel.DateTimeDataValidation;

Property Value

Excel.DateTimeDataValidation

Remarks

[ API set: ExcelApi 1.8 ]

wholeNumber

Warning

This API is now deprecated.

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();
});