Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder below:

Class DataValidationBuilder | Apps Script

Stay organized with collections Save and categorize content based on your preferences.

Builder for data validation rules.

// Set the data validation for cell A1 to require a value from B1:B10.
const cell = SpreadsheetApp.getActive().getRange('A1');
const range = SpreadsheetApp.getActive().getRange('B1:B10');
const rule =
    SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);

Constructs a data validation rule from the settings applied to the builder.

DataValidation — a representation of the data validation rule

Creates a builder for a data validation rule based on this rule's settings.

// Change existing data validation rules that require a date in 2013 to require
// a date in 2014.
const oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
const newDates = [new Date('1/1/2014'), new Date('12/31/2014')];
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
const rules = range.getDataValidations();

for (let i = 0; i < rules.length; i++) {
  for (let j = 0; j < rules[i].length; j++) {
    const rule = rules[i][j];

    if (rule != null) {
      const criteria = rule.getCriteriaType();
      const args = rule.getCriteriaValues();

      if (criteria === SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN &&
          args[0].getTime() === oldDates[0].getTime() &&
          args[1].getTime() === oldDates[1].getTime()) {
        // Create a builder from the existing rule, then change the dates.
        rules[i][j] = rule.copy().withCriteria(criteria, newDates).build();
      }
    }
  }
}
range.setDataValidations(rules);

DataValidationBuilder — a builder based on this rule's settings

Returns true if the rule shows a warning when input fails data validation, or false if it rejects the input entirely. The default for new data validation rules is true.

Boolean — true if the rule allows input that fails data validation; false if not

Gets the rule's criteria type as defined in the DataValidationCriteria enum. To get the arguments for the criteria, use getCriteriaValues(). To use these values to create or modify a data validation rule, see withCriteria(criteria, args).

// Log information about the data validation rule for cell A1.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = cell.getDataValidation();
if (rule != null) {
  const criteria = rule.getCriteriaType();
  const args = rule.getCriteriaValues();
  Logger.log('The data validation rule is %s %s', criteria, args);
} else {
  Logger.log('The cell does not have a data validation rule.');
}

DataValidationCriteria — the type of data validation criteria

Gets an array of arguments for the rule's criteria. To get the criteria type, use getCriteriaType(). To use these values to create or modify a data validation rule, see withCriteria(criteria, args).

// Log information about the data validation rule for cell A1.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = cell.getDataValidation();
if (rule != null) {
  const criteria = rule.getCriteriaType();
  const args = rule.getCriteriaValues();
  Logger.log('The data validation rule is %s %s', criteria, args);
} else {
  Logger.log('The cell does not have a data validation rule.');
}

Object[] — an array of arguments appropriate to the rule's criteria type; the number of arguments and their type match the corresponding require...() method of the DataValidationBuilder class

Gets the rule's help text, or null if no help text is set.

String — the rule's help text, or null if no help text is set

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

// Set the data validation for cell A1 to require a boolean value; the value is
// rendered as a checkbox.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is the specified value or blank. When the input matches the specified value the cell is rendered as a checked checkbox. When the input is blank the cell is rendered as an unchecked checkbox.

// Set the data validation for cell A1 to require a custom checked value that is
// rendered as a checkbox.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireCheckbox('APPROVED').build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is one of the specified values. When the input is checkedValue the cell is rendered as a checked checkbox. When the input is uncheckedValue the cell is rendered as an unchecked checkbox.

// Set the data validation for cell A1 to require custom checked values that are
// rendered as a checkbox.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireCheckbox('APPROVED', 'PENDING')
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date.

// Set the data validation for cell A1 to require a date.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation().requireDate().build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date after the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date after January 1, 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireDateAfter(new Date('1/1/2013'))
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date before the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date before January 1, 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireDateBefore(new Date('1/1/2013'))
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date that falls between, or is either of, two specified dates. The time fields of the Date objects are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date in 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation()
        .requireDateBetween(new Date('1/1/2013'), new Date('12/31/2013'))
        .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date equal to the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date equal to January 1,
// 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireDateEqualTo(new Date('1/1/2013'))
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date that does not fall between, and is neither of, two specified dates. The time fields of the Date objects are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date not in 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation()
        .requireDateNotBetween(new Date('1/1/2013'), new Date('12/31/2013'))
        .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date on or after the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date on or after January 1,
// 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireDateOnOrAfter(new Date('1/1/2013'))
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require a date on or before the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

// Set the data validation for cell A1 to require a date on or before January 1,
// 2013.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireDateOnOrBefore(new Date('1/1/2013'))
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to equal B1 with a custom formula.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireFormulaSatisfied('=EQ(A1,B1)')
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number between 1 and 10.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireNumberBetween(1, 10).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number equal
// to 3.1415926536.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireNumberEqualTo(Math.PI).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number greater than 0.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireNumberGreaterThan(0).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number greater than or equal
// to 0.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireNumberGreaterThanOrEqualTo(0)
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number less than 0.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireNumberLessThan(0).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number less than or equal to
// 0.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireNumberLessThanOrEqualTo(0)
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number not between 1 and 10.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireNumberNotBetween(1, 10).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

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

// Set the data validation for cell A1 to require a number not equal to 0.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireNumberNotEqualTo(0).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input contains the given value.

// Set the data validation for cell A1 to require any value that includes
// "Google".
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireTextContains('Google').build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input does not contain the given value.

// Set the data validation for cell A1 to require any value that does not
// include "@".
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireTextDoesNotContain('@').build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is equal to the given value.

// Set the data validation for cell A1 to require "Yes".
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule =
    SpreadsheetApp.newDataValidation().requireTextEqualTo('Yes').build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is in the form of an email address.

// Set the data validation for cell A1 to require text in the form of an email
// address.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation().requireTextIsEmail().build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is in the form of a URL.

// Set the data validation for cell A1 to require text in the form of a URL.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation().requireTextIsUrl().build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is equal to one of the given values.

// Set the data validation for cell A1 to require "Yes" or "No", with a dropdown
// menu.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireValueInList(['Yes', 'No'])
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is equal to one of the given values, with an option to hide the dropdown menu.

// Set the data validation for cell A1 to require "Yes" or "No", with no
// dropdown menu.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = SpreadsheetApp.newDataValidation()
                 .requireValueInList(['Yes', 'No'], false)
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is equal to a value in the given range.

// Set the data validation for cell A1 to require a value from B1:B10, with a
// dropdown menu.
const cell = SpreadsheetApp.getActive().getRange('A1');
const range = SpreadsheetApp.getActive().getRange('B1:B10');
const rule =
    SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to require that the input is equal to a value in the given range, with an option to hide the dropdown menu.

// Set the data validation for cell A1 to require value from B1:B10, with no
// dropdown menu.
const cell = SpreadsheetApp.getActive().getRange('A1');
const range = SpreadsheetApp.getActive().getRange('B1:B10');
const rule = SpreadsheetApp.newDataValidation()
                 .requireValueInRange(range, false)
                 .build();
cell.setDataValidation(rule);

DataValidationBuilder — this builder, for chaining

Sets whether to show a warning when input fails data validation or whether to reject the input entirely. The default for new data validation rules is true.

DataValidationBuilder — this builder, for chaining

Sets the help text that appears when the user hovers over the cell on which data validation is set.

DataValidationBuilder — this builder, for chaining

Sets the data validation rule to criteria defined by DataValidationCriteria values, typically taken from the criteria and arguments of an existing rule.

// Change existing data validation rules that require a date in 2013 to require
// a date in 2014.
const oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
const newDates = [new Date('1/1/2014'), new Date('12/31/2014')];
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
const rules = range.getDataValidations();

for (let i = 0; i < rules.length; i++) {
  for (let j = 0; j < rules[i].length; j++) {
    const rule = rules[i][j];

    if (rule != null) {
      const criteria = rule.getCriteriaType();
      const args = rule.getCriteriaValues();

      if (criteria === SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN &&
          args[0].getTime() === oldDates[0].getTime() &&
          args[1].getTime() === oldDates[1].getTime()) {
        // Create a builder from the existing rule, then change the dates.
        rules[i][j] = rule.copy().withCriteria(criteria, newDates).build();
      }
    }
  }
}
range.setDataValidations(rules);

DataValidationBuilder — this builder, for chaining

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2024-12-03 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2024-12-03 UTC."],[[["The `DataValidationBuilder` class in Google Apps Script is used to create data validation rules for Google Sheets, ensuring data integrity."],["It offers a variety of methods to define specific criteria, such as number ranges, date restrictions, text patterns, and custom formulas, for cell values."],["Validation rules can be customized further by controlling the dropdown menu visibility and whether to allow invalid input with warnings or outright rejection."],["The `build()` method finalizes the validation rule and can be applied to a specific cell or range of cells using `setDataValidation()`."],["Detailed documentation and code examples are available to guide developers in effectively using `DataValidationBuilder` for diverse data validation scenarios."]]],["The `DataValidationBuilder` creates data validation rules for spreadsheets. Key actions include setting criteria for input, such as `requireDateBetween`, `requireNumberGreaterThan`, `requireTextContains`, or `requireValueInList`. It supports checkbox, date, number, text, list, range, and formula-based validations. Methods like `setHelpText` add cell instructions, and `setAllowInvalid` determines whether to allow or reject invalid input. The `build()` method creates a final `DataValidation` object, while `copy()` duplicates an existing builder. The `withCriteria` method modifies existing rules.\n"]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4