Automations

How to automate weekly reports by email

The short answer

Pick three to five numbers, fix the window to last Monday to Sunday in your timezone, and email them every Monday against the week before. Make the report say when data is missing, never a silent zero.

4 min read

Which tool should send it?

Where do your numbers live?

Use Looker Studio's scheduled delivery

It sends a PDF and a link to up to 50 recipients a schedule. Reports with errors are not sent, so watch for a missing Monday.

Automation

Use Apps Script, or an automation

Apps Script needs a little code and a time trigger, with 6 minutes per run. An automation tool can read the sheet and send the email instead.

Should the report explain what changed?
Automation

Use an automation on a schedule

Fixed steps read the numbers and send them. Check which timezone the schedule runs in.

AI agent

Use an AI agent on a schedule

It reads, compares and writes why the number moved. It costs more per run than fixed steps; worth it when the why matters.

See the ready-made version: Stripe weekly revenue report by email

Decide four things before you build

DecideGood answerWhy
The numbers3 to 5 you would act onTwenty numbers get skimmed, then ignored
The windowMonday to Sunday, your timezone"Last 7 days" shifts with send time
The comparisonThe week beforeA change says more than a number
The wordsOne line on what changedWhat an AI step adds

How to automate weekly reports by email, step by step

  1. Step 1: Write the report by hand once

    Open the source, pull last week's numbers, and write the email you would want. That email is your spec.

  2. Step 2: Fix the window in words

    "Payments from last Monday 00:00 to Sunday 23:59, Europe/London". Print the window and timezone in the report itself.

    Watch out. A schedule set in UTC moves an hour when daylight saving starts. Set it in your own timezone.

  3. Step 3: Schedule it after the data settles

    If payouts or ad spend arrive overnight, send at 9:00, not 0:05.

    Do this step in 50agents

    Set up an AI agent that reads last week's payments in Stripe every Monday at 9:00 and emails me the totals and what changed

    Build it

    Captain plans an AI agent that reads, compares and writes the summary each Monday. You see when it runs and what it asks you first before it is built. See the ready-made version.

  4. Step 4: Make empty and failed weeks visible

    If a source returns nothing, the email says "no payments found between X and Y", not "$0". If a step fails, you hear about it the same morning.

  5. Step 5: Send to yourself for two weeks

    Check each one against the source by hand. Then add the team.

What can go wrong

  • The report says $0

    The data was late or the search found nothing. Make the report say what it searched and that it found nothing.

  • The window is off by a day

    "Last 7 days" counted from the send time. Use calendar weeks in your timezone.

  • It failed and nobody noticed

    A connection expired and the report simply stopped. Pick a tool that tells you about failed runs, and notice the Monday it does not arrive.

  • The person who built it left

    The schedule ran from their account. Build reports on a shared account, or write down who owns them.

Questions people ask

Can Google Sheets email a report on a schedule?

Not by itself. Apps Script can, with a time trigger and a little code, or an automation tool can read the sheet and send the email.

Can Looker Studio email a report weekly?

Yes. Scheduled delivery sends a PDF and a link to up to 50 recipients; free schedules can run at most daily.

What timezone does a scheduled report use?

It depends on the tool: Looker Studio uses your computer's timezone, Zapier uses the account's. Always check, and print the window in the report.

How many numbers should a weekly report have?

Three to five you would act on, each against the week before. Twenty numbers get skimmed, then ignored. One line on what changed helps more than another chart.

What if a data source is down on Monday?

The report should say so: "no payments found between X and Y", not "$0". If a step fails, you should hear about it the same morning, not notice a quiet inbox.

Who should get the report first?

You, for two weeks. Check each one against the source by hand, then add the team. A wrong number in front of everyone costs more trust than a late report.

Read next

Or let Captain build it

One sentence to Captain. Free to start.

Build it with Captain
How to automate weekly reports by emailBuild it