ExcelScript.DataValidation interface

Clears the data validation from the current range.

clear(): void;

Returns

Error alert when user enters invalid data.

getErrorAlert(): DataValidationErrorAlert;

Returns

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

getIgnoreBlanks(): boolean;

Returns

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

getInvalidCells(): RangeAreas;

Returns

Prompt when users select a cell.

getPrompt(): DataValidationPrompt;

Returns

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

getRule(): DataValidationRule;

Returns

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

getType(): DataValidationType;

Returns

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

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

Error alert when user enters invalid data.

setErrorAlert(errorAlert: DataValidationErrorAlert): void;

Parameters

Returns

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

setIgnoreBlanks(ignoreBlanks: boolean): void;

Parameters

Returns

Prompt when users select a cell.

setPrompt(prompt: DataValidationPrompt): void;

Parameters

Returns

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

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

setRule(rule: DataValidationRule): void;

Parameters

Returns

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