Stay organized with collections Save and categorize content based on your preferences.
DataValidationCriteriaAn enumeration representing the data validation criteria that can be set on a range.
To call an enum, you call its parent class, name, and property. For example, SpreadsheetApp.DataValidationCriteria.DATE_IS_VALID_DATE.
// 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); Properties Property Type Description DATE_AFTER Enum Requires a date that is after the given value. DATE_BEFORE Enum Requires a date that is before the given value. DATE_BETWEEN Enum Requires a date that is between the given values. DATE_EQUAL_TO Enum Requires a date that is equal to the given value. DATE_IS_VALID_DATE Enum Requires a date. DATE_NOT_BETWEEN Enum Requires a date that is not between the given values. DATE_ON_OR_AFTER Enum Require a date that is on or after the given value. DATE_ON_OR_BEFORE Enum Requires a date that is on or before the given value. NUMBER_BETWEEN Enum Requires a number that is between the given values. NUMBER_EQUAL_TO Enum Requires a number that is equal to the given value. NUMBER_GREATER_THAN Enum Require a number that is greater than the given value. NUMBER_GREATER_THAN_OR_EQUAL_TO Enum Requires a number that is greater than or equal to the given value. NUMBER_LESS_THAN Enum Requires a number that is less than the given value. NUMBER_LESS_THAN_OR_EQUAL_TO Enum Requires a number that is less than or equal to the given value. NUMBER_NOT_BETWEEN Enum Requires a number that is not between the given values. NUMBER_NOT_EQUAL_TO Enum Requires a number that is not equal to the given value. TEXT_CONTAINS Enum Requires that the input contains the given value. TEXT_DOES_NOT_CONTAIN Enum Requires that the input does not contain the given value. TEXT_EQUAL_TO Enum Requires that the input is equal to the given value. TEXT_IS_VALID_EMAIL Enum Requires that the input is in the form of an email address. TEXT_IS_VALID_URL Enum Requires that the input is in the form of a URL. VALUE_IN_LIST Enum Requires that the input is equal to one of the given values. VALUE_IN_RANGE Enum Requires that the input is equal to a value in the given range. CUSTOM_FORMULA Enum Requires that the input makes the given formula evaluate to true. CHECKBOX Enum Requires that the input is a custom value or a boolean; rendered as a checkbox. DATE_AFTER_RELATIVE Enum Requires a date that is after the relative date value. DATE_BEFORE_RELATIVE Enum Requires a date that is before the relative date value. DATE_EQUAL_TO_RELATIVE Enum Requires a date that is equal to the relative date value.
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 2025-08-04 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 2025-08-04 UTC."],[[["`DataValidationCriteria` is an enumeration used to define data validation rules for cell ranges in Google Sheets."],["It provides various criteria like `DATE_BETWEEN`, `NUMBER_GREATER_THAN`, `TEXT_CONTAINS`, and more to enforce data integrity."],["To use a specific criterion, you refer to it through `SpreadsheetApp.DataValidationCriteria` followed by the specific property, for instance, `SpreadsheetApp.DataValidationCriteria.DATE_IS_VALID_DATE`."],["The provided code example demonstrates how to modify existing data validation rules that require a date within a certain range."]]],["`DataValidationCriteria` defines the types of data validation rules applicable to a spreadsheet range. You can retrieve or modify existing rules on a range, to change the validation criteria. For example, you can update all DATE_BETWEEN rules from a date range in 2013 to a new date range in 2014, by copying and modifying the existing rule, or set new validation criteria on a range to ensure certain data types, including dates, numbers, or text, fit within specified parameters.\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