The problem

Every Monday morning you open a Google Sheet, look at the latest numbers, and build the same report you built last Monday. Copy the key metrics, paste them into an email or a Doc, add some context, format it, send it out. Next Monday, same thing.

Or maybe it's a daily operations snapshot. Or a monthly client report. Or a weekly dashboard summary for your boss. The data changes. The structure doesn't. But you rebuild it by hand every single time.

This is one of the most common time sinks in small teams. A report that takes 30 minutes to assemble, done 4 times a month, costs you 24 hours a year on a single recurring task. Most people have 3 or 4 reports like this.

Google Sheets can generate and send these reports automatically on a schedule. No manual assembly. No forgetting to send it. No Monday morning spent copying and pasting.

Skip the setup? Get the pre-built template. Weekly Report System Template $79

What we're building

A system that runs on a schedule (daily, weekly, monthly) and does the following:

  1. Reads the latest data from your Google Sheet
  2. Assembles a formatted summary (as an email body, a Google Doc, or a PDF)
  3. Sends it to one or more recipients automatically

You set it up once. It runs forever. If the data in your Sheet gets updated (manually, by a form, by another script, or by an integration), the next scheduled report reflects the latest numbers automatically.

Before you start

You need:

  1. A Google Sheet with the data you want to report on
  2. A clear picture of what the report should contain (which cells, ranges, or summary metrics)
  3. A list of who should receive it and how often

You don't need a Doc template for this one. The script builds the report content directly from the Sheet data and sends it as a formatted email.

Step-by-step setup

Step 1: Organize your Sheet data

Your Sheet needs to be structured so the script knows where to find the numbers. The simplest approach: put your key metrics in a dedicated "Summary" row or a "Dashboard" tab that aggregates from your raw data.

For example, if your Sheet tracks sales, you might have:

  • Cell B1: "Total Revenue This Week"
  • Cell B2: the SUM formula for this week's revenue
  • Cell B3: "Deals Closed"
  • Cell B4: the COUNT of closed deals
  • Cell B5: "Average Deal Size"
  • Cell B6: the AVERAGE formula

The script will read these cells and drop them into the email.

Step 2: Open the Apps Script editor

In your Google Sheet, click Extensions, then Apps Script. Delete any existing code.

Step 3: Paste the report generator script

javascript
function sendWeeklyReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Dashboard'); // change to your tab name

  // === CONFIGURATION ===
  var recipients  = 'you@company.com, boss@company.com';
  var reportTitle = 'Weekly Operations Report';
  // === END CONFIGURATION ===

  // Read your key metrics
  var totalRevenue  = sheet.getRange('B2').getValue();
  var dealsClosed   = sheet.getRange('B4').getValue();
  var avgDealSize   = sheet.getRange('B6').getValue();
  var topClient     = sheet.getRange('B8').getValue();
  var pipelineValue = sheet.getRange('B10').getValue();

  // Format currency values
  var fmt = function(n) {
return '$' + Number(n).toLocaleString('en-US', {
  minimumFractionDigits: 0,
  maximumFractionDigits: 0
});
  };

  // Build the email body
  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMMM d, yyyy');

  var htmlBody = '<div style="font-family: Arial, sans-serif; max-width: 600px;">'
+ '<h2 style="color: #1a1a1a; margin-bottom: 4px;">' + reportTitle + '</h2>'
+ '<p style="color: #6b7280; font-size: 14px; margin-top: 0;">' + today + '</p>'
+ '<hr style="border: none; border-top: 1px solid #e5e7eb; margin: 16px 0;">'
+ '<table style="width: 100%; border-collapse: collapse;">'
+ '<tr>'
+ '<td style="padding: 12px 0; font-size: 14px; color: #6b7280;">Total Revenue</td>'
+ '<td style="padding: 12px 0; font-size: 18px; font-weight: bold; text-align: right;">' + fmt(totalRevenue) + '</td>'
+ '</tr>'
+ '<tr style="border-top: 1px solid #f3f4f6;">'
+ '<td style="padding: 12px 0; font-size: 14px; color: #6b7280;">Deals Closed</td>'
+ '<td style="padding: 12px 0; font-size: 18px; font-weight: bold; text-align: right;">' + dealsClosed + '</td>'
+ '</tr>'
+ '<tr style="border-top: 1px solid #f3f4f6;">'
+ '<td style="padding: 12px 0; font-size: 14px; color: #6b7280;">Avg Deal Size</td>'
+ '<td style="padding: 12px 0; font-size: 18px; font-weight: bold; text-align: right;">' + fmt(avgDealSize) + '</td>'
+ '</tr>'
+ '<tr style="border-top: 1px solid #f3f4f6;">'
+ '<td style="padding: 12px 0; font-size: 14px; color: #6b7280;">Top Client</td>'
+ '<td style="padding: 12px 0; font-size: 18px; font-weight: bold; text-align: right;">' + topClient + '</td>'
+ '</tr>'
+ '<tr style="border-top: 1px solid #f3f4f6;">'
+ '<td style="padding: 12px 0; font-size: 14px; color: #6b7280;">Pipeline Value</td>'
+ '<td style="padding: 12px 0; font-size: 18px; font-weight: bold; text-align: right;">' + fmt(pipelineValue) + '</td>'
+ '</tr>'
+ '</table>'
+ '<hr style="border: none; border-top: 1px solid #e5e7eb; margin: 16px 0;">'
+ '<p style="color: #9ca3af; font-size: 12px;">Auto-generated from '
+ '<a href="' + ss.getUrl() + '">' + ss.getName() + '</a></p>'
+ '</div>';

  MailApp.sendEmail({
to: recipients,
subject: reportTitle + ' — ' + today,
htmlBody: htmlBody
  });
}

Step 4: Customize the metrics

This is the part you need to edit for your specific report. The script above reads 5 metrics from specific cells on a "Dashboard" tab. Change these to match your Sheet:

  1. Update the sheet name in getSheetByName() to match your tab
  2. Update each getRange() call to point to the cells containing your metrics
  3. Update the labels in the HTML table to match what each metric represents
  4. Add or remove table rows as needed

If you have 3 metrics instead of 5, delete two rows from the table. If you have 10, copy and paste the row pattern. The HTML is repetitive by design so it's easy to modify.

Step 5: Set the recipients

Change the recipients variable to the email addresses that should receive the report. Separate multiple addresses with commas.

Step 6: Set up the schedule

In the Apps Script editor:

  1. Click the clock icon in the left sidebar (Triggers)
  2. Click "Add Trigger"
  3. Set the function to sendWeeklyReport
  4. Set the event source to "Time-driven"
  5. Set the type to "Week timer"
  6. Set the day and time (for example, every Monday between 8am and 9am)
  7. Click Save

Authorize the script when prompted.

Step 7: Test it

Before waiting for the schedule, test it manually. Select sendWeeklyReport from the function dropdown in the script editor. Click Run. Check your inbox. The report email should arrive within a few seconds.

Review the formatting. If the numbers look wrong, check your cell references. If the layout needs adjustment, modify the HTML in the script.

How to extend this

Generate a PDF report instead of an email. Create a Google Doc template with placeholders for each metric. Use the same approach from the Sheets-to-PDF tutorial to populate the template and convert to PDF. Attach the PDF to the email instead of using an HTML body.

Include a chart or graph. Apps Script can export a chart from your Sheet as an image and embed it in the email. Use sheet.getCharts()[0].getAs('image/png') to get the chart as a blob, then include it as an inline image in the email using the inlineImages parameter of MailApp.sendEmail.

Send to Slack instead of email. Replace the MailApp.sendEmail block with a UrlFetchApp.fetch call to a Slack webhook URL. Format the metrics as a Slack message payload. This is useful if your team lives in Slack and ignores email.

Add conditional formatting. Check if a metric is above or below a threshold and change the text color in the HTML. For example: if revenue is below target, make it red. If it's above, make it green.

Build a monthly version. Duplicate the function, rename it sendMonthlyReport, and set a separate trigger for "Month timer." Adjust the data range to pull monthly aggregates instead of weekly.

Common issues

Email never arrives. Check your spam folder first. Then verify the recipients variable has valid email addresses. Open the script editor, click Executions in the sidebar, and check for errors.

Numbers show as 0 or undefined. The cell references in your getRange() calls are pointing to the wrong cells. Open your Sheet, click on the cell that has the metric, and note the exact cell reference (like B2). Make sure that matches what's in the script.

"Exception: Service invoked too many times." Google limits email sends to about 100 per day for consumer accounts and 1,500 for Workspace accounts. If you're sending to many recipients, consider using a single email with multiple addresses in the CC or BCC field.

Report looks good on desktop, bad on mobile. The HTML template above uses a simple table layout that works on most email clients. If you need more control, keep the max-width at 600px and use inline styles only. Email clients strip external CSS.

Schedule seems wrong. Google's time-driven triggers run within a window, not at an exact time. "Between 8am and 9am" means it could fire at 8:03 or 8:47. If you need precision, use a more specific trigger type or accept the approximate window.