The Monday report problem: how to automate a weekly Excel or Google Sheets report

Every Monday morning, somewhere in your business, someone opens five exports, pastes them into a workbook, fixes whichever formula broke last week, saves a PDF and emails it to the team. It takes a few hours, it depends on one person, and a single row pasted in the wrong place quietly changes the numbers everyone makes decisions on.

This is the job we automate more than any other. The report itself is rarely the problem. The manual steps around it are, and those are exactly what software is good at. Here is the approach we use, whether the report lives in Excel or Google Sheets.

1. Write down what the report actually does

Before touching a formula, describe the job in plain English. Most weekly reports fit in five lines:

This short spec does two things. It exposes manual steps nobody realised were there, and it gives you a clear standard to test the automated version against.

2. Get the data in without copy and paste

Copy and paste is where most errors begin, so it is the first thing to remove.

• In Excel, Power Query (Data → Get Data) pulls from CSV files, whole folders of exports, SharePoint lists, databases and many web sources. Point it at the folder where exports land and it combines every new file automatically. Refreshing becomes one click, or one line of code.
• In Google Sheets, IMPORTRANGE pulls from other spreadsheets, and Apps Script can fetch data from almost any system with an API or read attachments arriving under a Gmail label.

If a system can only be exported by hand, keep that single step and automate everything after it. Removing four manual steps out of five is still a big win.

3. Separate raw data, logic and presentation

Fragile workbooks mix everything on one sheet. Reliable ones keep three layers apart:

1. Raw data tabs that are only ever replaced by the import and never edited by hand.
2. Calculation tabs built on Excel Tables or named ranges, so formulas refer to column names instead of addresses like D2:D418 that break as data grows.
3. A report tab that only reads from the calculations and is laid out for printing or PDF.

Prefer XLOOKUP or INDEX/MATCH to VLOOKUP with hard-coded column numbers, add data validation wherever people still type, and protect the report tab so nobody “fixes” a total by typing over it.

4. Automate the last mile: refresh, export, send

Once data flows in and the logic is solid, the final steps can run on their own.

Excel with VBA

A short macro refreshes the queries, saves the report tab as a PDF and emails it through Outlook:

Sub SendWeeklyReport()
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone

    Dim pdfPath As String
    pdfPath = ThisWorkbook.Path & "\Weekly report " & Format(Date, "yyyy-mm-dd") & ".pdf"
    ThisWorkbook.Worksheets("Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

    Dim olApp As Object, mail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.CreateItem(0)
    With mail
        .To = "team@yourcompany.com"
        .Subject = "Weekly report " & Format(Date, "d mmm yyyy")
        .Body = "This week's report is attached."
        .Attachments.Add pdfPath
        .Send
    End With
End Sub

Turn off “Enable background refresh” in each query’s properties so the macro waits for fresh data before exporting. The macro can run from a button, when the workbook opens, or on a schedule through Windows Task Scheduler. Teams on Microsoft 365 can achieve the same with Office Scripts and Power Automate, without leaving a computer switched on.

Google Sheets with Apps Script

Apps Script can export a single tab as a PDF, email it, and run itself every Monday on a time-driven trigger:

function sendWeeklyReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Report");
  var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() +
    "/export?format=pdf&gid=" + sheet.getSheetId() + "&portrait=false&fitw=true";
  var pdf = UrlFetchApp.fetch(url, {
    headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() }
  }).getBlob().setName("Weekly report.pdf");

  GmailApp.sendEmail("team@yourcompany.com", "Weekly report",
    "This week's report is attached.", { attachments: [pdf] });
}

function createMondayTrigger() {
  ScriptApp.newTrigger("sendWeeklyReport")
    .timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(7).create();
}

Run createMondayTrigger once and the report sends itself every week, whether or not anyone’s computer is on.

5. Make it fail loudly, not quietly

An automated report that is silently wrong is worse than a manual one. Build in a few simple checks:

• A “last refreshed” timestamp printed on the report page.
• Row counts compared with the previous week, with a warning when the change is extreme.
• A reconciliation line, such as total hours imported equals total hours allocated to jobs.
• If any check fails, send an alert to the owner instead of sending the report.

Excel or Google Sheets?

The honest answer is usually whichever one your team already lives in. Moving people is harder than moving formulas.

What this looks like in practice

For a US industrial services company, weekly payroll was being rebuilt by hand every single week. Rebuilding the workbook around clean imports, structured calculations and a one-click output turned a job that took hours into one that takes minutes, and removed the risk of a pasted row changing someone’s pay.

Most spreadsheet automations like this are delivered in one to three weeks, on a fixed quote agreed before any work starts.

Have a report that eats your Monday? Send it to us for a free audit and we will tell you exactly what can run itself.