Add-ons and extensions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Using an Add-on to Clean Data

You have a Google Sheet with messy data including extra spaces and inconsistent capitalization. You want to use an add-on to clean this data quickly. Which add-on feature would help you remove extra spaces and standardize text case?

A Use the 'Remove Duplicates' add-on to clean spaces and fix capitalization.

B Use the 'Chart Builder' add-on to visualize text data.

C Use the 'Translate' add-on to convert text to uppercase.

D Use the 'Power Tools' add-on to trim spaces and change text case.

💡 Hint

Think about an add-on that offers multiple text cleaning tools like trimming spaces and changing case.

The 'Power Tools' add-on includes features to trim spaces and change text case, making it ideal for cleaning messy text data.

❓ Function Choice

intermediate

Choosing the Right Add-on for Data Validation

You want to add advanced data validation rules beyond what Google Sheets offers by default. Which add-on would best help you create custom validation rules?

A Use 'Autocrat' add-on to automate document creation.

B Use 'Advanced Find & Replace' add-on to create validation rules.

C Use 'Data Validation Helper' add-on to build custom validation rules.

D Use 'Form Publisher' add-on to validate form responses.

💡 Hint

Look for an add-on focused on data validation and rules.

'Data Validation Helper' add-on is designed to help create and manage custom data validation rules in Google Sheets.

📊 Formula Result

advanced

Output of a Custom Function from an Add-on

You installed an add-on that provides a custom function =EXTRACTEMAILS(A1:A5) which extracts all email addresses from the range. If A1:A5 contains:

• A1: "Contact: [e-mail]"
• A2: "No email here"
• A3: "Email: [e-mail]"
• A4: ""
• A5: "Reach us at [e-mail]"

What will be the output of =EXTRACTEMAILS(A1:A5) in a single cell?

A "[e-mail] [e-mail] [e-mail]"

B "[e-mail], [e-mail], [e-mail]"

C "[e-mail]; [e-mail]; [e-mail]"

D "[e-mail]\[e-mail]\[e-mail]"

💡 Hint

Think about how add-ons usually separate multiple extracted items in one cell.

Most add-ons return multiple extracted emails separated by commas and spaces in a single cell.

❓ data_analysis

advanced

Analyzing Add-on Impact on Sheet Performance

You installed an add-on that runs a script to update your sheet every minute. After installation, your sheet becomes slow and sometimes unresponsive. What is the most likely cause?

A The add-on's script triggers too frequently, causing performance issues.

B The add-on is incompatible with your browser version.

C The add-on is using too many custom formulas causing recalculation delays.

D The add-on is deleting important data causing errors.

💡 Hint

Think about scripts running often and how that affects sheet speed.

Scripts that trigger very frequently (like every minute) can slow down the sheet by constantly running updates.

🧠 Conceptual

expert

Security Considerations When Using Add-ons

When installing third-party add-ons in Google Sheets, what is the most important security practice you should follow?

A Only install add-ons from trusted sources and review permissions carefully.

B Always grant full access to your Google account to ensure add-ons work properly.

C Disable all add-ons after use to prevent data loss.

D Share your sheet with the add-on developer for better support.

💡 Hint

Think about protecting your data and privacy when using external tools.

Installing add-ons only from trusted sources and reviewing permissions helps protect your data and privacy.

Practice

1. What is the main purpose of add-ons in Google Sheets?

easy

A. To change the spreadsheet's background color

B. To add new tools and features without writing code

C. To create charts manually

D. To delete rows automatically

Solution

1. Step 1: Understand what add-ons do

   Add-ons provide extra tools and features that are not built-in by default.

2. Step 2: Compare options with add-ons purpose

   Only To add new tools and features without writing code describes adding new tools without coding, which matches add-ons' purpose.

3. Final Answer:

   To add new tools and features without writing code -> Option B

4. Quick Check:

   Add-ons = new tools without coding [OK]

Hint: Add-ons = extra tools without coding [OK]

Common Mistakes:

• Thinking add-ons change colors only
• Confusing add-ons with manual chart creation
• Assuming add-ons delete data automatically

2. Which menu in Google Sheets do you use to install add-ons?

easy

A. Extensions

B. File

C. View

D. Insert

Solution

1. Step 1: Recall where add-ons are installed

   Add-ons are installed from the Extensions menu in Google Sheets.

2. Step 2: Match the correct menu option

   Among the options, only Extensions is the correct menu for add-ons.

3. Final Answer:

   Extensions -> Option A

4. Quick Check:

   Install add-ons from Extensions menu [OK]

Hint: Add-ons come from Extensions menu [OK]

Common Mistakes:

• Choosing File menu instead of Extensions
• Selecting Insert or View menus incorrectly
• Not knowing where to find add-ons

3. You installed an add-on that automatically formats dates in your sheet. What will happen when you enter a date like 3/14/2024?

medium

A. The date will stay as plain text without changes

B. The sheet will show an error message

C. The add-on will format the date according to your chosen style

D. The date will be deleted automatically

Solution

1. Step 1: Understand add-on behavior

   The add-on is designed to format dates automatically when entered.

2. Step 2: Predict the effect on date input

   When you enter a date, the add-on changes its appearance to the chosen format, not deleting or ignoring it.

3. Final Answer:

   The add-on will format the date according to your chosen style -> Option C

4. Quick Check:

   Date formatting add-on changes date style [OK]

Hint: Add-ons automate tasks like formatting [OK]

Common Mistakes:

• Thinking add-ons delete data
• Assuming no change happens to input
• Expecting error messages from formatting add-ons

4. You installed an add-on but it does not appear in the Extensions menu. What is the most likely reason?

medium

A. Add-ons only work on mobile apps

B. Google Sheets does not support add-ons

C. You need to refresh the browser to see the add-on

D. The add-on was not installed properly or needs authorization

Solution

1. Step 1: Check installation and permissions

   If an add-on is missing, it often means it was not installed fully or needs permission to run.

2. Step 2: Evaluate other options

   Google Sheets supports add-ons, refreshing may help but usually authorization is needed, and add-ons work on desktop, not only mobile.

3. Final Answer:

   The add-on was not installed properly or needs authorization -> Option D

4. Quick Check:

   Missing add-on = install or authorize issue [OK]

Hint: Missing add-on? Check install and permissions [OK]

Common Mistakes:

• Assuming Google Sheets lacks add-on support
• Ignoring authorization prompts
• Thinking refresh alone fixes missing add-ons

5. You want to automate sending emails from Google Sheets using an add-on. Which of these steps is NOT required?

hard

A. Write complex scripts manually in the sheet

B. Authorize the add-on to access your Gmail account

C. Install an email automation add-on from Extensions

D. Set up the email template and recipient list in the add-on

Solution

1. Step 1: Understand add-on automation

   Add-ons automate tasks without needing manual script writing.

2. Step 2: Identify unnecessary step

   Writing complex scripts manually is not needed because the add-on handles automation.

3. Final Answer:

   Write complex scripts manually in the sheet -> Option A

4. Quick Check:

   Add-ons automate without manual scripts [OK]

Hint: Add-ons save scripting effort [OK]

Common Mistakes:

• Thinking manual scripts are needed with add-ons
• Skipping authorization steps
• Not setting up templates in add-ons