ExcelScript.DataValidationRule interface

パッケージ:

ExcelScript

データ検証ルールには、さまざまな種類のデータ検証が含まれています。 一度に使用できるのは、 ExcelScript.DataValidationTypeに従って 1 つだけです。

プロパティ

custom	データ検証条件のカスタム数式。
date	日付のデータ検証条件。
decimal	10 進数のデータ検証条件。
list	リストのデータ検証条件。
textLength	テキスト長データの検証条件。
time	時刻のデータ検証条件。
wholeNumber	整数データの検証条件。

プロパティの詳細

custom

データ検証条件のカスタム数式。

custom?: CustomDataValidation;

プロパティ値

ExcelScript.CustomDataValidation

例

/**
 * This script adds data validation to a range.
 * The validation prevents duplicate entries within that range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range "B2:B20".
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getRange("B2:B20");

  // Set data validation on the range to prevent duplicate, non-blank entries.
  const dataValidation = range.getDataValidation();
  dataValidation.setIgnoreBlanks(true);
  const duplicateRule : ExcelScript.CustomDataValidation = { 
    formula: "=COUNTIF($B$2:$B$20, B2)=1"
  };
  dataValidation.setRule({
    custom: duplicateRule
  });
}

date

日付のデータ検証条件。

date?: DateTimeDataValidation;

プロパティ値

ExcelScript.DateTimeDataValidation

decimal

10 進数のデータ検証条件。

decimal?: BasicDataValidation;

プロパティ値

ExcelScript.BasicDataValidation

list

リストのデータ検証条件。

list?: ListDataValidation;

プロパティ値

ExcelScript.ListDataValidation

例

/**
 * This script creates a dropdown selection list for a cell.
 * It uses the existing values of the selected range as the choices for the list.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the values for data validation.
    const selectedRange = workbook.getSelectedRange();
    const rangeValues = selectedRange.getValues();

    // Convert the values into a comma-delimited string.
    let dataValidationListString = "";
    rangeValues.forEach((rangeValueRow) => {
        rangeValueRow.forEach((value) => {
            dataValidationListString += value + ",";
        });
    });

    // Clear the old range.
    selectedRange.clear(ExcelScript.ClearApplyTo.contents);

    // Apply the data validation to the first cell in the selected range.
    const targetCell = selectedRange.getCell(0, 0);
    const dataValidation = targetCell.getDataValidation();

    // Set the content of the dropdown list.
    let validationCriteria : ExcelScript.ListDataValidation = {
        inCellDropDown: true,
        source: dataValidationListString
    };
    let validationRule: ExcelScript.DataValidationRule = {
        list: validationCriteria
    };
    dataValidation.setRule(validationRule);
}

textLength

テキスト長データの検証条件。

textLength?: BasicDataValidation;

プロパティ値

ExcelScript.BasicDataValidation

time

時刻のデータ検証条件。

time?: DateTimeDataValidation;

プロパティ値

ExcelScript.DateTimeDataValidation

wholeNumber

整数データの検証条件。

wholeNumber?: BasicDataValidation;

プロパティ値

ExcelScript.BasicDataValidation

例

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