Class DataValidationBuilder
Page Summary
The DataValidationBuilder is used to construct data validation rules in a spreadsheet.
You can define rules to require specific data types like dates, numbers, text, or values from a list or range.
Builders can be copied to easily modify existing data validation rules.
You can configure whether invalid input results in a warning or is rejected entirely.
Help text can be set to appear when a user hovers over a cell with data validation.
Builder for data validation rules.
// Set the data validation for cell A1 to require a value from B1:B10.constcell=SpreadsheetApp.getActive().getRange('A1');constrange=SpreadsheetApp.getActive().getRange('B1:B10');construle=SpreadsheetApp.newDataValidation().requireValueInRange(range).build();cell.setDataValidation(rule);
Methods
| Method | Return type | Brief description |
|---|---|---|
build() | Data | Constructs a data validation rule from the settings applied to the builder. |
copy() | Data | Creates a builder for a data validation rule based on this rule's settings. |
get | Boolean | Returnstrue if the rule shows a warning when input fails data validation, orfalse if it rejects the input entirely. |
get | Data | Gets the rule's criteria type as defined in theData enum. |
get | Object[] | Gets an array of arguments for the rule's criteria. |
get | String | Gets the rule's help text, ornull if no help text is set. |
require | Data | Sets the data validation rule to require that the input is a boolean value; this value isrendered as a checkbox. |
require | Data | Sets the data validation rule to require that the input is the specified value or blank. |
require | Data | Sets the data validation rule to require that the input is one of the specified values. |
require | Data | Sets the data validation rule to require a date. |
require | Data | Sets the data validation rule to require a date after the given value. |
require | Data | Sets the data validation rule to require a date before the given value. |
require | Data | Sets the data validation rule to require a date that falls between, or is either of, twospecified dates. |
require | Data | Sets the data validation rule to require a date equal to the given value. |
require | Data | Sets the data validation rule to require a date that does not fall between, and is neither of,two specified dates. |
require | Data | Sets the data validation rule to require a date on or after the given value. |
require | Data | Sets the data validation rule to require a date on or before the given value. |
require | Data | Sets the data validation rule to require that the given formula evaluates totrue. |
require | Data | Sets the data validation rule to require a number that falls between, or is either of, twospecified numbers. |
require | Data | Sets the data validation rule to require a number equal to the given value. |
require | Data | Sets the data validation rule to require a number greater than the given value. |
require | Data | Sets the data validation rule to require a number greater than or equal to the given value. |
require | Data | Sets the data validation rule to require a number less than the given value. |
require | Data | Sets the data validation rule to require a number less than or equal to the given value. |
require | Data | Sets the data validation rule to require a number that does not fall between, and is neitherof, two specified numbers. |
require | Data | Sets the data validation rule to require a number not equal to the given value. |
require | Data | Sets the data validation rule to require that the input contains the given value. |
require | Data | Sets the data validation rule to require that the input does not contain the given value. |
require | Data | Sets the data validation rule to require that the input is equal to the given value. |
require | Data | Sets the data validation rule to require that the input is in the form of an email address. |
require | Data | Sets the data validation rule to require that the input is in the form of a URL. |
require | Data | Sets the data validation rule to require that the input is equal to one of the given values. |
require | Data | 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. |
require | Data | Sets the data validation rule to require that the input is equal to a value in the given range. |
require | Data | 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 | Data | Sets whether to show a warning when input fails data validation or whether to reject the inputentirely. |
set | Data | Sets the help text that appears when the user hovers over the cell on which data validation isset. |
with | Data | Sets the data validation rule to criteria defined byData values,typically taken from thecriteria andarguments of an existing rule. |
Detailed documentation
build()
Constructs a data validation rule from the settings applied to the builder.
Return
Data — a representation of the data validation rule
copy()
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.constoldDates=[newDate('1/1/2013'),newDate('12/31/2013')];constnewDates=[newDate('1/1/2014'),newDate('12/31/2014')];constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());construles=range.getDataValidations();for(leti=0;i <rules.length;i++){for(letj=0;j <rules[i].length;j++){construle=rules[i][j];if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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);
Return
Data — a builder based on this rule's settings
getAllowInvalid()
Returnstrue if the rule shows a warning when input fails data validation, orfalse if it rejects the input entirely. The default for new data validation rules istrue.
Return
Boolean —true if the rule allows input that fails data validation;false if not
getCriteriaType()
Gets the rule's criteria type as defined in theData enum. To get thearguments for the criteria, useget. To use these values to create ormodify a data validation rule, seewith.
// Log information about the data validation rule for cell A1.constcell=SpreadsheetApp.getActive().getRange('A1');construle=cell.getDataValidation();if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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.');}
Return
Data — the type of data validation criteria
getCriteriaValues()
Gets an array of arguments for the rule's criteria. To get the criteria type, useget. To use these values to create or modify a data validation rule, seewith.
// Log information about the data validation rule for cell A1.constcell=SpreadsheetApp.getActive().getRange('A1');construle=cell.getDataValidation();if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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.');}
Return
Object[] — an array of arguments appropriate to the rule's criteria type; the number of arguments and their type match the correspondingrequire...() method of theData class
getHelpText()
Gets the rule's help text, ornull if no help text is set.
Return
String — the rule's help text, ornull if no help text is set
requireCheckbox()
Sets the data validation rule to require that the input is a boolean value; this value isrendered as a checkbox.
// Set the data validation for cell A1 to require a boolean value; the value is// rendered as a checkbox.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireCheckbox().build();cell.setDataValidation(rule);
Return
Data — this builder, for chaining
requireCheckbox(checkedValue)
Sets the data validation rule to require that the input is the specified value or blank. Whenthe input matches the specified value the cell is rendered as a checked checkbox. When theinput 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireCheckbox('APPROVED').build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
checked | Object | The value assigned to a checked box. |
Return
Data — this builder, for chaining
requireCheckbox(checkedValue, uncheckedValue)
Sets the data validation rule to require that the input is one of the specified values. Whenthe input ischecked the cell is rendered as a checked checkbox. When theinput isunchecked 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireCheckbox('APPROVED','PENDING').build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
checked | Object | The value assigned to a checked box. |
unchecked | Object | The value assigned to an unchecked box. |
Return
Data — this builder, for chaining
requireDate()
Sets the data validation rule to require a date.
// Set the data validation for cell A1 to require a date.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDate().build();cell.setDataValidation(rule);
Return
Data — this builder, for chaining
requireDateAfter(date)
Sets the data validation rule to require a date after the given value. The time fields of theDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateAfter(newDate('1/1/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The latest unacceptable date. |
Return
Data — this builder, for chaining
requireDateBefore(date)
Sets the data validation rule to require a date before the given value. The time fields of theDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateBefore(newDate('1/1/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The earliest unacceptable date. |
Return
Data — this builder, for chaining
requireDateBetween(start, end)
Sets the data validation rule to require a date that falls between, or is either of, twospecified dates. The time fields of theDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateBetween(newDate('1/1/2013'),newDate('12/31/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
start | Date | The earliest acceptable date. |
end | Date | The latest acceptable date. |
Return
Data — this builder, for chaining
requireDateEqualTo(date)
Sets the data validation rule to require a date equal to the given value. The time fields oftheDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateEqualTo(newDate('1/1/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The sole acceptable date. |
Return
Data — this builder, for chaining
requireDateNotBetween(start, end)
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 theDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateNotBetween(newDate('1/1/2013'),newDate('12/31/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
start | Date | The earliest unacceptable date. |
end | Date | The latest unacceptable date. |
Return
Data — this builder, for chaining
requireDateOnOrAfter(date)
Sets the data validation rule to require a date on or after the given value. The time fields oftheDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateOnOrAfter(newDate('1/1/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The earliest acceptable date. |
Return
Data — this builder, for chaining
requireDateOnOrBefore(date)
Sets the data validation rule to require a date on or before the given value. The time fieldsof theDate 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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireDateOnOrBefore(newDate('1/1/2013')).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
date | Date | The latest acceptable date. |
Return
Data — this builder, for chaining
requireFormulaSatisfied(formula)
Sets the data validation rule to require that the given formula evaluates totrue.
// Set the data validation for cell A1 to equal B1 with a custom formula.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireFormulaSatisfied('=EQ(A1,B1)').build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
formula | String | A custom formula that evaluates totrue if the input is valid. |
Return
Data — this builder, for chaining
requireNumberBetween(start, end)
Sets the data validation rule to require a number that falls between, or is either of, twospecified numbers.
// Set the data validation for cell A1 to require a number between 1 and 10.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberBetween(1,10).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
start | Number | The lowest acceptable value. |
end | Number | The highest acceptable value. |
Return
Data — this builder, for chaining
requireNumberEqualTo(number)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberEqualTo(Math.PI).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The sole acceptable value. |
Return
Data — this builder, for chaining
requireNumberGreaterThan(number)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberGreaterThan(0).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The highest unacceptable value. |
Return
Data — this builder, for chaining
requireNumberGreaterThanOrEqualTo(number)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(0).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The lowest acceptable value. |
Return
Data — this builder, for chaining
requireNumberLessThan(number)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberLessThan(0).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The lowest unacceptable value. |
Return
Data — this builder, for chaining
requireNumberLessThanOrEqualTo(number)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberLessThanOrEqualTo(0).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The highest acceptable value. |
Return
Data — this builder, for chaining
requireNumberNotBetween(start, end)
Sets the data validation rule to require a number that does not fall between, and is neitherof, two specified numbers.
// Set the data validation for cell A1 to require a number not between 1 and 10.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberNotBetween(1,10).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
start | Number | The lowest unacceptable value. |
end | Number | The highest unacceptable value. |
Return
Data — this builder, for chaining
requireNumberNotEqualTo(number)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireNumberNotEqualTo(0).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
number | Number | The sole unacceptable value. |
Return
Data — this builder, for chaining
requireTextContains(text)
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".constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireTextContains('Google').build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | The value that the input must contain. |
Return
Data — this builder, for chaining
requireTextDoesNotContain(text)
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 "@".constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireTextDoesNotContain('@').build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | The value that the input must not contain. |
Return
Data — this builder, for chaining
requireTextEqualTo(text)
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".constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireTextEqualTo('Yes').build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
text | String | The sole acceptable value. |
Return
Data — this builder, for chaining
requireTextIsEmail()
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireTextIsEmail().build();cell.setDataValidation(rule);
Return
Data — this builder, for chaining
requireTextIsUrl()
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireTextIsUrl().build();cell.setDataValidation(rule);
Return
Data — this builder, for chaining
requireValueInList(values)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireValueInList(['Yes','No']).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
values | String[] | An array of acceptable values. |
Return
Data — this builder, for chaining
requireValueInList(values, showDropdown)
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.constcell=SpreadsheetApp.getActive().getRange('A1');construle=SpreadsheetApp.newDataValidation().requireValueInList(['Yes','No'],false).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
values | String[] | An array of acceptable values. |
show | Boolean | true if the spreadsheet should show a dropdown menu for the values;false if not. |
Return
Data — this builder, for chaining
requireValueInRange(range)
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.constcell=SpreadsheetApp.getActive().getRange('A1');constrange=SpreadsheetApp.getActive().getRange('B1:B10');construle=SpreadsheetApp.newDataValidation().requireValueInRange(range).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
range | Range | A range that contains the acceptable values. |
Return
Data — this builder, for chaining
requireValueInRange(range, showDropdown)
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.constcell=SpreadsheetApp.getActive().getRange('A1');constrange=SpreadsheetApp.getActive().getRange('B1:B10');construle=SpreadsheetApp.newDataValidation().requireValueInRange(range,false).build();cell.setDataValidation(rule);
Parameters
| Name | Type | Description |
|---|---|---|
range | Range | A range that contains the acceptable values. |
show | Boolean | true if the spreadsheet should show a dropdown menu for the values;false if not. |
Return
Data — this builder, for chaining
setAllowInvalid(allowInvalidData)
Sets whether to show a warning when input fails data validation or whether to reject the inputentirely. The default for new data validation rules istrue.
Parameters
| Name | Type | Description |
|---|---|---|
allow | Boolean | true if the rule should allow input that fails data validation;false if not. |
Return
Data — this builder, for chaining
setHelpText(helpText)
Sets the help text that appears when the user hovers over the cell on which data validation isset.
Parameters
| Name | Type | Description |
|---|---|---|
help | String | The help text to set. |
Return
Data — this builder, for chaining
withCriteria(criteria, args)
Sets the data validation rule to criteria defined byData values,typically taken from thecriteria andarguments of an existing rule.
// Change existing data validation rules that require a date in 2013 to require// a date in 2014.constoldDates=[newDate('1/1/2013'),newDate('12/31/2013')];constnewDates=[newDate('1/1/2014'),newDate('12/31/2014')];constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());construles=range.getDataValidations();for(leti=0;i <rules.length;i++){for(letj=0;j <rules[i].length;j++){construle=rules[i][j];if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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);
Parameters
| Name | Type | Description |
|---|---|---|
criteria | Data | The type of data validation criteria. |
args | Object[] | An array of arguments appropriate to the criteria type; the number of arguments and their type match the correspondingrequire...() method above. |
Return
Data — this builder, for chaining
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-11 UTC.