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.