Class DataValidationBuilder

DataValidationBuilder

Builder for data validation rules.

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

Methods

MethodReturn typeBrief description
build()DataValidationConstructs a data validation rule from the settings applied to the builder.
copy()DataValidationBuilderCreates a builder for a data validation rule based on this rule's settings.
getAllowInvalid()BooleanReturns true if the rule shows a warning when input fails data validation, or false if it rejects the input entirely.
getCriteriaType()DataValidationCriteriaGets the rule's criteria type as defined in the DataValidationCriteria enum.
getCriteriaValues()Object[]Gets an array of arguments for the rule's criteria.
getHelpText()StringGets the rule's help text, or null if no help text is set.
requireCheckbox()DataValidationBuilderSets the data validation rule to require that the input is a boolean value; this value is rendered as a checkbox.
requireCheckbox(checkedValue)DataValidationBuilderSets the data validation rule to require that the input is the specified value or blank.
requireCheckbox(checkedValue, uncheckedValue)DataValidationBuilderSets the data validation rule to require that the input is one of the specified values.
requireDate()DataValidationBuilderSets the data validation rule to require a date.
requireDateAfter(date)DataValidationBuilderSets the data validation rule to require a date after the given value.
requireDateBefore(date)DataValidationBuilderSets the data validation rule to require a date before the given value.
requireDateBetween(start, end)DataValidationBuilderSets the data validation rule to require a date that falls between, or is either of, two specified dates.
requireDateEqualTo(date)DataValidationBuilderSets the data validation rule to require a date equal to the given value.
requireDateNotBetween(start, end)DataValidationBuilderSets the data validation rule to require a date that does not fall between, and is neither of, two specified dates.
requireDateOnOrAfter(date)DataValidationBuilderSets the data validation rule to require a date on or after the given value.
requireDateOnOrBefore(date)DataValidationBuilderSets the data validation rule to require a date on or before the given value.
requireFormulaSatisfied(formula)DataValidationBuilderSets the data validation rule to require that the given formula evaluates to true.
requireNumberBetween(start, end)DataValidationBuilderSets the data validation rule to require a number that falls between, or is either of, two specified numbers.
requireNumberEqualTo(number)DataValidationBuilderSets the data validation rule to require a number equal to the given value.
requireNumberGreaterThan(number)DataValidationBuilderSets the data validation rule to require a number greater than the given value.
requireNumberGreaterThanOrEqualTo(number)DataValidationBuilderSets the data validation rule to require a number greater than or equal to the given value.
requireNumberLessThan(number)DataValidationBuilderSets the data validation rule to require a number less than the given value.
requireNumberLessThanOrEqualTo(number)DataValidationBuilderSets the data validation rule to require a number less than or equal to the given value.
requireNumberNotBetween(start, end)DataValidationBuilderSets the data validation rule to require a number that does not fall between, and is neither of, two specified numbers.
requireNumberNotEqualTo(number)DataValidationBuilderSets the data validation rule to require a number not equal to the given value.
requireTextContains(text)DataValidationBuilderSets the data validation rule to require that the input contains the given value.
requireTextDoesNotContain(text)DataValidationBuilderSets the data validation rule to require that the input does not contain the given value.
requireTextEqualTo(text)DataValidationBuilderSets the data validation rule to require that the input is equal to the given value.
requireTextIsEmail()DataValidationBuilderSets the data validation rule to require that the input is in the form of an email address.
requireTextIsUrl()DataValidationBuilderSets the data validation rule to require that the input is in the form of a URL.
requireValueInList(values)DataValidationBuilderSets the data validation rule to require that the input is equal to one of the given values.
requireValueInList(values, showDropdown)DataValidationBuilderSets 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.
requireValueInRange(range)DataValidationBuilderSets the data validation rule to require that the input is equal to a value in the given range.
requireValueInRange(range, showDropdown)DataValidationBuilderSets 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.
setAllowInvalid(allowInvalidData)DataValidationBuilderSets whether to show a warning when input fails data validation or whether to reject the input entirely.
setHelpText(helpText)DataValidationBuilderSets the help text that appears when the user hovers over the cell on which data validation is set.
withCriteria(criteria, args)DataValidationBuilderSets the data validation rule to criteria defined by DataValidationCriteria values, typically taken from the criteria and arguments of an existing rule.

Detailed documentation

build()

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

Return

DataValidation — 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.
var oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
var newDates = [new Date('1/1/2014'), new Date('12/31/2014')];
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
var rules = range.getDataValidations();

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

    if (rule != null) {
      var criteria = rule.getCriteriaType();
      var 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);

Return

DataValidationBuilder — a builder based on this rule's settings


getAllowInvalid()

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.

Return

Booleantrue if the rule allows input that fails data validation; false if not


getCriteriaType()

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = cell.getDataValidation();
if (rule != null) {
  var criteria = rule.getCriteriaType();
  var 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.')
}

Return

DataValidationCriteria — the type of data validation criteria


getCriteriaValues()

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = cell.getDataValidation();
if (rule != null) {
  var criteria = rule.getCriteriaType();
  var 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.')
}

Return

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


getHelpText()

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

Return

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


requireCheckbox()

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
cell.setDataValidation(rule);

Return

DataValidationBuilder — this builder, for chaining


requireCheckbox(checkedValue)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireCheckbox('APPROVED').build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
checkedValueObjectThe value assigned to a checked box.

Return

DataValidationBuilder — this builder, for chaining


requireCheckbox(checkedValue, uncheckedValue)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireCheckbox('APPROVED', 'PENDING').build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
checkedValueObjectThe value assigned to a checked box.
uncheckedValueObjectThe value assigned to an unchecked box.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireDate().build();
cell.setDataValidation(rule);

Return

DataValidationBuilder — this builder, for chaining


requireDateAfter(date)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireDateAfter(new Date('1/1/2013')).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
dateDateThe latest unacceptable date.

Return

DataValidationBuilder — this builder, for chaining


requireDateBefore(date)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireDateBefore(new Date('1/1/2013')).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
dateDateThe earliest unacceptable date.

Return

DataValidationBuilder — this builder, for chaining


requireDateBetween(start, end)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation()
    .requireDateBetween(new Date('1/1/2013'), new Date('12/31/2013')).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
startDateThe earliest acceptable date.
endDateThe latest acceptable date.

Return

DataValidationBuilder — this builder, for chaining


requireDateEqualTo(date)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireDateEqualTo(new Date('1/1/2013'))
    .build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
dateDateThe sole acceptable date.

Return

DataValidationBuilder — 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 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation()
    .requireDateNotBetween(new Date('1/1/2013'), new Date('12/31/2013')).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
startDateThe earliest unacceptable date.
endDateThe latest unacceptable date.

Return

DataValidationBuilder — this builder, for chaining


requireDateOnOrAfter(date)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation()
    .requireDateOnOrAfter(new Date('1/1/2013')).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
dateDateThe earliest acceptable date.

Return

DataValidationBuilder — this builder, for chaining


requireDateOnOrBefore(date)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation()
    .requireDateOnOrBefore(new Date('1/1/2013')).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
dateDateThe latest acceptable date.

Return

DataValidationBuilder — this builder, for chaining


requireFormulaSatisfied(formula)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireFormulaSatisfied('=EQ(A1,B1)').build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
formulaStringA custom formula that evaluates to true if the input is valid.

Return

DataValidationBuilder — this builder, for chaining


requireNumberBetween(start, end)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberBetween(1, 10).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
startNumberThe lowest acceptable value.
endNumberThe highest acceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberEqualTo(3.1415926536).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
numberNumberThe sole acceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberGreaterThan(0).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
numberNumberThe highest unacceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(0).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
numberNumberThe lowest acceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberLessThan(0).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
numberNumberThe lowest unacceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberLessThanOrEqualTo(0).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
numberNumberThe highest acceptable value.

Return

DataValidationBuilder — this builder, for chaining


requireNumberNotBetween(start, end)

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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberNotBetween(1, 10).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
startNumberThe lowest unacceptable value.
endNumberThe highest unacceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireNumberNotEqualTo(0).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
numberNumberThe sole unacceptable value.

Return

DataValidationBuilder — 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".
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireTextContains('Google').build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
textStringThe value that the input must contain.

Return

DataValidationBuilder — 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 "@".
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireTextDoesNotContain('@').build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
textStringThe value that the input must not contain.

Return

DataValidationBuilder — 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".
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireTextEqualTo('Yes').build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
textStringThe sole acceptable value.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireTextIsEmail().build();
cell.setDataValidation(rule);

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireTextIsUrl().build();
cell.setDataValidation(rule);

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No']).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
valuesString[]An array of acceptable values.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], false).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
valuesString[]An array of acceptable values.
showDropdownBooleantrue if the spreadsheet should show a dropdown menu for the values; false if not.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var range = SpreadsheetApp.getActive().getRange('B1:B10');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
rangeRangeA range that contains the acceptable values.

Return

DataValidationBuilder — 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.
var cell = SpreadsheetApp.getActive().getRange('A1');
var range = SpreadsheetApp.getActive().getRange('B1:B10');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range, false).build();
cell.setDataValidation(rule);

Parameters

NameTypeDescription
rangeRangeA range that contains the acceptable values.
showDropdownBooleantrue if the spreadsheet should show a dropdown menu for the values; false if not.

Return

DataValidationBuilder — this builder, for chaining


setAllowInvalid(allowInvalidData)

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.

Parameters

NameTypeDescription
allowInvalidDataBooleantrue if the rule should allow input that fails data validation; false if not.

Return

DataValidationBuilder — this builder, for chaining


setHelpText(helpText)

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

Parameters

NameTypeDescription
helpTextStringThe help text to set.

Return

DataValidationBuilder — this builder, for chaining


withCriteria(criteria, args)

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.
var oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
var newDates = [new Date('1/1/2014'), new Date('12/31/2014')];
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
var rules = range.getDataValidations();

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

    if (rule != null) {
      var criteria = rule.getCriteriaType();
      var 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);

Parameters

NameTypeDescription
criteriaDataValidationCriteriaThe type of data validation criteria.
argsObject[]An array of arguments appropriate to the criteria type; the number of arguments and their type match the corresponding require...() method above.

Return

DataValidationBuilder — this builder, for chaining