ExcelScript.DataValidation interface

Package:

ExcelScript

Represents the data validation applied to the current range.

Methods

clear()	Clears the data validation from the current range.
getErrorAlert()	Error alert when user enters invalid data.
getIgnoreBlanks()	Specifies if data validation will be performed on blank cells. Default is true.
getInvalidCells()	Returns a RangeAreas object, comprising one or more rectangular ranges, with invalid cell values. If all cell values are valid, this method returns null.
getPrompt()	Prompt when users select a cell.
getRule()	Data validation rule that contains different type of data validation criteria.
getType()	Type of the data validation, see ExcelScript.DataValidationType for details.
getValid()	Represents if all cell values are valid according to the data validation rules. Returns true if all cell values are valid, or false if all cell values are invalid. Returns null if there are both valid and invalid cell values within the range.
setErrorAlert(errorAlert)	Error alert when user enters invalid data.
setIgnoreBlanks(ignoreBlanks)	Specifies if data validation will be performed on blank cells. Default is true.
setPrompt(prompt)	Prompt when users select a cell.
setRule(rule)	Data validation rule that contains different type of data validation criteria.

Method Details

clear()

Clears the data validation from the current range.

clear(): void;

Returns

void

getErrorAlert()

Error alert when user enters invalid data.

getErrorAlert(): DataValidationErrorAlert;

Returns

ExcelScript.DataValidationErrorAlert

getIgnoreBlanks()

Specifies if data validation will be performed on blank cells. Default is true.

getIgnoreBlanks(): boolean;

Returns

boolean

getInvalidCells()

Returns a RangeAreas object, comprising one or more rectangular ranges, with invalid cell values. If all cell values are valid, this method returns null.

getInvalidCells(): RangeAreas;

Returns

ExcelScript.RangeAreas

getPrompt()

Prompt when users select a cell.

getPrompt(): DataValidationPrompt;

Returns

ExcelScript.DataValidationPrompt

getRule()

Data validation rule that contains different type of data validation criteria.

getRule(): DataValidationRule;

Returns

ExcelScript.DataValidationRule

getType()

Type of the data validation, see ExcelScript.DataValidationType for details.

getType(): DataValidationType;

Returns

ExcelScript.DataValidationType

Examples

/**
 * This sample reads and logs the data validation type of the currently selected range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the currently selected range.
  let range = workbook.getSelectedRange();

  // Get the type (`DataValidationType`) of data validation applied to the range.
  let validationType = range.getDataValidation().getType();

  /*
   * Log the data validation type.
   * If the range has a single value, it logs that type.
   * If the range doesn't have data validation applied, it logs "None".
   * If the range has multiple different types of data validation, it logs "Inconsistent" or "MixedCriteria".
   */
  console.log(validationType.toString());
}

getValid()

Represents if all cell values are valid according to the data validation rules. Returns true if all cell values are valid, or false if all cell values are invalid. Returns null if there are both valid and invalid cell values within the range.

getValid(): boolean;

Returns

boolean

setErrorAlert(errorAlert)

Error alert when user enters invalid data.

setErrorAlert(errorAlert: DataValidationErrorAlert): void;

Parameters

errorAlert

ExcelScript.DataValidationErrorAlert

Returns

void

setIgnoreBlanks(ignoreBlanks)

Specifies if data validation will be performed on blank cells. Default is true.

setIgnoreBlanks(ignoreBlanks: boolean): void;

Parameters

ignoreBlanks

boolean

Returns

void

setPrompt(prompt)

Prompt when users select a cell.

setPrompt(prompt: DataValidationPrompt): void;

Parameters

prompt

ExcelScript.DataValidationPrompt

Returns

void

Examples

/**
 * This script creates a text prompt that's shown in C2:C8 when a user enters the cell.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the data validation object for C2:C8 in the current worksheet.
    let selectedSheet = workbook.getActiveWorksheet();
    let dataValidation = selectedSheet.getRange("C2:C8").getDataValidation();

    // Clear any previous validation to avoid conflicts.
    dataValidation.clear();

    // Create a prompt to remind users to only enter first names in this column.
    dataValidation.setPrompt({ 
        showPrompt: true, 
        title: "First names only", 
        message: "Only enter the first name of the employee, not the full name." 
    });
}    

setRule(rule)

Data validation rule that contains different type of data validation criteria.

setRule(rule: DataValidationRule): void;

Parameters

rule

ExcelScript.DataValidationRule

Returns

void

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