Assemble a quarterly business review by email

At quarter-end Northwind owes every active client a business review — a short summary of what shipped, how many hours went in, and where the relationship stands. Done by hand it is a tedious afternoon of copying numbers from spreadsheets into a Doc template, exporting a PDF, and writing near-identical emails one client at a time.

This script does the whole round. For each active client it pulls their projects from a tracking Sheet, totals the shipped work and hours, fills a Doc template with those figures, exports it as a PDF, and emails it. One run on the last day of the quarter produces and sends every QBR.

What you’ll need

• A clients Sheet whose first tab has columns name, email, and status. Only rows with status set to active get a review.
• A projects Sheet whose first tab has columns client, status, and hours. The script counts rows with status of done as shipped work.
• A QBR template Google Doc containing the placeholders {{client}}, {{quarter}}, {{shipped}}, and {{hours}} wherever those values should appear.
• The file IDs of the template Doc, the clients Sheet, and the projects Sheet, taken from their URLs.

The script

// Google Doc used as the QBR template. Must contain the placeholders
// {{client}}, {{quarter}}, {{shipped}} and {{hours}}.
const QBR_TEMPLATE = '1abcQbrTemplateId';

// Sheet listing clients — needs name, email and status columns.
const CLIENTS_SHEET = '1abcClientsSheetId';

// Sheet listing projects — needs client, status and hours columns.
const PROJECTS_SHEET = '1abcProjectsSheetId';

// Name signed at the bottom of every QBR email.
const SENDER_NAME = 'Awadesh';

/**
 * Builds and emails a quarterly business review PDF for every active
 * client. Designed to run once at the end of each quarter.
 */
function sendQuarterlyReviews() {
  const quarter = currentQuarterLabel();

  // 1. Read the clients Sheet and keep only the active accounts.
  const clients = readSheet(CLIENTS_SHEET).filter((c) => c.status === 'active');

  if (!clients.length) {
    Logger.log('No active clients — nothing to send.');
    return;
  }

  // 2. For each client: gather their numbers, render a Doc, send the PDF.
  for (const client of clients) {
    const data = pullClientData(client.name);
    const file = renderDoc(client, quarter, data);

    GmailApp.sendEmail(client.email, `${client.name} — ${quarter} QBR`,
      `Hi ${client.name},\n\nHere's our quarter-end review. Happy to discuss.\n\n— ${SENDER_NAME}`, {
      attachments: [file.getAs('application/pdf')],
    });
    Logger.log('Sent ' + quarter + ' QBR to ' + client.email);
  }
}

/**
 * Totals one client's quarter: how many projects shipped and how many
 * hours were booked against them.
 */
function pullClientData(name) {
  const projects = readSheet(PROJECTS_SHEET).filter((p) => p.client === name);
  const shipped = projects.filter((p) => p.status === 'done').length;
  const hours = projects.reduce((sum, p) => sum + (p.hours || 0), 0);
  return { shipped, hours, projects };
}

/**
 * Copies the QBR template, swaps the placeholders for this client's
 * figures, and returns the saved Doc file.
 */
function renderDoc(client, quarter, data) {
  // Work on a copy so the master template is never modified.
  const copy = DriveApp.getFileById(QBR_TEMPLATE).makeCopy(`${client.name} — ${quarter}`);
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();

  // Replace every placeholder with the real value.
  body.replaceText('{{client}}', client.name);
  body.replaceText('{{quarter}}', quarter);
  body.replaceText('{{shipped}}', String(data.shipped));
  body.replaceText('{{hours}}', String(data.hours));

  doc.saveAndClose();
  return copy;
}

/**
 * Returns a label for the current quarter, e.g. "Q2 2026".
 */
function currentQuarterLabel() {
  const d = new Date();
  return `Q${Math.floor(d.getMonth() / 3) + 1} ${d.getFullYear()}`;
}

/**
 * Reads the first tab of a Sheet into an array of plain objects, keyed
 * by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id).getSheets()[0].getDataRange().getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. sendQuarterlyReviews works out the quarter label, then reads the clients Sheet and filters it down to rows with status of active. If none are active it logs a message and stops.
2. For each active client it calls pullClientData, which reads the projects Sheet, keeps only that client’s rows, counts the ones marked done, and sums the hours column.
3. renderDoc makes a copy of the template Doc — never touching the master — opens the copy, and replaces the four placeholders with the client name, quarter, shipped count, and total hours.
4. Back in the loop, file.getAs('application/pdf') converts the finished Doc to a PDF on the fly and GmailApp.sendEmail sends it as an attachment with a short personalised message.
5. readSheet is the shared helper: it reads a Sheet’s data range and turns each row into an object keyed by the header, so the rest of the code can use client.name instead of column indexes.

Example run

Given a clients Sheet with two active accounts and a projects Sheet recording their work for the quarter:

Client	Active?	Projects done	Hours booked
Harbour Co	active	3	84
Lumen Ltd	active	1	22
Old Mill	inactive	—	—

A run on 30 June produces two PDFs — Harbour Co — Q2 2026 and Lumen Ltd — Q2 2026 — each filled with that client’s figures, and emails them. Old Mill is skipped because it is not active.

Trigger it

Apps Script has no native quarterly recurrence, so set four fixed time-based triggers — one per quarter-end:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger, choose sendQuarterlyReviews, event source Time-driven, type Specific date and time, and enter 31 March.
3. Repeat for 30 June, 30 September, and 31 December.
4. Approve the Gmail, Drive, and spreadsheet authorisation prompts on the first run.

Watch out for

• Each run leaves a copied Doc in your Drive. Over a year that is dozens of files — move them to a dated archive folder afterwards, or call copy.setTrashed(true) once the PDF has been sent.
• A placeholder with no matching value stays in the PDF as literal {{...}} text. Make sure every client row has a name and email.
• GmailApp.sendEmail counts against the daily send quota — 100 messages a day on a consumer account, 1,500 on Workspace. A large client list could hit that ceiling in one run.
• The script trusts the spreadsheet data. A blank hours cell is treated as zero, but a non-numeric value would break the reduce total — keep that column clean.
• A long client list can push the run past the six-minute execution limit. If that happens, process clients in batches and store progress in Script Properties between runs.