Data validation rules in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to apply a data validation rule that allows only numbers between 1 and 10.

Google Sheets

dataValidation = SpreadsheetApp.newDataValidation().requireNumberBetween([1], 10).build()

Drag options to blanks, or click blank then click option'

A1

B"1"

Ctrue

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using a string instead of a number for the minimum value.

Using 0 instead of 1 as the minimum value.

The requireNumberBetween method needs the minimum number as the first argument. Here, 1 is the minimum allowed value.

2fill in blank

medium

Complete the code to set a data validation rule that only allows text values from a list of options.

Google Sheets

dataValidation = SpreadsheetApp.newDataValidation().requireValueInList([1], true).build()

Drag options to blanks, or click blank then click option'

A[Red, Green, Blue]

B"Red, Green, Blue"

C["Red", "Green", "Blue"]

DRed, Green, Blue

Attempts:

3 left

💡 Hint

Common Mistakes

Passing a single string with commas instead of an array.

Not quoting the list items.

The requireValueInList method expects an array of strings as the first argument. So, the list must be inside square brackets and each option in quotes.

3fill in blank

hard

Fix the error in the code to require a checkbox data validation.

Google Sheets

dataValidation = SpreadsheetApp.newDataValidation().requireCheckbox([1]).build()

Drag options to blanks, or click blank then click option'

Afalse

Btrue

C"true"

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using a string "true" instead of boolean true.

Using numbers like 1 instead of boolean.

The requireCheckbox method takes a boolean argument to indicate if the checkbox is checked or unchecked by default. It must be the boolean true or false, not a string or number.

4fill in blank

hard

Complete the code to create a data validation rule that only allows dates after January 1, 2023.

Google Sheets

dataValidation = SpreadsheetApp.newDataValidation().requireDateAfter([1]).build()

Drag options to blanks, or click blank then click option'

ADate.parse('2023-01-01')

Bnew Date('01/01/2023')

CDate('2023-01-01')

Dnew Date('2023-01-01')

Attempts:

3 left

💡 Hint

Common Mistakes

Using Date() without new keyword.

Using Date.parse() which returns a number, not a Date object.

The requireDateAfter method expects a JavaScript Date object. Using new Date('2023-01-01') creates the correct date object.

5fill in blank

hard

Fill all three blanks to create a data validation rule that requires a number between 5 and 15 and shows custom help text.

Google Sheets

dataValidation = SpreadsheetApp.newDataValidation().requireNumberBetween([1], [2]).setHelpText([3]).build()

Drag options to blanks, or click blank then click option'

A5

B15

C"Please enter a number between 5 and 15."

D"Number must be between 1 and 10."

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the min and max values.

Using a number instead of a string for the help text.

The requireNumberBetween method needs the minimum and maximum numbers as the first two arguments. The setHelpText method sets the custom help text shown to users.