How to do it
Fix the source sheet first, because everything downstream breaks if you don't
One row per job. Columns for date, customer, job type, tech, invoiced amount, paid amount. Every column gets a header, and every column holds one kind of data. Google's own QUERY documentation says that when a column has mixed types, the majority type wins and the rest get treated as nulls. That is how a revenue column with a stray 'see notes' in it silently drops rows out of your total.
Add a week column
New column, =ISOWEEKNUM(A2) filled down, or =YEAR(A2)&"-W"&TEXT(ISOWEEKNUM(A2),"00") if your data crosses a January. Grouping on a raw date gives you 300 rows. Grouping on a week number gives you 52. Do this before the pivot table, not after.
Insert the pivot table
Select the data including headers, then Insert then Pivot table. In the side panel click Add next to Rows and pick your week column, then Add next to Values and pick invoiced amount summarized by SUM. Add job type to Columns if you want the split by service line. That is the whole report.
Let it refresh itself
Google's help page is explicit: the pivot table refreshes any time you change the source data cells it is drawn from. So do not rebuild it weekly. Point your source range at whole columns, A:F rather than A1:F400, and new rows land in the report the moment they are typed.
Add the two or three numbers that actually change behavior
Above the pivot, a small block of plain formulas: jobs closed this week, average ticket, dollars invoiced but not paid. Use a Calculated field inside the pivot if the number is a ratio of two columns you already have. Everything else is decoration. A weekly report nobody acts on is a chore, not a report.
Use QUERY when the pivot table can't say it
The syntax is QUERY(data, query, [headers]). Something like =QUERY(A:F,"select B, sum(E) where A is not null group by B order by sum(E) desc limit 10",1) gives you a top-ten customer list a pivot table makes awkward. QUERY is a formula, so it lives in a cell and updates like any other formula.
Make it show up on its own
Tools then Notification settings then Edit notifications gets you an email on changes or form submissions, either right away or as a daily digest. It only notifies you, and only about other people's edits, not yours. For a real Monday morning send, use Extensions then Apps Script, write a short function that emails the range, and add a time-driven trigger set to weekly on Monday.
Where this goes wrong
The parts most guides skip, and the ones that actually cost you money:
- The Apps Script weekly trigger is not punctual. Google's trigger documentation says the time is slightly randomized: a 9 AM trigger fires somewhere between 9 and 10. Fine for a report, bad if you promised someone a 9:00 email. Set it for 7 AM if you want it read with coffee.
- Notification settings only work for you. The help page states you can only set up notifications for yourself, and you will not be notified about your own edits. Owners set this up, see nothing all week because they are the only one entering data, and assume it is broken.
- Whole-column ranges plus heavy formulas get slow. A few thousand rows is nothing. Fifty thousand rows with a QUERY per cell and volatile date functions will make the file crawl. When you get there, stop adding formulas and move the raw data into a database or a real reporting tool.
- Sheets will not fix a sloppy intake habit. If half your jobs never get logged, the report is confidently wrong, which is worse than no report. Get the row created automatically when the invoice is created. Manual entry decays within about three weeks.
Free tools for service businesses
Quotes, invoice follow-ups and review requests. No signup.
Common questions
Pivot table or QUERY?
Pivot table for anything you would describe as totals by category. QUERY when you need filtering, sorting and limits in one expression, or when the output has to feed another formula. Most weekly reports never need QUERY.
Can Google Sheets email the report as a PDF?
Not with a built-in setting. You need Apps Script: generate the PDF blob from the spreadsheet and send it with MailApp, then attach a weekly time-driven trigger. It is about 15 lines of code.
How far back should the weekly report look?
Show the last 8 to 13 weeks next to the current one. One week in isolation tells you nothing, because weather and holidays move service revenue more than anything you did.
Is this worth doing if we already use Jobber or Housecall Pro?
Only for numbers those tools do not report well, which is usually anything crossing two systems, like marketing spend against booked revenue. Do not rebuild reporting you already pay for.
Sources
- QUERY function — Google Docs Editors HelpSyntax is QUERY(data, query, [headers]); each column can hold only boolean, numeric or string values, and in a mixed column the majority data type wins while minority types are treated as nulls.
- Create and use pivot tables — Google Docs Editors HelpPivot tables are created from Insert then Pivot table with each column needing a header; the pivot table refreshes any time you change the source data cells it is drawn from; calculated fields are added from Add next to Values.
- Turn on notifications for changes — Google Docs Editors HelpTools then Notification settings then Edit notifications offers email right away or a daily digest for edits or form submissions; you can only set up notifications for yourself and you are not notified about your own changes.
- Installable triggers — Google Apps Script documentationTime-driven triggers run from every minute to once per month, are created with ScriptApp.newTrigger().timeBased(), and the fire time is slightly randomized so a 9 AM trigger runs between 9 and 10 AM.
Related guides
- How to use Gemini for weekly reports
- How to use ChatGPT to summarize job notes
- How to use QuickBooks for expenses and receipts
- How to use Zapier to screen new leads
- How to use Jobber for scheduling and dispatch
Want this built for you instead?
Tell us the job you keep doing by hand. If we can automate it, we will quote it. If we cannot, we will tell you that too. No calls, everything in writing.