One-Click Monthly Report Generator
Beginner
35 min
58 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.
Data Sources
https://docs.google.com/spreadsheets/d/1Bwxypr9W2zodEbWQmeoCBZPV1Cf2-9T05aaZ99xdz08/edit?usp=sharing
Solution Frameworks
Automation Framework: Identify repetitive task → Map each step → Record macro → Test → Handle edge cases → Build reset → Document.
Macro Safety Framework: Every Generate macro must have a paired Reset macro. Automated changes must always be reversible.
Button UX Framework: Button labels must state the outcome, not the action. 'Generate Monthly Report' is better than 'Run Macro'.
Macro Safety Framework: Every Generate macro must have a paired Reset macro. Automated changes must always be reversible.
Button UX Framework: Button labels must state the outcome, not the action. 'Generate Monthly Report' is better than 'Run Macro'.
Solver Guidance & Tutorials
Critical: Instruct students to plan all macro steps on paper before hitting Record. Every click is captured — accidental clicks create messy scripts.
Top/Bottom highlight tip: After sorting, rows 2-4 are top 3, rows 14-16 are bottom 2. Select via keyboard (Shift+Click) during recording for accuracy.
Apps Script access: If students cannot find the GenerateReport function, they may have recorded under a different file. Check Editor > Files list.
Button assignment is case-sensitive: 'GenerateReport' must match exactly. Even a space will cause 'Script not found' error.
Demo the Reset macro AFTER showing Generate — students immediately understand why it's needed when they see the highlighted sheet needs to be cleared.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
Top/Bottom highlight tip: After sorting, rows 2-4 are top 3, rows 14-16 are bottom 2. Select via keyboard (Shift+Click) during recording for accuracy.
Apps Script access: If students cannot find the GenerateReport function, they may have recorded under a different file. Check Editor > Files list.
Button assignment is case-sensitive: 'GenerateReport' must match exactly. Even a space will cause 'Script not found' error.
Demo the Reset macro AFTER showing Generate — students immediately understand why it's needed when they see the highlighted sheet needs to be cleared.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
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