Auto-Generate Reports from Google Sheets with Apps Script

Repeating the same report—daily sales, weekly pipeline, monthly summaries—by hand is slow and error-prone. Auto-generating reports from Google Sheets with Apps Script lets you schedule them: the script reads sheet data (optionally filtered or aggregated), builds a report (e.g. PDF from a template or HTML email), and sends it via Gmail or saves to Drive. Time-driven triggers run daily or weekly; you control batching so many reports stay within execution and email quotas. This guide covers architecture, implementation, and scaling.

Common mistakes

• Generating one report per recipient in a long loop (execution time and Gmail limits).
• No template; building HTML or PDF from scratch in code (brittle).
• Not filtering data by date or segment before building the report.
• Assuming all reports can run in one 6-minute window.
• No error logging when email or PDF generation fails.

Architecture

Flow: Sheet(s) (source) → time-driven trigger → script reads data (filter by date/segment if needed) → build report (e.g. copy Doc/Sheet template, fill placeholders, export PDF; or build HTML body) → send email with attachment or save to Drive and share. Use a “Report config” sheet for recipients and report type. For many reports, process N per run and use a status column or queue. Execution time limits apply; chunk work and use continuation triggers if needed. Web apps can expose a “Generate report” button for on-demand runs.

Implementation

Data and template

Read from a data sheet (or multiple); filter rows by date range or segment. Use a report template (Doc or Sheet) with placeholders; copy, replace text, export PDF. For simple tables, build HTML in code and send as email body.

Scheduled send

function sendWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Sales");
  const lastRow = dataSheet.getLastRow();
  if (lastRow < 2) return;
  const numRows = lastRow - 1;
  const data = dataSheet.getRange(2, 1, 2 + numRows - 1, 5).getValues();
  var total = 0;
  for (var i = 0; i < data.length; i++) total += Number(data[i][4]) || 0;
  const html = "<h1>Weekly Sales</h1><p>Total: " + total + "</p>";
  GmailApp.sendEmail(Session.getActiveUser().getEmail(), "Weekly Sales Report", "", { htmlBody: html });
}

Example: A team sent a weekly pipeline summary to managers. Script pulled pipeline sheet, filtered by “Open,” aggregated by stage, filled a Doc template, exported PDF, and emailed to a list from a config sheet. Report generation went from ~45 minutes/week to zero.

Sending one summary report per run keeps execution under a minute; for 10+ personalized reports, batch to ~5 per run.

Scalability and quotas

Gmail send limits (e.g. 100–1,500/day depending on account), Drive create/export, and 6-minute execution apply. Batch reports (5–10 per run), use time-driven triggers, and log failures. API integrations can push report links to Slack or other systems.

Enterprise scaling

One trigger per report job; document recipients and schedule. Use Script Properties for config. Separate workbooks per department if needed. Monitor execution and email bounce logs.

FAQ