Automate Email Reports From Google Sheets With Apps Script

A weekly report is one of those tasks that never becomes easier. Someone opens the sheet, copies the rows they care about, pastes them into an email, writes the same subject line, and sends it to the same four people. It takes fifteen minutes, and it is the same fifteen minutes every week.

Apps Script removes the export step without adding a subscription. It is a code editor that lives inside your Google Sheet, it runs on Google’s servers, and it can read a range, build a small table, and email it on a schedule. This guide builds one report end to end: a Monday morning email with the last seven days of sales grouped by region. Forty-five lines of code, one trigger, and a list of checks for the week it goes quiet.

If you also maintain a summary tab by hand, the pivot table guide covers the sheet side of that job. Apps Script is only the delivery.

What Apps Script is, and when it is the wrong tool

Open a Google Sheet and look under Extensions. Apps Script sits there as a full scripting environment bound to that file, which is why getActiveSpreadsheet() already knows which spreadsheet you mean. You write JavaScript, Google runs it, and you can attach it to a clock instead of a button.

Two limits decide whether it fits the job:

• Runtime. A single execution gets 6 minutes, which is generous for a weekly summary and nowhere near enough for a heavy data pull.
• Sending. A free gmail.com account can email 100 recipients per day with a maximum of 50 per message. A Workspace account gets 1,500 recipients per day. Both figures come from the official Apps Script quotas table.

Skip this route if the report has to combine five services, if a non-technical teammate will have to maintain it, or if you need a formatted workbook sent as a file every time. A no-code tool handles those cases better, and the Zapier workflows for Sheets cover that path. Stay here if the data already lives in one sheet and the output is an email.

The report you are about to build

Assume a Sales tab with four columns:

The script filters to the last seven days, groups by region, adds a total, and sends an HTML table. Column order matters here: the script reads by position, so if you move the Region column, you update the code. If your sheet already tracks expenses rather than sales, the expense tracker template uses the same shape, and everything below still applies.

Step 1: Open the script editor

In your sheet, go to Extensions > Apps Script. A new tab opens with a file called Code.gs and an empty myFunction. Delete that stub and name the project something you will recognize later, like Weekly report. Because the script is bound to the file, you never have to paste a spreadsheet ID. Google documents the rest of the Sheets side in the Apps Script for Sheets guide.

Step 2: Set the time zone before you write code

Open Project Settings in the left sidebar and check the time zone. It is not inherited from the spreadsheet, and that mismatch is the most common reason a report says the wrong date or arrives on the wrong morning.

Two clocks are in play. The spreadsheet has its own time zone under File > Settings, which decides how dates look in cells. The script project has its own, which decides what new Date() means and how Utilities.formatDate prints it. Set both to the same zone and the report stops lying about dates.

Step 3: The report function

Replace the contents of Code.gs with this. It is a complete, working script:

const SHEET_NAME = 'Sales';
const TO = '[email protected]';

function sendWeeklyReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const values = sheet.getDataRange().getValues();
  const rows = values.slice(1); // drop the header row

  const since = new Date();
  since.setHours(0, 0, 0, 0);
  since.setDate(since.getDate() - 7);

  const recent = rows.filter(function (row) {
    return row[0] instanceof Date && row[0] >= since;
  });

  let total = 0;
  const byRegion = {};
  recent.forEach(function (row) {
    const region = row[1] || 'Unassigned';
    const amount = Number(row[3]) || 0;
    total += amount;
    byRegion[region] = (byRegion[region] || 0) + amount;
  });

  let html = '<h2>Sales, last 7 days</h2>';
  html += '<table cellpadding="6" style="border-collapse:collapse">';
  html += '<tr><th align="left">Region</th><th align="right">Amount</th></tr>';
  Object.keys(byRegion).sort().forEach(function (region) {
    html += '<tr><td>' + region + '</td><td align="right">' +
            byRegion[region].toFixed(2) + '</td></tr>';
  });
  html += '<tr><td><strong>Total</strong></td><td align="right"><strong>' +
          total.toFixed(2) + '</strong></td></tr></table>';
  html += '<p>' + recent.length + ' entries, generated ' +
          Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'EEE, MMM d, yyyy HH:mm') +
          '.</p>';

  MailApp.sendEmail({
    to: TO,
    subject: 'Weekly sales report, ' +
             Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MMM d'),
    htmlBody: html
  });
}

What each block does:

• getDataRange().getValues() returns the used range as a two-dimensional array. Dates arrive as real Date objects and numbers as numbers, which is what makes the date filter work.
• The filter keeps rows whose first cell is a date on or after midnight seven days ago. setHours(0, 0, 0, 0) drops the time component so today’s rows are never cut off by the current clock time.
• The loop builds a plain object of region totals. Grouping in code beats adding a helper column to the sheet, and it needs no formula maintenance.
• MailApp.sendEmail takes an options object. htmlBody is what makes the table render as a table; the MailApp reference lists the rest of the options, including attachments and CC.

Step 4: Run it once and approve the permissions

Select sendWeeklyReport in the toolbar dropdown and click Run. Google asks for authorization the first time, because the script reads your spreadsheet and sends mail on your behalf. On a personal account you may see an “app isn’t verified” screen: this is your own script, so continue through Advanced > Go to project.

Open the inbox and check the message. The Execution log panel shows how long the run took and prints any error text. A missing email means the script never reached the send line, and the log says why.

Step 5: Add the weekly trigger

An authorized send needs an installable trigger. Simple triggers, the ones built into the file as onOpen, cannot call services that need authorization, and they are capped at 30 seconds, as described in the triggers guide.

1. Click the clock icon in the left sidebar to open Triggers.
2. Click Add Trigger.
3. Function: sendWeeklyReport. Event source: Time-driven. Type: Week timer. Day: Monday. Time: 8am to 9am.
4. Save, and set the failure notification to daily so a broken run emails you instead of failing quietly.

One expectation to set: the hour you pick is a window, not a timestamp. Google’s documentation is explicit that the time is slightly randomized, so a recurring 8 AM trigger fires somewhere between 8 and 9 and then holds that same offset for every later run, as noted in the installable triggers guide. If a report has to land at 8:00 sharp, solve that with a different tool.

Step 6: Recipients from the sheet, not the code

Hard-coding one address is fine until someone joins the team. Keep the list in the sheet instead:

const settings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Settings');
const to = settings.getRange('B2').getValue(); // comma-separated addresses

Two habits keep this polite:

• Skip empty weeks. Add if (recent.length === 0) return; after the filter. An empty report trains people to ignore the real one.
• Prevent double sends. Store the send date and bail out if it already exists:

const props = PropertiesService.getScriptProperties();
const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
if (props.getProperty('lastSent') === stamp) return;
// after MailApp.sendEmail(...)
props.setProperty('lastSent', stamp);

That guard matters if the trigger ever fires twice, which happens after you edit and re-save a trigger while testing.

Step 7: Formatting that survives Gmail and Outlook

Email clients are not browsers. They strip style blocks, ignore most modern CSS, and render tables reliably. So:

• Inline the styles you need (cellpadding, align, a border-collapse on the table).
• Keep the layout to one table plus a heading and a paragraph.
• Avoid external stylesheets and background images pointing at local files.
• Send modest volume from your own account rather than blasting hundreds of recipients, since bulk sending rules apply at that scale: Google’s sender guidelines are the reference for what counts as bulk.

When the numbers or the email look wrong

When to stop using Apps Script

Apps Script is the right tool for one sheet going to one inbox on a clock. It is the wrong tool for scraping a vendor API, waiting on an approval step, or fanning out to a CRM, a Slack channel, and a client list at once. At that point the maintenance cost moves from code to a platform, and Make.com scenarios or a Zapier workflow is the shorter path.

For the sheet side of reporting, keep the heavy lifting in formulas and pivots, then let the script email a clean snapshot. A pivot that recalculates beats a script that rebuilds totals every Monday, and the two combine well. If the raw data sits in a separate workbook, a working IMPORTRANGE link keeps the snapshot fed without a manual paste each week.

FAQ

Is Apps Script free to use for sending reports? Yes. It ships with any Google account. The real limits are runtime and sending quotas: 6 minutes per execution, 100 recipients per day on a free account, 1,500 on Workspace, and 50 recipients per message.

Why did my report arrive at 8:40 when I scheduled it for 8 AM? Time-driven triggers are not exact. Google documents that a recurring 8 AM trigger picks a time between 8 and 9, then keeps that offset for later runs.

Can I attach the sheet as a PDF instead of an HTML table? Yes, with DriveApp.getFileById(id).getAs('application/pdf') passed as an attachment. Watch the 25 MB per-message attachment cap.

Do I need a Google Workspace account? No. A personal Google account is enough. Workspace changes the quotas and lets you send from a domain address.

Sources

• Apps Script: Extending Google Sheets
• Apps Script reference: MailApp
• Apps Script: Simple triggers and installable triggers
• Apps Script: Installable triggers
• Apps Script: Quotas for Google services
• Gmail help: Email sender guidelines

Last updated: September 21, 2026. Trigger behavior, quotas, and the MailApp options are checked against Google’s own documentation.