Automating a Weekly Report That Fails Loudly

A weekly report that runs itself needs three parts: a scheduled job that rebuilds the numbers from raw data, a set of checks that run before the report is sent, and a failure path that is louder than the success path. The third part is the one most people skip, and it is the reason automated reports quietly go wrong for months.

The failure you should design against is not a crash. It is a report that arrives on time, looks normal, and is wrong.

Start by separating the three layers

Most spreadsheet reports mix raw data, calculations and presentation in one file. That works fine while a person is driving. It breaks the moment a schedule takes over, because there is no longer anyone to notice that row 4,102 was pasted in the wrong place.

Split the work into three files or three tabs, in this order:

• Raw. Append-only. Exports land here untouched — no formatting, no manual edits, no inserted subtotal rows.
• Model. Formulas or a query that reads Raw by column name and produces the tidy numbers.
• Report. The formatted output people read. It contains no logic of its own.

Once those are separate, the scheduled job has one job: refresh Raw, recompute Model, render Report, run checks, send. If a check fails, it sends an alert instead of a report.

Why scheduled reports break

1. Renamed and reordered columns

Someone renames "Amount" to "Amount (USD)" because it was ambiguous. Every formula pointed at the header, or worse, at column F. The report keeps running and either errors on one cell or silently reads a neighboring column.

Two defenses. First, reference by header name, not by letter, so a reorder is harmless. Second, add a schema check: before computing anything, compare the actual header row against an expected list and stop if it does not match exactly. A schema check is five lines of code and it converts the most common silent failure into a loud one.

2. Timezone drift

Scheduled jobs run in whatever timezone the platform thinks it is in, and that is often not the timezone your data is in. Three separate settings can disagree: the spreadsheet's own locale, the script or flow's timezone, and the timezone of the timestamps in the data.

In Google Apps Script, the script's timezone lives in the project manifest as a timeZone field holding a value like "America/Denver" — Google documents it as "the script time zone" in the manifest reference. That is a different setting from the spreadsheet's locale, and neither one changes what timezone your source system stamped its rows with.

Daylight saving is the other half. Microsoft's documentation for the Recurrence trigger is explicit: "If you want the trigger to honor daylight saving time (DST), make sure that you select a time zone." Without one, the docs warn, "the start time shifts one hour forward when DST starts and one hour backward when DST ends" (Microsoft Learn, checked September 2026).

Even with the timezone set correctly, do not assume the job runs at the minute you asked for. Google's Apps Script documentation says time-driven triggers can run "as frequently as every minute or as infrequently as once per month," but that the exact timing is not guaranteed — schedule a trigger for 9 a.m. and Apps Script picks a time between 9 and 10 and keeps to that offset (Installable triggers, checked September 2026).

3. Empty rows and partial exports

The nastiest version of this is a source export that returns zero rows because an API token expired or a filter matched nothing. The report renders, every total shows zero, and the chart shows a cliff. A person would call that broken. A script calls it Tuesday.

Guard it with a row-count floor. If last week had 2,400 rows and this week has 11, stop. A crude rule — fewer than 50% of the trailing four-week average — catches nearly all of these, and you can tune it later.

Make failure louder than success

Default alerting on most platforms is either off or easy to ignore. Two examples worth knowing about.

Make's incomplete executions feature — the thing that captures a failed run so you can inspect and resume it — is, per Make's Help Center, "disabled by default" and has to be turned on in scenario settings. If you never enabled it, a failed run may simply be gone.

Zapier does email on error by default, and lets you pick the cadence: "Immediately" (the default), "Immediately, then hourly summary," "Hourly summary," or "Never," which the docs themselves label as not recommended. Notifications go to the account email address. Zapier also documents that it "will not send any error notification emails when an error handler runs" — so if you added an error handler to keep things tidy, you may have muted the alarm (Zapier help, checked September 2026).

Build the alarm yourself and you avoid depending on any of it. The pattern is a heartbeat: the job writes a timestamp and a row count to a small log every time it finishes, and a second, separate job checks that log. If the last successful run is older than the expected interval plus a margin, that second job sends the alert. This catches the case platform alerts cannot — the job that never started at all.

Checks worth running before the send

Note the column on the right. "Stop" means no report goes out. Sending a report with a warning banner is a reasonable choice for soft problems like a rising null rate, but for a schema break or an empty export, silence plus an alert beats a wrong number in someone's inbox.

Limits that bite once the report grows

Spreadsheet-based reports run into platform ceilings sooner than people expect, and the failure usually appears as a timeout rather than a clear error.

Google Sheets files hold "up to 10 million cells or 18,278 columns (column ZZZ)," per Google's documented limits, checked September 2026. That sounds generous until a raw tab with 40 columns accumulates two years of daily rows alongside a few thousand formulas.

Apps Script has tighter constraints. Google publishes a script runtime limit of 6 minutes per execution for both consumer and Workspace accounts, and a total trigger runtime of 90 minutes per day for consumer accounts versus 6 hours per day for Workspace accounts (Apps Script quotas, checked September 2026). Email quotas differ the same way — 100 recipients per day on consumer accounts, 1,500 on Workspace. A weekly report that emails 40 people is fine; a daily one that emails a large list on a personal Gmail account is not.

If you use Zapier to move sheet data, note the documented file ceilings: Google Sheets triggers are limited to 30MB, actions to 50MB, per Zapier's Google Sheets guide.

An afternoon's build

1. Split the workbook into Raw, Model, Report. Delete every manual edit from Raw.
2. Write the refresh as one function that takes a date range as an argument, so you can rerun any past week by hand.
3. Add the header check and the row-count floor before any calculation runs.
4. Log every run — start time, end time, row count, pass or fail — to a plain tab.
5. Schedule the job, then schedule a second, smaller job that reads the log and shouts if the last success is too old.
6. Set the platform timezone explicitly. Write it in a comment next to the schedule so the next person knows it was deliberate.

The reason to invest the extra hour in checks is arithmetic, not principle. A wrong report that goes unnoticed for six weeks costs six weeks of decisions made on bad numbers, plus the day spent reconstructing which weeks were affected. A job that stops and emails you costs five minutes.