How to Automate Google Sheets Reporting With Apps Script

Many small teams produce the same report every week by hand: filter the orders tab, total by region, paste the numbers into a summary, export a PDF, and email it to the owner. It takes twenty minutes when everything goes well, and it gets skipped or done wrong when someone is out.

Google Apps Script can do this entire routine on a schedule, using services that already come with a Google account. This tutorial builds a working weekly report step by step: structuring the source data, reading it efficiently, building a summary, exporting a PDF to Drive, emailing it, scheduling it with a time-driven trigger, and alerting someone when it fails. The code uses only documented Apps Script methods, and each part is explained so you can adapt it rather than paste it blindly.

What you will build

The finished script runs every Monday morning and does five things:

1. Reads every row from an “Orders” sheet in one call.
2. Totals orders and revenue by region for the previous seven days, skipping cancelled and malformed rows.
3. Writes the totals to a “Weekly Summary” tab.
4. Saves a PDF copy of the summary in a Drive folder.
5. Emails the summary and PDF to the report recipients, and emails an alert address if anything fails.

Work on a copy of your spreadsheet while you build and test this. The script clears and rewrites a tab and sends email, and neither should be tested against the file your team relies on.

Step 1: Structure the source data

Most reporting scripts break because of the data, not the code. Before writing anything, make the source sheet predictable:

• One header row, one record per row. No merged cells, subtotals, or blank spacer rows inside the data.
• Stable header names. The script below finds columns by header text (Order Date, Region, Amount, Status), so someone inserting a column won’t break it. Renaming a header will, and the script is written to fail loudly when that happens.
• Real dates and numbers. A date typed as text (“Sept 3”) is a string to Apps Script, not a Date. Use data validation or a date format on the column so values are stored as dates.
• A controlled status list. A dropdown with values such as Paid and Cancelled prevents “cancelled”, “Canceled”, and “CANCELLED” from being counted three different ways.

Step 2: Read the data in one batch

Google’s Apps Script best practices recommend minimizing calls to other services and using batch operations. Every getValue() call inside a loop is a separate round trip to Sheets. Reading the whole range once with getValues() returns a two-dimensional array that you can process in plain JavaScript, which is far faster. Google’s own comparison shows a batched version finishing in about a second where a cell-by-cell version took over a minute.

Open Extensions > Apps Script from your spreadsheet and add this configuration and summary function:

const CONFIG = {
  sourceSheet: 'Orders',
  summarySheet: 'Weekly Summary',
  recipients: 'owner@example.com',        // comma-separated for several
  alertEmail: 'ops@example.com',
  reportFolderId: 'YOUR_DRIVE_FOLDER_ID'
};

function buildWeeklySummary() {
  const ss = SpreadsheetApp.getActive();
  const source = ss.getSheetByName(CONFIG.sourceSheet);
  if (!source) throw new Error('Missing sheet: ' + CONFIG.sourceSheet);

  const values = source.getDataRange().getValues();   // one read
  const header = values.shift();
  const col = {
    date: header.indexOf('Order Date'),
    region: header.indexOf('Region'),
    amount: header.indexOf('Amount'),
    status: header.indexOf('Status')
  };
  Object.keys(col).forEach(function (key) {
    if (col[key] === -1) throw new Error('Missing column for: ' + key);
  });

  const end = new Date();
  const start = new Date(end.getTime() - 7 * 24 * 60 * 60 * 1000);
  const totals = {};
  let skipped = 0;

  values.forEach(function (row) {
    const date = row[col.date];
    const rawAmount = row[col.amount];
    const amount = Number(rawAmount);
    if (!(date instanceof Date) || rawAmount === '' || isNaN(amount)) {
      skipped++;
      return;
    }
    if (date < start || date >= end) return;
    if (String(row[col.status]).trim() === 'Cancelled') return;

    const region = String(row[col.region]).trim() || 'Unassigned';
    if (!totals[region]) totals[region] = { orders: 0, revenue: 0 };
    totals[region].orders += 1;
    totals[region].revenue += amount;
  });

  const output = [['Region', 'Orders', 'Revenue']];
  Object.keys(totals).sort().forEach(function (region) {
    output.push([region, totals[region].orders, totals[region].revenue]);
  });

  const summary = ss.getSheetByName(CONFIG.summarySheet) ||
      ss.insertSheet(CONFIG.summarySheet);
  summary.clearContents();
  summary.getRange(1, 1, output.length, output[0].length).setValues(output); // one write

  return { output: output, start: start, end: end, skipped: skipped };
}

Step 3: Understand the summary logic

A few decisions in that function are worth understanding before you change it:

• values.shift() removes the header row from the array so the loop only sees records, while keeping the header for column lookup.
• Malformed rows are counted, not ignored silently. A row without a real date or with a blank or non-numeric amount increments skipped. The email reports that number, so a data-entry problem shows up in the report instead of quietly lowering the totals. Note that Number('') is 0 in JavaScript, which is why blank amounts are checked explicitly.
• The date window is a rolling seven days ending when the script runs. If you need calendar weeks (Monday through Sunday) or a specific time zone boundary, compute start and end accordingly. The script’s time zone is set in Project Settings, and the spreadsheet has its own time zone under File > Settings. Keep them the same to avoid off-by-one-day totals.
• setValues() needs matching dimensions. The target range is sized from the output array itself (output.length rows by output[0].length columns), which avoids the common mismatch error.
• clearContents() removes old values but keeps formatting, so you can style the summary tab once and the script won’t undo it.

Run buildWeeklySummary from the editor once. Apps Script will ask for permission to access your spreadsheet. Check the Weekly Summary tab against a manual filter of the same week before going further.

Step 4: Export the summary as a PDF

The Spreadsheet class has a getAs(contentType) method that converts the file to a blob such as a PDF. It converts the spreadsheet file, not one tab, so the simplest way to get a clean, one-table PDF is to write the summary into a small temporary spreadsheet, convert that, and then trash the temporary file.

function exportSummaryPdf(result) {
  const tz = Session.getScriptTimeZone();
  const label = Utilities.formatDate(result.end, tz, 'yyyy-MM-dd');

  const temp = SpreadsheetApp.create('Weekly Summary ' + label);
  temp.getSheets()[0]
      .getRange(1, 1, result.output.length, result.output[0].length)
      .setValues(result.output);
  SpreadsheetApp.flush();   // make sure the values are written before export

  const pdf = temp.getAs(MimeType.PDF);
  const file = DriveApp.getFolderById(CONFIG.reportFolderId).createFile(pdf);

  DriveApp.getFileById(temp.getId()).setTrashed(true);
  return file;
}

Some details that matter:

• SpreadsheetApp.flush() applies pending changes. Without it, Apps Script may batch the write and the export could run before the data is in place.
• File naming. The reference notes that getAs treats anything after the last period in the name as an extension and replaces it. A date format like yyyy.MM.dd would produce a truncated name, which is why the example uses hyphens.
• Quotas. Creating a spreadsheet counts toward a daily limit (250 per day for consumer accounts at the time of writing). A weekly report won’t come close, but a script that creates one file per row could.
• Folder access. Replace YOUR_DRIVE_FOLDER_ID with the ID from the folder’s URL. The account that owns the trigger must be able to add files to that folder.

If you don’t need an archived PDF, skip this step and send the summary in the email body only. It is one less thing that can fail.

Step 5: Email the report and alert on failure

For sending only, MailApp is a good fit. Unlike GmailApp, it can’t read your inbox, so it requests a narrower permission. Its getRemainingDailyQuota() method returns how many more recipients the account can email today.

function sendWeeklyReport() {
  try {
    const result = buildWeeklySummary();
    const file = exportSummaryPdf(result);

    const recipientCount = CONFIG.recipients.split(',').length;
    if (MailApp.getRemainingDailyQuota() < recipientCount) {
      throw new Error('Not enough email quota left today.');
    }

    const tz = Session.getScriptTimeZone();
    const lines = result.output.slice(1).map(function (r) {
      return r[0] + ': ' + r[1] + ' orders, ' + r[2].toFixed(2);
    });
    const body = [
      'Orders from ' + Utilities.formatDate(result.start, tz, 'MMM d') +
        ' to ' + Utilities.formatDate(result.end, tz, 'MMM d, yyyy'),
      '',
      lines.length ? lines.join('\n') : 'No orders in this period.',
      '',
      'Rows skipped because of missing or invalid data: ' + result.skipped,
      'PDF archive: ' + file.getUrl()
    ].join('\n');

    MailApp.sendEmail({
      to: CONFIG.recipients,
      subject: 'Weekly order summary',
      body: body,
      attachments: [file.getBlob()]
    });
    console.log('Report sent. Skipped rows: ' + result.skipped);
  } catch (err) {
    console.error('Weekly report failed: ' + (err.stack || err));
    MailApp.sendEmail(CONFIG.alertEmail, 'Weekly report failed',
        'The scheduled report did not complete.\n\n' + (err.stack || err));
    throw err;   // keep the execution marked as Failed
  }
}

The catch block sends a plain alert and then re-throws the error. Re-throwing is deliberate: it keeps the run marked as Failed in the Executions list and in Google’s trigger failure emails, so you have two independent signals instead of a report that silently never arrives. If the failure is itself an email quota problem, the alert email may also fail, which is another reason not to rely on it alone.

Step 6: Schedule it with a time-driven trigger

Run this setup function once from the editor, signed in as the account that should own the schedule. It removes any existing trigger for the same function first, so running it twice doesn’t create duplicate reports.

function installWeeklyTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'sendWeeklyReport') {
      ScriptApp.deleteTrigger(t);
    }
  });
  ScriptApp.newTrigger('sendWeeklyReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(7)
    .create();
}

Three things to expect, based on Google’s installable trigger documentation:

• It won’t run at exactly 7:00. Apps Script picks a time within the hour and keeps it fairly consistent. Plan around a window, not a minute.
• It runs as you. Installable triggers run under the account that created them, using that account’s permissions and quotas. If that person leaves, the report stops. Use an account the business controls.
• Hours follow the script’s time zone unless you set another one with inTimezone() on the ClockTriggerBuilder.

After installing, open the Triggers page in the editor and confirm the trigger is listed. When you edit a trigger there, you can also choose how often Google emails you about failed runs.

Step 7: Stay within quotas and time limits

The quotas page is the reference for limits, and Google states they can change without notice. The ones most relevant to reporting at the time of writing:

If a report approaches the 6-minute limit, the batch read in Step 2 is usually the first fix. For very large sources, Google suggests saving progress with the Properties service and continuing in a later triggered run, or moving the data to a database.

Testing before you rely on it

• Point CONFIG.recipients at your own address while testing.
• Add test rows that should be excluded: a cancelled order, a row with text in the Amount column, and a row older than seven days. Confirm the totals and the skipped count.
• Rename a header on your copy and confirm the run fails with a clear message and the alert email arrives.
• Run sendWeeklyReport from the editor, then check the Executions page for a Completed entry and your log line.
• After the first scheduled Monday, confirm the entry in Executions shows the type Time Driven.

Common mistakes

• Reading or writing cell by cell inside a loop, which is the usual cause of timeouts.
• Letting the script and spreadsheet time zones differ, which shifts date boundaries.
• Running the trigger setup function several times without removing old triggers, which sends duplicate reports.
• Catching errors without re-throwing them, which hides failures.
• Hardcoding column positions, so one inserted column breaks the totals without any error.

If a trigger you set up stops running, our guide Google Apps Script Trigger Not Working? Causes and Fixes covers authorization, ownership, and quota problems in detail. If the report data comes from other tools, CRM Data Not Syncing Between Applications? may help with the upstream side. When you want the reporting built and maintained for you, see our Google Sheets and Apps Script development service.

FAQ

Can Apps Script email a PDF of just one sheet tab?

The documented getAs() method converts the whole spreadsheet file. Copying the summary into a temporary spreadsheet, as shown above, is a straightforward way to produce a PDF that contains only the report.

Should I use MailApp or GmailApp?

For sending reports, MailApp is usually enough and requests a narrower permission. GmailApp is useful when you also need to work with the mailbox, such as searching threads or creating drafts. Both are subject to the daily recipient quota.

Why did my scheduled report arrive at 7:40 instead of 7:00?

Time-driven triggers run at a time Apps Script chooses within the hour you specified. If you need exact timing, account for that window in how you communicate the report.

Will the report keep running if I’m not logged in?

Yes. Installable triggers run on Google’s servers under the creator’s account without the spreadsheet being open. They stop if that account loses access or authorization.

How large can the source data be?

There is no single row limit for this approach; the practical constraint is the 6-minute execution limit. Batch reads handle large sheets well, and very large datasets are better served by processing in chunks or moving to a database.

Sources

• Best Practices — Google Apps Script
• Class Spreadsheet — Google Apps Script
• Class SpreadsheetApp — Google Apps Script
• Class File (Drive) — Google Apps Script
• Class MailApp — Google Apps Script
• Installable Triggers — Google Apps Script
• ClockTriggerBuilder reference
• Quotas for Google Services — Google Apps Script