Overview
Manual reporting is the bane of every SMB operator. This automation uses Google Sheets + Apps Script (or Zapier) to pull sales data from your POS, CRM, or e-commerce platform, compile it into a formatted weekly summary, and email it to your team automatically every Monday — no touching required.
Before you start
- Google Workspace account
- Basic familiarity with Google Sheets formulas
- Sales data source with Zapier integration
Step-by-step guide (5 steps)
Set up your Google Sheet reporting template
Create a Google Sheet with: raw data tab (where your sales data lands), summary tab (formulas that calculate your KPIs: total revenue, # transactions, average order value, top products).
Connect your sales source to Sheets
Use Zapier to push daily sales data into your Sheet from your POS (Square, Shopify, Toast) or CRM. Each transaction gets one row: date, amount, product, customer.
Build your summary formulas
Use SUMIF, COUNTIF, AVERAGEIF, and QUERY functions to auto-calculate your weekly KPIs. The summary tab should update automatically as new data comes in.
Use the QUERY function to pull only current-week data: =QUERY(RawData!A:E, "select B, sum(C) where A >= date '"&TEXT(TODAY()-7,"yyyy-mm-dd")&"' group by B", 1)
Set up the automated email with Apps Script
In your Google Sheet, go to Extensions > Apps Script. Write a script that reads your summary tab, formats it as an HTML email, and uses Gmail API to send it every Monday at 8am.
Set a time-based trigger
In Apps Script, set a time-based trigger: Triggers > Add Trigger > Function: sendWeeklyReport, Event: Time-driven, Week timer, Every Monday, 8am.
What you'll get
Weekly team visibility without any manual work
Consistent, timely reporting every week
Data stays in Google Sheets — easy to customize
Free to run with Google Workspace
Common mistakes to avoid
Not handling timezone differences in the Apps Script
Formulas breaking when new columns are added to raw data
Not testing the email formatting before setting the trigger
Frequently asked questions
Do I need coding experience to set up this Google Workspace automation?
No coding is required. This guide walks you through everything using Google Workspace's built-in features and Zapier's visual interface. If you can follow a recipe, you can follow this guide.
How long does this automation take to set up?
Most users complete this setup in 30–60 minutes on their first try. Once set up, it runs completely automatically with zero ongoing effort.
What happens if the automation fails?
Zapier and Make both have error notifications and task history, so you'll know immediately if something goes wrong. We cover troubleshooting steps in the guide above.
Can I customize this automation for my specific business?
Absolutely. The guide includes notes on common customizations. Most automations have multiple variation points — timing, conditions, notification recipients, and more.