How to automate your weekly report with Google Sheets and get Monday mornings back

Where the hours go in a weekly report

Example only. Two hours a week is 104 hours a year. At AED 45 an hour that one report costs AED 4,680 a year, before anyone has made a decision from it.

Step 1: one tab per data source

Give every source its own raw tab and never edit it by hand. Many tools can send data to Google Sheets on a schedule, directly or through a connector. Data that already lives in another spreadsheet can be pulled in with IMPORTRANGE. The goal is simple: no more copying.

Step 2: a clean data tab

Use formulas such as QUERY, FILTER and SUMIFS to turn raw tabs into one tidy table with consistent dates, sources and owners. ISOWEEKNUM gives each row a week number, so "this week" and "last week" calculate themselves.

Step 3: build the report once

Create a report tab that reads only from the clean tab: this week, last week, the change, and the target. Add conditional formatting so numbers that need attention stand out. From now on, nobody touches this tab except to improve it.

Step 4: let it send itself

• Apps Script: a short script can email the report or a PDF of it. A time driven trigger runs it on a chosen weekday and hour. Google notes the exact time is chosen within that hour, so set it an hour before you need it.
• Looker Studio: connect a dashboard to the sheet and use its scheduled email delivery.

Step 5: add an AI summary, carefully

Once the numbers are right, AI can draft three to five plain sentences on what changed and why it might matter. Rules: it reads only the report data, it never invents numbers, and a person checks it before it goes out. This step removes the last 20 minutes of the job.

Checks that keep it trustworthy

• Run the automated report next to the manual one for two weeks and compare totals.
• Show a warning if any raw tab is empty or older than expected.
• Keep one owner who fixes it when a source changes its export format.

Not sure the report is your biggest time sink? Find out with the one week time audit.