API Reference

FDataValidationBuilder

Builder for data validation rules. use FUniver univerAPI.newDataValidation() to create a new builder.

Access

Access through:

Example

TypeScript
// Set the data validation for cell A1 to require a value from B1:B10const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const fRange = fWorksheet.getRange('B1:B2')fRange.setValues([['Yes'], ['No']])const rule = univerAPI  .newDataValidation()  .requireValueInRange(fRange)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a value from the list',  })  .build()const cell = fWorksheet.getRange('A1')cell.setDataValidation(rule)

Setup

Register @univerjs/sheets-data-validation or a preset that includes it. In plugin mode, import @univerjs/sheets-data-validation/facade. Additional methods below require their listed plugin packages. See Facade setup.

@univerjs/sheets-data-validation

FDataValidationBuilder.build

Builds an FDataValidation instance based on the _rule property of the current class

TypeScript
build(): FDataValidation

Returns

A new instance of the FDataValidation class

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number between 1 and 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberBetween(1, 10)  .setOptions({    allowBlank: true,    showErrorMessage: true,    error: 'Please enter a number between 1 and 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidation

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.copy

Creates a duplicate of the current DataValidationBuilder object

TypeScript
copy(): FDataValidationBuilder

Returns

A new instance of the DataValidationBuilder class

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number between 1 and 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const builder = univerAPI.newDataValidation().requireNumberBetween(1, 10).setOptions({  allowBlank: true,  showErrorMessage: true,  error: 'Please enter a number between 1 and 10',})fRange.setDataValidation(builder.build())// Copy the builder applied to the new range F1:G10const newRange = fWorksheet.getRange('F1:G10')const copyBuilder = builder.copy()newRange.setDataValidation(copyBuilder.build())

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.getAllowInvalid

Determines whether invalid data is allowed

TypeScript
getAllowInvalid(): boolean

Returns

True if invalid data is allowed, False otherwise

Examples

TypeScript
const builder = univerAPI.newDataValidation().requireNumberBetween(1, 10)console.log(builder.getAllowInvalid())

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.getCriteriaType

Gets the data validation type of the rule

TypeScript
getCriteriaType(): DataValidationType | string

Returns

The data validation type

Examples

TypeScript
const builder = univerAPI.newDataValidation()console.log(builder.getCriteriaType()) // custombuilder.requireNumberBetween(1, 10)console.log(builder.getCriteriaType()) // decimalbuilder.requireValueInList(['Yes', 'No'])console.log(builder.getCriteriaType()) // list

Types: DataValidationType

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.getCriteriaValues

Gets the values used for criteria evaluation

TypeScript
getCriteriaValues(): [string | undefined, string | undefined, string | undefined]

Returns

An array containing the operator, formula1, and formula2 values

Examples

TypeScript
const builder = univerAPI.newDataValidation().requireNumberBetween(1, 10)const [operator, formula1, formula2] = builder.getCriteriaValues()console.log(operator, formula1, formula2) // between 1 10builder.requireValueInList(['Yes', 'No'])console.log(builder.getCriteriaValues()) // undefined Yes,No undefined

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.getHelpText

Gets the help text information, which is used to provide users with guidance and support

TypeScript
getHelpText(): string | undefined

Returns

Returns the help text information. If there is no error message, it returns an undefined value

Examples

TypeScript
const builder = univerAPI.newDataValidation().setOptions({  showErrorMessage: true,  error: 'Please enter a valid value',})console.log(builder.getHelpText()) // 'Please enter a valid value'

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireCheckbox

Sets the data validation rule to require that the input is a boolean value; this value is rendered as a checkbox.

TypeScript
requireCheckbox(checkedValue?: string, uncheckedValue?: string): FDataValidationBuilder

Parameters

  • checkedValue — Optional. The value assigned to a checked box.
  • uncheckedValue — Optional. The value assigned to an unchecked box.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set the data validation for cell A1:A10 to require a checkbox with default 1 and 0 valuesconst fRange = fWorksheet.getRange('A1:A10')const rule = univerAPI.newDataValidation().requireCheckbox().build()fRange.setDataValidation(rule)// Set the data validation for cell B1:B10 to require a checkbox with 'Yes' and 'No' valuesconst fRange2 = fWorksheet.getRange('B1:B10')const rule2 = univerAPI.newDataValidation().requireCheckbox('Yes', 'No').build()fRange2.setDataValidation(rule2)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateAfter

Set the data validation type to DATE and configure the validation rules to be after a specific date.

TypeScript
requireDateAfter(date: Date): FDataValidationBuilder

Parameters

  • date — Required. The latest unacceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date after 2025-01-01const rule = univerAPI.newDataValidation().requireDateAfter(new Date('2025-01-01')).build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['invalid', 'invalid', 'invalid', 'valid']]

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateBefore

Set the data validation type to DATE and configure the validation rules to be before a specific date.

TypeScript
requireDateBefore(date: Date): FDataValidationBuilder

Parameters

  • date — Required. The earliest unacceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date before 2025-01-01const rule = univerAPI.newDataValidation().requireDateBefore(new Date('2025-01-01')).build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['valid', 'valid', 'invalid', 'invalid']]

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateBetween

Set the data validation type to DATE and configure the validation rules to be within a specific date range.

TypeScript
requireDateBetween(start: Date, end: Date): FDataValidationBuilder

Parameters

  • start — Required. The earliest acceptable date.
  • end — Required. The latest acceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date between 2024-06-01 and 2025-06-01const rule = univerAPI  .newDataValidation()  .requireDateBetween(new Date('2024-06-01'), new Date('2025-06-01'))  .build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['invalid', 'valid', 'valid', 'invalid']]

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateEqualTo

Set the data validation type to DATE and configure the validation rules to be equal to a specific date.

TypeScript
requireDateEqualTo(date: Date): FDataValidationBuilder

Parameters

  • date — Required. The sole acceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date equal to 2025-01-01const rule = univerAPI.newDataValidation().requireDateEqualTo(new Date('2025-01-01')).build()fRange.setDataValidation(rule)// Get the validation status of the cell A2const status = await fWorksheet.getRange('A2').getValidatorStatus()console.log(status?.[0]?.[0]) // 'valid'// Get the validation status of the cell B2const status2 = await fWorksheet.getRange('B2').getValidatorStatus()console.log(status2?.[0]?.[0]) // 'invalid'

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateNotBetween

Set the data validation type to DATE and configure the validation rules to be not within a specific date range.

TypeScript
requireDateNotBetween(start: Date, end: Date): FDataValidationBuilder

Parameters

  • start — Required. The earliest unacceptable date.
  • end — Required. The latest unacceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date not between 2024-06-01 and 2025-06-01const rule = univerAPI  .newDataValidation()  .requireDateNotBetween(new Date('2024-06-01'), new Date('2025-06-01'))  .build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['valid', 'invalid', 'invalid', 'valid']]

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateOnOrAfter

Set the data validation type to DATE and configure the validation rules to be on or after a specific date.

TypeScript
requireDateOnOrAfter(date: Date): FDataValidationBuilder

Parameters

  • date — Required. The earliest acceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date on or after 2025-01-01const rule = univerAPI.newDataValidation().requireDateOnOrAfter(new Date('2025-01-01')).build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['invalid', 'invalid', 'valid', 'valid']]

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireDateOnOrBefore

Set the data validation type to DATE and configure the validation rules to be on or before a specific date.

TypeScript
requireDateOnOrBefore(date: Date): FDataValidationBuilder

Parameters

  • date — Required. The latest acceptable date.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some date values in the range A1:B2const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  ['2024-01-01', '2024-12-31'],  ['2025-01-01', '2025-12-31'],])// Create a data validation rule that requires a date on or before 2025-01-01const rule = univerAPI.newDataValidation().requireDateOnOrBefore(new Date('2025-01-01')).build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['valid', 'valid', 'valid', 'invalid']]

Types: FDataValidationBuilder · Date

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireFormulaSatisfied

Sets the data validation rule to require that the given formula evaluates to true.

TypeScript
requireFormulaSatisfied(formula: string): FDataValidationBuilder

Parameters

  • formula — Required. The formula string that needs to be satisfied, formula result should be TRUE or FALSE, and references range will relative offset.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set some values in the range A1:B2 and C1:D2const cell = fWorksheet.getRange('A1:B2')cell.setValues([  [4, 3],  [2, 1],])const fRange = fWorksheet.getRange('C1:D2')fRange.setValues([  [1, 2],  [3, 4],])// Create a data validation rule that requires the formula '=A1>2' to be satisfiedconst rule = univerAPI  .newDataValidation()  .requireFormulaSatisfied('=A1>2')  .setOptions({    showErrorMessage: true,    error: 'Please enter a value equal to A1',  })  .build()fRange.setDataValidation(rule)// Get the validation status of the rangeconst status = await fRange.getValidatorStatus()console.log(status) // [['valid', 'valid', 'invalid', 'invalid']]

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberBetween

Sets the data validation rule to require a number that falls between, or is either of, two specified numbers.

TypeScript
requireNumberBetween(start: number, end: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • start — Required. The lowest acceptable value.
  • end — Required. The highest acceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number between 1 and 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberBetween(1, 10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number between 1 and 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberEqualTo

Sets the data validation rule to require a number equal to the given value.

TypeScript
requireNumberEqualTo(num: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • num — Required. The sole acceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number equal to 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberEqualTo(10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number equal to 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberGreaterThan

Sets the data validation rule to require a number greater than the given value.

TypeScript
requireNumberGreaterThan(num: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • num — Required. The highest unacceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number greater than 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberGreaterThan(10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number greater than 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberGreaterThanOrEqualTo

Sets the data validation rule to require a number greater than or equal to the given value.

TypeScript
requireNumberGreaterThanOrEqualTo(num: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • num — Required. The lowest acceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number greater than 10 or equal to 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberGreaterThanOrEqualTo(10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number greater than 10 or equal to 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberLessThan

Sets the data validation rule to require a number less than the given value.

TypeScript
requireNumberLessThan(num: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • num — Required. The lowest unacceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number less than 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberLessThan(10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number less than 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberLessThanOrEqualTo

Sets the data validation rule to require a number less than or equal to the given value.

TypeScript
requireNumberLessThanOrEqualTo(num: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • num — Required. The highest acceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number less than 10 or equal to 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberLessThanOrEqualTo(10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number less than 10 or equal to 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberNotBetween

Sets the data validation rule to require a number that does not fall between, and is neither of, two specified numbers.

TypeScript
requireNumberNotBetween(start: number, end: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • start — Required. The lowest unacceptable value.
  • end — Required. The highest unacceptable value.
  • isInteger — Optional. Optional parameter, indicating whether the number to be verified is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number not between 1 and 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberNotBetween(1, 10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number not between 1 and 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireNumberNotEqualTo

Sets the data validation rule to require a number not equal to the given value.

TypeScript
requireNumberNotEqualTo(num: number, isInteger?: boolean): FDataValidationBuilder

Parameters

  • num — Required. The sole unacceptable value.
  • isInteger — Optional. Indicates whether the required number is an integer.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires a number not equal to 10 for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireNumberNotEqualTo(10)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a number not equal to 10',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireValueInList

Sets a data validation rule that requires the user to enter a value from a list of specific values. The list can be displayed in a dropdown, and the user can choose multiple values according to the settings.

TypeScript
requireValueInList(values: string[], multiple?: boolean, showDropdown?: boolean): FDataValidationBuilder

Parameters

  • values — Required. An array of acceptable values.
  • multiple — Optional. Optional parameter indicating whether the user can select multiple values.
  • showDropdown — Optional. Optional parameter indicating whether to display the list in a dropdown.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires the user to enter a value from the list ['Yes', 'No'] for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireValueInList(['Yes', 'No'])  .setOptions({    allowBlank: true,    showErrorMessage: true,    error: 'Please enter a value from the list',  })  .build()fRange.setDataValidation(rule)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.requireValueInRange

Sets a data validation rule that requires the user to enter a value within a specific range. The range is defined by an FRange object, which contains the unit ID, sheet name, and cell range.

TypeScript
requireValueInRange(range: FRange, multiple?: boolean, showDropdown?: boolean): FDataValidationBuilder

Parameters

  • range — Required. An FRange object representing the range of values that the user can enter.
  • multiple — Optional. Optional parameter indicating whether the user can select multiple values.
  • showDropdown — Optional. Optional parameter indicating whether to display the list in a dropdown.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set the values in the range B1:B2const fRange = fWorksheet.getRange('B1:B2')fRange.setValues([['Yes'], ['No']])// Create a new data validation rule that requires the user to enter a value from the range B1:B2 for the range A1:A10const rule = univerAPI  .newDataValidation()  .requireValueInRange(fRange)  .setOptions({    allowBlank: false,    showErrorMessage: true,    error: 'Please enter a value from the list',  })  .build()const cell = fWorksheet.getRange('A1')cell.setDataValidation(rule)

Types: FDataValidationBuilder · FRange

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.setAllowBlank

Sets whether to allow blank values.

TypeScript
setAllowBlank(allowBlank: boolean): FDataValidationBuilder

Parameters

  • allowBlank — Required. Whether to allow blank values.

Returns

The current instance for method chaining.

Examples

TypeScript
// Assume current sheet is empty dataconst fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set the data validation for cell A1:B2 to allow blank valuesconst fRange = fWorksheet.getRange('A1:B2')const rule = univerAPI  .newDataValidation()  .requireValueInList(['Yes', 'No'])  .setAllowBlank(true)  .build()fRange.setDataValidation(rule)// Set the data validation for cell C1:D2 to not allow blank valuesconst fRange2 = fWorksheet.getRange('C1:D2')const rule2 = univerAPI  .newDataValidation()  .requireValueInList(['Yes', 'No'])  .setAllowBlank(false)  .build()fRange2.setDataValidation(rule2)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.setAllowInvalid

Sets whether to allow invalid data and configures the error style. If invalid data is not allowed, the error style will be set to STOP, indicating that data entry must stop upon encountering an error. If invalid data is allowed, the error style will be set to WARNING, indicating that a warning will be displayed when invalid data is entered, but data entry can continue.

TypeScript
setAllowInvalid(allowInvalidData: boolean): FDataValidationBuilder

Parameters

  • allowInvalidData — Required. Whether to allow invalid data.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Set the data validation for cell A1:B2 to allow invalid data, so A1:B2 will display a warning when invalid data is enteredconst fRange = fWorksheet.getRange('A1:B2')const rule = univerAPI  .newDataValidation()  .requireValueInList(['Yes', 'No'])  .setAllowInvalid(true)  .build()fRange.setDataValidation(rule)// Set the data validation for cell C1:D2 to not allow invalid data, so C1:D2 will stop data entry when invalid data is enteredconst fRange2 = fWorksheet.getRange('C1:D2')const rule2 = univerAPI  .newDataValidation()  .requireValueInList(['Yes', 'No'])  .setAllowInvalid(false)  .build()fRange2.setDataValidation(rule2)

Types: FDataValidationBuilder

Package: @univerjs/sheets-data-validation · Type definitions

FDataValidationBuilder.setOptions

Sets the options for the data validation rule.

TypeScript
setOptions(options: Partial<IDataValidationRuleOptions>): this

Parameters

  • options — Required. The options to set for the data validation rule.

Returns

The current instance for method chaining.

Examples

TypeScript
const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Create a new data validation rule that requires the user to enter a value from the list ['Yes', 'No'] for the range A1:B10const fRange = fWorksheet.getRange('A1:B10')const rule = univerAPI  .newDataValidation()  .requireValueInList(['Yes', 'No'])  .setOptions({    allowBlank: true,    showErrorMessage: true,    error: 'Please enter a value from the list',  })  .build()fRange.setDataValidation(rule)

Types: Partial · IDataValidationRuleOptions

Package: @univerjs/sheets-data-validation · Type definitions

How is this guide?

© 2026 DreamNum Co., Ltd.