Excel.DataValidationRule interface
A data validation rule contains different types of data validation. You can only use one of them at a time according the Excel.DataValidationType.
Properties
| custom | Custom data validation criteria. |
| date | Date data validation criteria. |
| decimal | Decimal data validation criteria. |
| list | List data validation criteria. |
| text |
Text length data validation criteria. |
| time | Time data validation criteria. |
| whole |
Whole number data validation criteria. |
Property Details
custom
Custom data validation criteria.
custom?: Excel.CustomDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyCustom(sheet: Excel.Worksheet) {
// Custom formula: Value in B8 must not duplicate any value already in B2:B7.
const customRule: Excel.CustomDataValidation = {
formula: "=COUNTIF($B$2:$B$7,B8)=0"
};
const rule: Excel.DataValidationRule = { custom: customRule };
sheet.getRange("B8").dataValidation.rule = rule;
}
date
Date data validation criteria.
date?: Excel.DateTimeDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyDate(sheet: Excel.Worksheet) {
// Date must be on or after January 1, 2000.
const basicRule: Excel.DateTimeDataValidation = {
formula1: "1/1/2000",
operator: Excel.DataValidationOperator.greaterThanOrEqualTo
};
const rule: Excel.DataValidationRule = { date: basicRule };
sheet.getRange("B5").dataValidation.rule = rule;
}
decimal
Decimal data validation criteria.
decimal?: Excel.BasicDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyDecimal(sheet: Excel.Worksheet) {
// Decimal values between 0 and 10 are allowed.
const basicRule: Excel.BasicDataValidation = {
formula1: 0,
formula2: 10,
operator: Excel.DataValidationOperator.between
};
const rule: Excel.DataValidationRule = { decimal: basicRule };
sheet.getRange("B3").dataValidation.rule = rule;
}
list
List data validation criteria.
list?: Excel.ListDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyList(sheet: Excel.Worksheet) {
// Value must be chosen from the predefined list.
const listRule: Excel.ListDataValidation = {
inCellDropDown: true,
source: "Apple,Banana,Cherry"
};
const rule: Excel.DataValidationRule = { list: listRule };
sheet.getRange("B4").dataValidation.rule = rule;
}
textLength
Text length data validation criteria.
textLength?: Excel.BasicDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyTextLength(sheet: Excel.Worksheet) {
// Text must be 10 characters or fewer.
const basicRule: Excel.BasicDataValidation = {
formula1: 10,
operator: Excel.DataValidationOperator.lessThanOrEqualTo
};
const rule: Excel.DataValidationRule = { textLength: basicRule };
sheet.getRange("B7").dataValidation.rule = rule;
}
time
Time data validation criteria.
time?: Excel.DateTimeDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyTime(sheet: Excel.Worksheet) {
// Time must be between 9:00 AM and 5:00 PM.
const basicRule: Excel.DateTimeDataValidation = {
formula1: "9:00 AM",
formula2: "5:00 PM",
operator: Excel.DataValidationOperator.between
};
const rule: Excel.DataValidationRule = { time: basicRule };
sheet.getRange("B6").dataValidation.rule = rule;
}
wholeNumber
Whole number data validation criteria.
wholeNumber?: Excel.BasicDataValidation;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/22-data-validation/data-validation-types.yaml
function applyWholeNumber(sheet: Excel.Worksheet) {
// Only integers greater than 0 are allowed.
const basicRule: Excel.BasicDataValidation = {
formula1: 0,
operator: Excel.DataValidationOperator.greaterThan
};
const rule: Excel.DataValidationRule = { wholeNumber: basicRule };
sheet.getRange("B2").dataValidation.rule = rule;
}