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.

Practice

1. What is the main purpose of a dropdown menu in Google Sheets?

easy

A. To let you pick a value from a list inside a cell

B. To automatically calculate sums

C. To change the font style of a cell

D. To protect a sheet from editing

Solution

1. Step 1: Understand dropdown menus

   Dropdown menus let users select one option from a list inside a cell, making data entry easier.

2. Step 2: Compare options

   Only To let you pick a value from a list inside a cell describes this feature correctly; others describe unrelated functions.

3. Final Answer:

   To let you pick a value from a list inside a cell -> Option A

4. Quick Check:

   Dropdown menu = pick from list [OK]

Hint: Dropdowns let you choose from a list inside a cell [OK]

Common Mistakes:

• Confusing dropdowns with formulas
• Thinking dropdowns change cell formatting
• Mixing dropdowns with sheet protection

2. Which of these is the correct way to create a dropdown menu in Google Sheets?

easy

A. Select cells > Format > Conditional formatting > Add rule

B. Select cells > Data > Data validation > List of items or range > Save

C. Select cells > Insert > Chart > Dropdown

D. Select cells > Tools > Script editor > Create dropdown

Solution

1. Step 1: Recall dropdown creation steps

   Dropdowns are created via Data > Data validation, choosing list items or a range.

2. Step 2: Check options

   Only Select cells > Data > Data validation > List of items or range > Save correctly describes this process; others describe unrelated features.

3. Final Answer:

   Select cells > Data > Data validation > List of items or range > Save -> Option B

4. Quick Check:

   Data validation creates dropdowns [OK]

Hint: Use Data > Data validation to make dropdowns [OK]

Common Mistakes:

• Using Format or Insert menus instead of Data
• Confusing dropdown creation with charts or scripts
• Skipping the Save step

3. You created a dropdown menu in cell A1 with items: Red, Green, Blue. What happens if you type Yellow in A1?

medium

A. The cell clears automatically

B. The cell accepts Yellow without error

C. Yellow is automatically added to the dropdown list

D. An error message appears and prevents entry

Solution

1. Step 1: Understand dropdown validation

   Dropdown menus restrict entries to listed items unless 'Show warning' is chosen.

2. Step 2: Predict behavior for invalid entry

   Typing 'Yellow' not in the list triggers an error preventing entry by default.

3. Final Answer:

   An error message appears and prevents entry -> Option D

4. Quick Check:

   Invalid dropdown entry = error [OK]

Hint: Typing outside dropdown list causes error by default [OK]

Common Mistakes:

• Assuming new items auto-add to dropdown
• Thinking invalid entries are accepted silently
• Believing the cell clears automatically

4. You set a dropdown menu using Data validation with a range but it shows blank options. What is the likely fix?

medium

A. Ensure the range contains visible, non-empty cells

B. Change the cell format to Text

C. Delete the dropdown and retype items manually

D. Refresh the browser tab

Solution

1. Step 1: Identify cause of blank dropdown

   Dropdowns from ranges show blank if the range cells are empty or hidden.

2. Step 2: Fix by checking range content

   Make sure the range has visible, filled cells to appear as dropdown options.

3. Final Answer:

   Ensure the range contains visible, non-empty cells -> Option A

4. Quick Check:

   Dropdown range must have data [OK]

Hint: Check dropdown range cells are not empty [OK]

Common Mistakes:

• Changing cell format instead of range content
• Deleting dropdown unnecessarily
• Thinking browser refresh fixes data validation

5. You want a dropdown in cell B2 that shows only the names from column A where the adjacent cell in column B is not empty. How can you create this dynamic dropdown?

hard

A. Create a dropdown with all names and ignore column B

B. Manually type all names from column A into the dropdown list

C. Use Data validation with a FILTER formula like =FILTER(A:A, B:B <> "")

D. Use Data validation with a fixed range like A1:A10

Solution

1. Step 1: Understand dynamic dropdown needs

   The dropdown should show names from column A only if adjacent B cell is not empty.

2. Step 2: Use FILTER formula in Data validation

   Using =FILTER(A:A, B:B <> "") creates a dynamic list meeting the condition.

3. Step 3: Compare options

   Only Use Data validation with a FILTER formula like =FILTER(A:A, B:B <> "") uses a formula to filter dynamically; others are static or ignore the condition.

4. Final Answer:

   Use Data validation with a FILTER formula like =FILTER(A:A, B:B <> "") -> Option C

5. Quick Check:

   FILTER formula creates dynamic dropdown [OK]

Hint: Use FILTER formula in Data validation for dynamic lists [OK]

Common Mistakes:

• Typing static lists instead of dynamic formulas
• Ignoring the condition on adjacent cells
• Using fixed ranges that don't update