newDataValidation

Returns a new DataValidation object to be applied to a workbook.

newDataValidation()

About DataValidations

• DataValidations allow you to restrict the values which can be entered in specific cells.
• You can define the criteria for what the cells can accept in various ways:
  • by providing an array of acceptable values;
  • by specifying existing cells in the same or a different sheet in the workbook which contain the acceptable values;
  • by specifying the minimum and/or maximum acceptable values;
  • by specifying a formula which must evaluate to true for the value to be accepted, e.g. ISODD(A1) to only allow odd numbers.
• The object allows you to customize the error alert which pops up when invalid values are entered.
• For sets of values an optional drop-down will be provided in the UI.
• DataValidations are created using a "builder" syntax (see examples below).
• You can apply the validation object either by passing in the workbook to the addToWorkbook() builder method, or by returning the new DataValidation object and passing it to the library's addDataValidation() method.

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .addToWorkbook( wb );
  
// alternative syntax
 dv = spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] );
 wb = spreadsheet.new();
 spreadsheet.addDataValidation( wb, dv );

Data validation configuration

The following methods are available to build and apply your desired DataValidation.

addToWorkbook( required workbook )

Applies the current DataValidation object to the specified workbook object.

onCells( required cell range )

The cell or range of cells to which the validation will apply, eg "A1:E1".

withAllowEmptyCells( boolean default=true )

Set this to falseto override the default behaviour and require a value to be entered.

withErrorMessage( required string )

The message used in the body of the validation error alert.

withErrorTitle( required string )

The title bar text of the validation error alert.

withFormula( required formula )

The formula should return true or false. If it returns false, the validation will fail.

withMaxDate( required date or formula )

The maximum acceptable date value.

withMaxDecimal( required decimal or formula )

The maximum acceptable decimal value.

withMaxInteger( required integer or formula )

The maximum acceptable whole number value.

withMaxLength( required integer or formula )

The maximum acceptable text length.

withMinDate( required string date )

The minimum acceptable date value.

withMinDecimal( required decimal or formula )

The minimum acceptable decimal value.

withMinInteger( required integer or formula )

The minimum acceptable whole number value.

withMinLength( required integer or formula )

The minimum acceptable text length.

withNoDropdownArrow()

Don't use a dropdown element with a list of values.

withValues( required array )

The array of acceptable values.

withValuesFromCells( required cell range )

The range of cells containing the values.

withValuesFromSheetName( required sheet name )

The name of the sheet containing the values.

Examples

Example 1: passing in allowed values

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .addToWorkbook( wb );

Example 2: using values from other cells in the same sheet

wb = spreadsheet.new();
// the first column will contain the allowed values
spreadsheet.addColumn( wb, [ "London", "Paris", "New York" ] );
spreadsheet.newDataValidation()
  .onCells( "B1:C1" )
  .withValuesFromCells( "A1:A3" )
  .addToWorkbook( wb );

Example 3: using values from other cells in a different sheet

wb = spreadsheet.new();
// create a sheet with the valid values in the first column
spreadsheet.createSheet( wb, "cities" );
spreadsheet.setActiveSheetNumber( wb, 2 );
spreadsheet.addColumn( wb, [ "London", "Paris", "New York" ] );
// create and apply the dataValidation object to the first sheet
spreadsheet.setActiveSheetNumber( wb, 1 );
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValuesFromSheetName( "cities" )
  .withValuesFromCells( "A1:A3" )
  .addToWorkbook( wb );

Example 4: using a custom error message

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .withErrorTitle( "City not allowed" )
  .withErrorMessage( "Please choose from the list of allowed cities" )
  .addToWorkbook( wb );

Example 5: omitting the drop-down UI

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .withNoDropdownArrow()
  .addToWorkbook( wb );

Example 6: limit input to a date range

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withMinDate( CreateDate( 2020, 1, 1 ) )
  .withMaxDate( CreateDate( 2020, 12, 31 ) )
  .withErrorTitle( "Invalid date" )
  .withErrorMessage( "Please enter a date in 2020" )
  .addToWorkbook( wb );

Example 7: limit input to an integer range

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withMinInteger( 1 )
  .withMaxInteger( 100 )
  .withErrorTitle( "Invalid number" )
  .withErrorMessage( "Please enter a whole number between 1 and 100" )
  .addToWorkbook( wb );

Example 8: limit input to an integer contained in a specific cell

wb = spreadsheet.new();
spreadsheet.setCellValue( wb, 50, 1, 1 );//A1
spreadsheet.newDataValidation()
  .onCells( "B1" )
  .withMinInteger( "$A$1" ) //absolute reference to the cell containing the minimum value
  .withErrorTitle( "Invalid number" )
  .withErrorMessage( "The number must be at least that given in cell A1" )
  .addToWorkbook( wb );

Example 9: limit input to an integer contained in the specfied column in the same row

wb = spreadsheet.new();
spreadsheet.addColumn( wb, [ 1, 2, 3 ] );//A1-3
spreadsheet.newDataValidation()
  .onCells( "B1:B3" )
  .withMinInteger( "A1" ) //relative reference to the column in the same row containing the minimum value
  .withErrorTitle( "Invalid number" )
  .withErrorMessage( "The number must be at least that given in column A" )
  .addToWorkbook( wb );

Example 10: limiting the length of text input

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1" )
  .withMaxLength( 50 )
  .withErrorTitle( "Entry too long" )
  .withErrorMessage( "Please enter no more than 50 characters including spaces" )
  .addToWorkbook( wb );

Example 11: custom validation using a formula

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A:A" )//all of column A
  .withFormula( "ISEVEN(A1)" ) //applies to any row in column A
  .withErrorTitle( "Invalid number" )
  .withErrorMessage( "Please enter an even number only" )
  .addToWorkbook( wb );