How do I set up a time-driven trigger for an automated report in Apps Script?

Open `Extensions → Apps Script`, write your report function, then click `Triggers (clock icon) → Add Trigger`. Set the event source to "Time-driven," choose your interval (hourly, daily, weekly), and set the exact time window. The trigger fires the function on Google's servers — your computer doesn't need to be open. Workspace accounts get 6 hours of execution quota per day; personal accounts get 1 hour. For a daily board pack refresh running under 5 minutes, you'll never hit the limit.

Can Google Sheets automatically email a report without any code?

No. Scheduled email delivery requires Apps Script. Google Sheets has no built-in "send on schedule" feature. You need a `MailApp.sendEmail()` call inside a time-triggered script. If you want a PDF attachment, use `DriveApp.getFileById(ss.getId()).getBlob()` to export the sheet. This is a 10-15 line script for most use cases — not a project. If you want formatted HTML email instead of a PDF, write the HTML string directly into the `htmlBody` parameter of `MailApp.sendEmail()`.

Why does my IMPORTRANGE stop refreshing automatically?

`IMPORTRANGE` recalculates on open and when a dependent cell changes — it doesn't poll on a schedule. If you need it to refresh hourly without opening the file, add a helper cell with `=NOW()` (or `=RAND()`) referenced by a volatile function, then trigger a script that calls `SpreadsheetApp.flush()` on a timer. Alternatively, have the Apps Script script write fresh data to the tab directly, bypassing `IMPORTRANGE` entirely. For production reports, the script-writes-data approach is more reliable because you control when data changes and can log failures.

What's the fastest way to build an automated variance report across multiple tabs?

Structure your actuals and budget on separate tabs with identical schema: date in column A, account code in column B, cost center in column C, amount in column D. Then use `SUMIFS` with tab references on the summary sheet. Avoid VLOOKUP chains between tabs — they break when someone inserts a row. A single `SUMIFS` pulling from `'Actuals Q1'!$D:$D` with two criteria (account code + period) handles a full P&L variance in one formula block. Lock the column references with `$` and let rows be relative — the formula copies down the entire account list without editing.

How do I automate a Google Sheets report that pulls from a PostgreSQL database?

Apps Script doesn't have native PostgreSQL support. Your options are: (1) a middleware service that queries PostgreSQL and writes to Sheets via the Sheets API on a schedule, (2) a Google Cloud Function that runs on a cron schedule, or (3) an add-on like ModelMonkey that handles the connection layer. Option 1 and 2 require engineering time to set up and maintain. The Sheets API write quota is 300 writes per minute per project, which is plenty for most financial reporting use cases. See the [PostgreSQL to Google Sheets](/blog/postgresql-to-google-sheets) guide for a comparison of the approaches.

Automated Reports in Google Sheets: What Actually Works

Here's what's worth automating and what the approaches actually cost you.

What "Automated" Means in Practice

A report that recalculates when you open it isn't automated — it's just a live model. Truly automated means the data refreshes and the report formats itself without you opening the file.

Google Sheets supports this through Apps Script triggers, which can fire on a time-driven schedule (hourly, daily, weekly) and execute any script you'd run manually. As of April 2026, time-driven triggers support minimum intervals of 1 minute in Workspace accounts. The practical floor for most finance reports is daily, since the data sources (ERP exports, bank feeds, CRM snapshots) rarely update faster than that.

The catch: Google's Apps Script execution quota is 6 hours per day for Workspace accounts and 1 hour per day for personal accounts. A board pack that pulls from 8 tabs, runs 4 QUERY functions, and formats 3 summary pages will hit this if you're not careful. Keep your automated scripts under 5 minutes of runtime and you'll never see the quota error.

Formula-Based Refresh: Where It Starts

Most multi-tab financial models are already partially automated — they just don't feel like it because they require manual data paste. The gap is almost always at the import layer.

IMPORTRANGE handles cross-file pulls but recalculates lazily (on open or on-demand, not on a schedule). For a weekly variance report that reads actuals from a controller's master file, this is usually sufficient:

=SUMIFS(
  IMPORTRANGE("spreadsheet_url","P&L!C:C"),
  IMPORTRANGE("spreadsheet_url","P&L!B:B"),">="&Assumptions!$B$3,
  IMPORTRANGE("spreadsheet_url","P&L!B:B"),"<="&Assumptions!$B$4
)

The performance hit is real — IMPORTRANGE pulls the full column on each call, so wrapping it in a helper tab that pulls the range once and references it locally cuts load time from 8–12 seconds to under 2. Pull IMPORTRANGE into a raw data tab; reference that tab everywhere else.

For banks or ERPs that support CSV export, the more reliable pattern is a scheduled Apps Script that fetches the export URL, parses it, and writes to a data tab. That way your formulas reference stable sheet data instead of a live external call.

Apps Script for Scheduled Distribution

The most common automation request is "send the weekly P&L to the team automatically." Apps Script handles this cleanly. The script reads your model, formats a summary, and sends it as an email attachment — all on a trigger:

function sendWeeklyPnL() {
  const ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID');
  const summarySheet = ss.getSheetByName('Executive Summary');
  
  // Export the summary tab as PDF
  const pdfBlob = DriveApp.getFileById(ss.getId())
    .getBlob()
    .setName('Weekly P&L - ' + Utilities.formatDate(new Date(), 'GMT+8', 'yyyy-MM-dd') + '.pdf');
  
  MailApp.sendEmail({
    to: 'finance-team@company.com',
    subject: 'Weekly P&L — ' + Utilities.formatDate(new Date(), 'GMT+8', 'MMM d'),
    body: 'Weekly P&L attached. Revenue: $' + summarySheet.getRange('C3').getValue().toFixed(0) + 'K',
    attachments: [pdfBlob]
  });
}

This sends the full sheet as a PDF, not just a static screenshot. Wire it to a weekly time trigger and it runs every Monday at 7 AM without opening the file. The revenue figure in the email body pulls live from Executive Summary!C3, so the subject line itself signals whether something looks off.

One limitation worth knowing: the PDF export captures the sheet as rendered, not as printed. If your print area isn't set, you get the full grid. Set File → Print → Set Print Area once, and the trigger export respects it permanently.

The Variance Report Pattern

A quarterly board pack usually needs a variance report: actuals vs. budget, current quarter vs. prior quarter, with flagging for anything >10% off. Automating this means three things: pulling fresh actuals, calculating the variances, and formatting the exceptions.

The formula layer handles the first two. For a board pack with quarterly P&L data:

=IFERROR(
  (SUMIFS('Actuals Q1'!$D:$D,'Actuals Q1'!$B:$B,Summary!$B5,'Actuals Q1'!$C:$C,Summary!C$2)
  - SUMIFS('Budget'!$D:$D,'Budget'!$B:$B,Summary!$B5,'Budget'!$C:$C,Summary!C$2))
  / ABS(SUMIFS('Budget'!$D:$D,'Budget'!$B:$B,Summary!$B5,'Budget'!$C:$C,Summary!C$2)),
  "N/A"
)

This pulls actuals from Actuals Q1, compares to Budget, and handles the divide-by-zero case when budget is zero. The row reference (Summary!$B5) is the GL account code; the column reference (Summary!C$2) is the period. One formula, structured correctly, covers the whole P&L without VLOOKUP chains.

The exception flagging — conditional formatting that highlights anything >15% variance — runs automatically since it's rule-based. But if you want to email a list of flagged line items, that's an Apps Script job. A script can read the variance column, filter for ABS(variance) > 0.15, and write those rows to a separate "Exceptions" tab before the PDF export runs.

According to Google's Workspace documentation, conditional formatting rules evaluate in real-time with no performance penalty up to approximately 200 rules per sheet. Beyond that, you'll see lag on edit. Most variance reports stay well under this limit.

What Breaks at Scale

The failure mode for automated Google Sheets reports is almost always data volume, not formula complexity. Google Sheets caps at 10 million cells per spreadsheet. A 3-year daily actuals feed for 400 GL accounts hits 438,000 rows — manageable. But if you're storing trailing-12-month transaction-level detail, you'll hit the cap.

The second failure mode is IMPORTRANGE at scale. Each IMPORTRANGE call can reference up to 10 million cells, but more than 50 cross-file references in a spreadsheet produces noticeable degradation. Finance teams building consolidated reporting models across 20+ entity sheets often discover this around month 3, when the model starts timing out.

The fix is to collapse entity data into a single "warehouse" tab via a nightly Apps Script consolidation job, then reference only that tab from the P&L. One controlled write, many lightweight reads.

Where AI Changes the Calculation

The formula layer and Apps Script cover about 80% of what finance teams need. The remaining 20% — interpreting anomalies, describing why a number moved, pulling commentary from a CRM into the variance explanation — requires language understanding, not just computation.

ModelMonkey handles this layer inside Google Sheets: it can read your variance report, pull in context from connected data sources, and write natural-language commentary into the cells you specify. If your board pack needs a "CFO notes" section that explains why gross margin dropped from 41.2% to 38.5%, that's the kind of task that currently takes 45 minutes of manual work and can be automated.

Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.