⚡
What You're BuildingA two-button automation system: one click generates a formatted monthly report (sort, highlight, timestamp), one click resets it. You'll record a Macro to capture the steps, add a timestamp via Apps Script, and assign everything to clickable buttons.
What is a Macro?A macro is a recording of your actions in Google Sheets. Every click, sort, and format you apply while recording gets saved as a script. You can replay it with one click — or assign it to a button.
🪜
Step-by-Step Guide1
Plan Your Steps Before Recording
Before you hit Record, write down exactly what you'll do — in order. Every click is captured, including accidental ones. Here's the plan for GenerateReport:
1. Click cell A2 (top of data)
2. Data → Sort range → Sort by column D (Revenue) → Z to A (Descending)
3. Select rows 3-5 → Format → Bold → Background colour (Blue)
4. Select rows 15-17 → Format → Text colour (Red)
5. View → Freeze → Up to row 2
2. Data → Sort range → Sort by column D (Revenue) → Z to A (Descending)
3. Select rows 3-5 → Format → Bold → Background colour (Blue)
4. Select rows 15-17 → Format → Text colour (Red)
5. View → Freeze → Up to row 2
2
Record the GenerateReport Macro
Go to Extensions → Macros → Record macro. A recording bar appears at the bottom of your screen. Now perform each step from your plan (above). When done, click Save. Name it exactly: GenerateReport.
Use "Use absolute references"When the recording bar appears, select "Use absolute references" so the macro always sorts the same data range, not wherever your cursor happens to be.
3
Add a Timestamp via Apps Script
Go to Extensions → Apps Script. You'll see the auto-generated macro code. Find the GenerateReport function. Add one line before the closing brace:
Apps Script — Add this line inside GenerateReport()
function GenerateReport() {
// ... existing macro code above ...
// ADD THIS LINE:
SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Monthly_Performance')
.getRange('H1')
.setValue('Report Generated On: ' + new Date());
}
Test Before AssigningAfter adding the line, click the ▶ Run button in Apps Script editor. Check that cell H1 now shows the timestamp. If it errors, check the sheet name matches exactly.
4
Create the Generate Button
Go to Insert → Drawing. Draw a rounded rectangle. Type: Generate Monthly Report. Choose a clear font and button colour. Click Save and Close. Position the button at the top of your sheet. Now click the 3-dot menu (⋮) on the drawing → Assign script → type: GenerateReport → OK.
Case-Sensitive!The script name "GenerateReport" must match EXACTLY — capital G, capital R, no spaces. Even one typo causes "Script function not found" error.
5
Record the ResetReport Macro
Record a second macro named ResetReport that undoes everything:
1. Select all data (A3:G17)
2. Format → Clear formatting (removes bold, colours)
3. Data → Sort range → Sort by column A (Analyst Name) → A to Z
4. Click cell H1 → Delete key (clear the timestamp)
2. Format → Clear formatting (removes bold, colours)
3. Data → Sort range → Sort by column A (Analyst Name) → A to Z
4. Click cell H1 → Delete key (clear the timestamp)
Create a second button labelled Reset Report and assign ResetReport to it.
6
Use Revision History as Your Audit Log
After testing both buttons, go to File → Version History → See version history. Find the timestamp of the version BEFORE you first ran GenerateReport. Copy that timestamp and paste it into cell J1 with the label: "Pre-automation snapshot: [timestamp]".
🧬
Full Apps Script Referencefunction GenerateReport() {
var sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Day5');
// Sort by Revenue (col 4) descending
sheet.getRange(3,1,15,7).sort({column: 4, ascending: false});
// Highlight top 3 rows
sheet.getRange('A3:G5').setFontWeight('bold')
.setBackground('#1A3C6E').setFontColor('#FFFFFF');
// Red text for bottom 2
sheet.getRange('A15:G16').setFontColor('#FF0000');
// Timestamp
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();
}