🤖
What You're BuildingA two-button automation system for DataFirst Consulting's monthly reports. You'll independently replicate the classroom macro — recording GenerateReport and ResetReport, adding an Apps Script timestamp, creating two assigned buttons, and documenting your work with Version History.
Connection to ClassroomThis homework mirrors exactly what Puneet Arora demonstrated in CL-03. The difference: you build it yourself from memory on your own dataset. If you get stuck, revisit the classroom sheet.
🪜
Step-by-Step Guide1
Create Your Performance Dataset (Tab: Day5)
Add 15 rows with columns: Analyst Name, Region, Deals Closed, Revenue Generated (₹), Customer Satisfaction (1-10), Month, Performance Tier. In the Performance Tier column, use this formula in G3:
Column G — Performance Tier
=ARRAYFORMULA(
IF(D3:D17>=500000, "Top Performer",
IF(D3:D17>=300000, "Strong",
IF(D3:D17>0, "Needs Improvement", ""))))
2
Plan Your Macro Steps (Write Before Recording!)
Write this plan on paper before you touch the Record button:
GenerateReport steps:
1. Click cell A3 (don't forget!)
2. Data → Sort range → By column D (Revenue) → Z to A
3. Select A3:G5 → Format → Bold → Background (#1A3C6E) → Font white
4. Select A15:G16 → Format → Text colour → Red (#FF0000)
5. View → Freeze → Up to row 2
6. STOP recording
1. Click cell A3 (don't forget!)
2. Data → Sort range → By column D (Revenue) → Z to A
3. Select A3:G5 → Format → Bold → Background (#1A3C6E) → Font white
4. Select A15:G16 → Format → Text colour → Red (#FF0000)
5. View → Freeze → Up to row 2
6. STOP recording
3
Record the GenerateReport Macro
Go to Extensions → Macros → Record macro. Select "Use absolute references". Now execute each step from your plan — calmly and precisely. When done, click Save. Name: GenerateReport.
If You Make a Mistake While RecordingClick "Cancel" in the recording bar. This discards the recording — your sheet data is unchanged. Start fresh from Extensions → Macros → Record macro.
4
Add Timestamp in Apps Script
Go to Extensions → Apps Script. Find GenerateReport(). Add the timestamp line before the last closing brace:
function GenerateReport() {
// ... your recorded macro code ...
// Add this line:
SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Day5')
.getRange('H1')
.setValue('Report Generated On: ' + new Date());
}
Press Ctrl+S to save. Click the ▶ Run button to test — check that H1 now shows the current timestamp.
5
Create the Generate Button
Insert → Drawing → draw a rectangle → type "Generate Monthly Report" → Save and Close. Position it above your data. Click the ⋮ menu on the drawing → Assign script → type: GenerateReport → OK. Click the button to confirm it runs.
6
Record ResetReport + Create Reset Button
Record macro ResetReport with these steps:
1. Select A3:G17 → Format → Clear formatting
2. Data → Sort range → By column A (Analyst Name) → A to Z
3. Click cell H1 → Delete key
4. STOP recording → Name: ResetReport
2. Data → Sort range → By column A (Analyst Name) → A to Z
3. Click cell H1 → Delete key
4. STOP recording → Name: ResetReport
Create second Drawing labelled "Reset Report". Assign script: ResetReport. Test full cycle: Generate → verify highlights → Reset → verify clean.
7
Document with Revision History
File → Version History → See version history. Scroll to find the version BEFORE your first GenerateReport run. The pre-macro version will show no blue/red formatting in the preview. Copy that timestamp. In cell J1, type: Pre-automation snapshot: [paste timestamp].
🧬
Complete Apps Script Referencefunction GenerateReport() {
var sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Day5');
sheet.getRange(3,1,15,7).sort({column: 4, ascending: false});
sheet.getRange('A3:G5').setFontWeight('bold')
.setBackground('#1A3C6E').setFontColor('#FFFFFF');
sheet.getRange('A15:G16').setFontColor('#FF0000');
sheet.setFrozenRows(2);
sheet.getRange('H1').setValue('Report Generated On: ' + new Date());
}
function ResetReport() {
var sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Day5');
sheet.getRange('A3:G17').clearFormat();
sheet.getRange(3,1,15,7).sort({column: 1, ascending: true});
sheet.getRange('H1').clearContent();
}