One-Click Monthly Report Generator
Beginner
35 min
17 views
0 solutions
Overview
Every month, the Sales Manager manually sorts data, highlights top/bottom performers, and timestamps the report — taking 20 minutes. In this live classroom case, Puneet Arora records a Google Sheets Macro that automates this entire workflow, edits the Apps Script for a timestamp, and creates two clickable buttons for one-click execution. Students immediately repeat every step on their own sheets.
Case Details
CONTEXT: The Sales Manager at DataFirst Consulting performs the same 5 manual steps every month on their performance report. This classroom case teaches how to eliminate repetitive work entirely using Macro recording and script-assigned buttons.
INSTRUCTOR SETUP: Prepare a 'Monthly Performance' sheet with 15 rows — Analyst Name, Region, Deals Closed, Revenue Generated, Satisfaction Score, Month. This is the sheet the macro will operate on.
DEMO — STEP 1 (RECORD MACRO): Go to Extensions > Macros > Record Macro. Perform: (a) Sort table by Revenue descending, (b) Bold and blue-fill top 3 rows, (c) Red text for bottom 2 rows, (d) Freeze header. Stop and name macro 'GenerateReport'. Play it back to show students it works.
DEMO — STEP 2 (APPS SCRIPT TIMESTAMP): Open Extensions > Apps Script. Locate the auto-generated GenerateReport function. Add: sheet.getRange('H1').setValue('Report Generated On: ' + new Date()); Save and run from editor to verify H1 populates.
STUDENT REPEAT: Students record their own GenerateReport macro on their own data (5 minutes). Instructor circulates. Common issue: wrong column selected for sort — show how to check by re-running.
DEMO — STEP 3 (CREATE BUTTON): Insert > Drawing → Draw rectangle → Type 'Generate Monthly Report' → Save and Close → 3-dot menu on drawing → Assign Script → type 'GenerateReport'. Click the button live in front of class.
STUDENT REPEAT: Students create their own button and assign their macro. Instructor verifies 3-4 student buttons work before moving on.
DEMO — STEP 4 (RESET BUTTON): Record second macro 'ResetReport' — clear formatting, sort A-Z by Analyst Name, clear H1. Create a second button 'Reset Report'. Demonstrate the full workflow: Generate → Reset → Generate again.
WRAP-UP QUESTION: 'Name one task you do every week that could be automated with a Macro. What would it do?' Students share answers.
INSTRUCTOR SETUP: Prepare a 'Monthly Performance' sheet with 15 rows — Analyst Name, Region, Deals Closed, Revenue Generated, Satisfaction Score, Month. This is the sheet the macro will operate on.
DEMO — STEP 1 (RECORD MACRO): Go to Extensions > Macros > Record Macro. Perform: (a) Sort table by Revenue descending, (b) Bold and blue-fill top 3 rows, (c) Red text for bottom 2 rows, (d) Freeze header. Stop and name macro 'GenerateReport'. Play it back to show students it works.
DEMO — STEP 2 (APPS SCRIPT TIMESTAMP): Open Extensions > Apps Script. Locate the auto-generated GenerateReport function. Add: sheet.getRange('H1').setValue('Report Generated On: ' + new Date()); Save and run from editor to verify H1 populates.
STUDENT REPEAT: Students record their own GenerateReport macro on their own data (5 minutes). Instructor circulates. Common issue: wrong column selected for sort — show how to check by re-running.
DEMO — STEP 3 (CREATE BUTTON): Insert > Drawing → Draw rectangle → Type 'Generate Monthly Report' → Save and Close → 3-dot menu on drawing → Assign Script → type 'GenerateReport'. Click the button live in front of class.
STUDENT REPEAT: Students create their own button and assign their macro. Instructor verifies 3-4 student buttons work before moving on.
DEMO — STEP 4 (RESET BUTTON): Record second macro 'ResetReport' — clear formatting, sort A-Z by Analyst Name, clear H1. Create a second button 'Reset Report'. Demonstrate the full workflow: Generate → Reset → Generate again.
WRAP-UP QUESTION: 'Name one task you do every week that could be automated with a Macro. What would it do?' Students share answers.
What You'll Learn
- Problem-solving and analytical thinking
- Data-driven decision making
- Business strategy development
- Professional report writing
0
Solutions Submitted
Difficulty
Beginner
Estimated Time
35 minutes
Relevance
Fresh
Source
Google Sheets Module - Puneet Arora