Add-ons and extensions in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to automate monthly sales report generation and send it by email.

📊 Data: You have a Google Sheet with daily sales data including Date, Product, Region, and Sales Amount columns.

🎯 Deliverable: Create an automated monthly sales summary report using Google Sheets add-ons and set up an email delivery system.

Sample Data

Date	Product	Region	Sales Amount
2024-05-01	Shirt	North	150
2024-05-02	Pants	South	200
2024-05-03	Shirt	East	120
2024-05-04	Hat	West	80
2024-05-05	Shirt	North	170
2024-05-06	Pants	South	210
2024-05-07	Hat	East	90
2024-05-08	Shirt	West	130

1

Step 1: Install the 'Power Tools' add-on from the Google Workspace Marketplace to help with data summarization.

Go to Extensions > Add-ons > Get add-ons, search 'Power Tools', and install it.

Expected Result

Power Tools add-on is installed and available under Extensions > Power Tools.

2

Step 2: Use Power Tools to create a monthly sales summary pivot table.

Select data range A1:D9, then Extensions > Power Tools > Start > Pivot Table. Set Rows=Product, Columns=Region, Values=SUM of Sales Amount.

Expected Result

Pivot table shows total sales by Product and Region for the month.

3

Step 3: Install the 'Yet Another Mail Merge' (YAMM) add-on to send emails from the sheet.

Go to Extensions > Add-ons > Get add-ons, search 'Yet Another Mail Merge', and install it.

Expected Result

YAMM add-on is installed and available under Extensions > Yet Another Mail Merge.

4

Step 4: Prepare an email draft in Gmail with placeholders for sales summary data.

Create a new email in Gmail with subject 'Monthly Sales Report' and body including placeholders like {{Product}}, {{Region}}, {{Sales}}.

Expected Result

Email draft saved in Gmail with placeholders ready for mail merge.

5

Step 5: Use YAMM to send the monthly sales report to the manager automatically.

In Google Sheets, add a column with manager's email, then Extensions > Yet Another Mail Merge > Start Mail Merge. Select the draft email and run.

Expected Result

Manager receives an email with the monthly sales summary filled in.

Final Result

Monthly Sales Summary Report

+---------+-------+-------+-------+-------+
| Product | North | South | East  | West  |
+---------+-------+-------+-------+-------+
| Shirt   | 320   |       | 120   | 130   |
| Pants   |       | 410   |       |       |
| Hat     |       |       | 90    | 80    |
+---------+-------+-------+-------+-------+

Email sent to manager with this report.

✓Shirt is the top-selling product in the North region with 320 sales.

✓Pants have strong sales in the South region totaling 410.

✓Hat sales are smaller but present in East and West regions.

✓Automation saves time by sending monthly reports automatically.

Bonus Challenge

Set up a time-driven trigger to run the report generation and email sending automatically every month.

Show Hint

Use Google Apps Script editor to create a script that runs the mail merge and set a time trigger under Triggers.

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