Level 3CapstonesProject, 12 min
Capstone: an automated weekly report
The task
Sales and marketing log every day's results on the Log tab. Build a Report tab that always shows the latest week, with no dates typed by hand: it finds the week, summarizes it, totals it, compares it with last week and writes its own headline. Add an Apps Script trigger to email it every Monday and nobody has to touch it again.
- Find the latest weekOn Report, in B1, find the Monday of the week holding the latest date in the log. Name the latest date with LET, then step back to Monday:
=LET(last, MAX(Log!A2:A15), last - WEEKDAY(last, 2) + 1). - This week by channelIn A3, FILTER the log to rows on or after B1, then QUERY the result by channel. Filtered data has no header row, so name columns Col1, Col2... and pass 0 as the third input:
=QUERY(FILTER(Log!A2:E15, Log!A2:A15>=B1), "select Col2, sum(Col3), sum(Col5) group by Col2 order by sum(Col5) desc label Col2 'Channel', sum(Col3) 'Leads', sum(Col5) 'Revenue'", 0) - A total row with HSTACKIn A8, right under the table, build the total row in one formula: the word Total, this week's leads and this week's revenue, side by side with
HSTACK. Use SUMIFS on the log with">="&B1as the date test. - Week over weekIn B10, compare this week's revenue with last week's (the seven days before B1): this week divided by last week, minus 1.
- A headline that writes itselfIn B12, join the pieces into one sentence with & and TEXT, so it reads exactly: Week of Oct 19: $21,650 revenue, 30% vs last week
The data, 2 tabs
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | |
|---|---|---|
| 1 | Week starting | |
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | vs last week | |
| 11 | ||
| 12 | Headline | |
| 13 | ||
| 14 |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Channel | Leads | Deals | Revenue |
| 2 | Oct 6 | Paid Search | 42 | 5 | $6,200 |
| 3 | Oct 7 | Paid Social | 35 | 3 | $3,900 |
| 4 | Oct 8 | 18 | 4 | $4,100 | |
| 5 | Oct 9 | Referral | 9 | 2 | $3,300 |
| 6 | Oct 13 | Paid Search | 39 | 4 | $5,400 |
| 7 | Oct 14 | Paid Social | 41 | 5 | $5,800 |
| 8 | Oct 14 | 21 | 3 | $3,600 | |
| 9 | Oct 16 | Referral | 7 | 1 | $1,900 |
| 10 | Oct 20 | Paid Search | 47 | 6 | $7,100 |
| 11 | Oct 21 | Paid Social | 33 | 2 | $2,700 |
| 12 | Oct 21 | 25 | 5 | $5,300 | |
| 13 | Oct 22 | Paid Search | 12 | 1 | $1,450 |
| 14 | Oct 23 | Referral | 11 | 3 | $4,200 |
| 15 | Oct 23 | 6 | 1 | $900 |