Dropdown menus in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a dropdown menu in cell A1 with options "Yes" and "No".

Google Sheets

DataValidationBuilder = SpreadsheetApp.newDataValidation().requireValueInList([1]).build()
SpreadsheetApp.getActiveSheet().getRange('A1').setDataValidation(DataValidationBuilder)

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

A[Yes, No]

B"Yes, No"

C["Yes", "No"]

DYes, No

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around options

Not using square brackets for the list

Passing a single string instead of a list

The method requireValueInList expects a list of strings inside square brackets. So ["Yes", "No"] is correct.

2fill in blank

medium

Complete the code to set a dropdown menu in cell B2 with options from the range C1:C5.

Google Sheets

var range = SpreadsheetApp.getActiveSheet().getRange('C1:C5');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange([1], true).build();
SpreadsheetApp.getActiveSheet().getRange('B2').setDataValidation(rule);

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

Arange

BC1:C5

C"C1:C5"

DgetRange('C1:C5')

Attempts:

3 left

💡 Hint

Common Mistakes

Passing a string instead of a Range object

Using quotes around the range variable

Calling getRange inside requireValueInRange

The method requireValueInRange requires a Range object, so passing the variable range is correct.

3fill in blank

hard

Fix the error in the code to create a dropdown menu in cell D4 with options "Red", "Green", "Blue".

Google Sheets

var rule = SpreadsheetApp.newDataValidation().requireValueInList([1]).build();
SpreadsheetApp.getActiveSheet().getRange('D4').setDataValidation(rule);

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

Missing quotes around options

Passing a single string instead of a list

Not using square brackets

The list of options must be an array of strings with quotes around each option, like ["Red", "Green", "Blue"].

4fill in blank

hard

Fill both blanks to create a dropdown menu in cell E5 with options from range F1:F10 and show a help message "Select a color".

Google Sheets

var range = SpreadsheetApp.getActiveSheet().getRange('F1:F10');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange([1], true).setHelpText([2]).build();
SpreadsheetApp.getActiveSheet().getRange('E5').setDataValidation(rule);

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

Arange

B"Select a color"

C"Choose an option"

DF1:F10

Attempts:

3 left

💡 Hint

Common Mistakes

Passing a string instead of a Range object for the first blank

Not using quotes for the help text

Using the range address string instead of the Range object

The first blank needs the Range object range. The second blank needs the help text string "Select a color".

5fill in blank

hard

Fill all three blanks to create a dropdown menu in cell G7 with options "Small", "Medium", "Large", show help text "Pick size", and allow invalid input.

Google Sheets

var rule = SpreadsheetApp.newDataValidation().requireValueInList([1]).setHelpText([2]).setAllowInvalid([3]).build();
SpreadsheetApp.getActiveSheet().getRange('G7').setDataValidation(rule);

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

A["Small", "Medium", "Large"]

B"Pick size"

Ctrue

Dfalse

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around options or help text

Passing false instead of true for allow invalid input

Using a string instead of a boolean for allow invalid input

The list of options is ["Small", "Medium", "Large"], the help text is "Pick size", and true allows invalid input.