Generate and email weekly client status updates

Clients want to know where their projects stand, but writing a status update for each one every Friday is a slow, repetitive job. The information already exists — it’s sitting in the project tracker — yet someone still has to read it, rephrase it, and paste it into an email per client. It’s the kind of task that quietly slips when the week gets busy, and a client who hears nothing assumes the worst.

Northwind automates the whole round. This script reads a Clients sheet and a Projects sheet, works out what each active client needs to hear, fills a Doc template with their specific updates, and emails every client a tailored one-page status note. Each message is personalised from real data, but nobody has to write a word.

What you’ll need

• A Clients sheet with columns name, email, and status. Only rows whose status is active are contacted.
• A Projects sheet with columns client, project, status, dueDate, and lastUpdate. The client value must match a name in the Clients sheet.
• A Google Doc template containing the placeholders {{client}}, {{updates}}, and {{nextWeek}}.
• The three file IDs, pasted into the config block.

The script

// Source spreadsheets and the Doc template used for each status note.
const CLIENTS_SHEET = '1abcClientsSheetId';
const PROJECTS_SHEET = '1abcProjectsSheetId';
const STATUS_TEMPLATE = '1abcStatusTemplateId';

// How far ahead to look when listing "what's coming next week".
const NEXT_WEEK_DAYS = 7;

/**
 * Sends every active client a tailored status update built from their
 * project rows and a Doc template. Runs on a weekly trigger.
 */
function sendWeeklyStatus() {
  // 1. Read both sheets; keep only the active clients.
  const clients = readSheet(CLIENTS_SHEET).filter((c) => c.status === 'active');
  const projects = readSheet(PROJECTS_SHEET);

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

  // 2. Build and send one update per client.
  for (const client of clients) {
    // Pull just this client's projects.
    const mine = projects.filter((p) => p.client === client.name);
    if (mine.length === 0) continue; // No projects — skip silently.

    // 3. Recent progress: any project with a lastUpdate note.
    const updates = mine
      .filter((p) => p.lastUpdate)
      .map((p) => `• ${p.project}: ${p.status} — ${p.lastUpdate}`)
      .join('\n');

    // 4. Looking ahead: projects due within the next week.
    const nextWeek = mine
      .filter((p) => isDueNextWeek(p.dueDate))
      .map((p) => `• ${p.project} (${formatDate(p.dueDate)})`)
      .join('\n');

    // 5. Render the template and email it to the client.
    const body = renderTemplate(client.name, updates, nextWeek || '— nothing major');
    GmailApp.sendEmail(
      client.email,
      `Northwind status: week of ${formatDate(new Date())}`,
      body
    );
    Logger.log('Sent status to ' + client.email);
  }
}

/**
 * Copies the Doc template, fills its placeholders, reads back the
 * finished text, then trashes the copy. Returns the plain-text body.
 */
function renderTemplate(name, updates, nextWeek) {
  const copy = DriveApp.getFileById(STATUS_TEMPLATE)
    .makeCopy(`status-${name}-${Date.now()}`);
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();

  // Swap each placeholder for the client's real data.
  body.replaceText('{{client}}', name);
  body.replaceText('{{updates}}', updates || '— nothing to report');
  body.replaceText('{{nextWeek}}', nextWeek);
  doc.saveAndClose();

  // Grab the rendered text, then bin the temporary copy.
  const text = body.getText();
  DriveApp.getFileById(copy.getId()).setTrashed(true);
  return text;
}

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

/**
 * True if a date falls within the next NEXT_WEEK_DAYS from now.
 */
function isDueNextWeek(d) {
  if (!(d instanceof Date)) return false;
  const days = (d - new Date()) / 86400000; // ms in a day
  return days > 0 && days <= NEXT_WEEK_DAYS;
}

/**
 * Formats a date as a short "d MMM" string, e.g. "5 Aug".
 */
function formatDate(d) {
  return Utilities.formatDate(d, 'GMT', 'd MMM');
}

How it works

1. sendWeeklyStatus reads both sheets with readSheet, which turns each tab into an array of objects keyed by the header row — so the code never depends on column order. It then keeps only clients whose status is active.
2. If there are no active clients, it logs a message and stops.
3. For each client, it filters the projects list down to rows whose client matches the client’s name. A client with no projects is skipped silently — no point sending an empty update.
4. The updates block lists every project that has a lastUpdate note, showing the project, its status, and the note itself.
5. The nextWeek block lists projects due within the next seven days, using isDueNextWeek to compare each dueDate against today.
6. renderTemplate copies the Doc template, replaces {{client}}, {{updates}}, and {{nextWeek}} with the client’s data, reads back the finished text, then trashes the copy. The returned string is the email body.
7. GmailApp.sendEmail sends the personalised note to the client’s address, with the current week in the subject line.

Example run

Suppose the Clients sheet has one active client, Riverside Co, and the Projects sheet holds:

client	project	status	dueDate	lastUpdate
Riverside Co	Brand refresh	In review	8 Aug	Sent draft logos Tuesday
Riverside Co	Website build	In progress	22 Aug	Homepage wireframe approved

With today being 5 August, the rendered email body reads:

Hello Riverside Co,

Here's where things stand this week:

• Brand refresh: In review — Sent draft logos Tuesday
• Website build: In progress — Homepage wireframe approved

Coming up next week:

• Brand refresh (8 Aug)

Have a good weekend,
Northwind Studios

The “Website build” line is left out of the next-week section because its 22 August due date is more than seven days away.

Trigger it

This is a weekly round, so schedule it for the end of the working week:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose sendWeeklyStatus, event source Time-driven, type Week timer, day Friday, and the 4pm–5pm slot.
4. Save, and approve the authorisation prompt — it covers Sheets, Docs, Drive, and Gmail.

Watch out for

• The template Doc is copied and trashed on every run, so the bin fills up fast. Sweep trash regularly, or skip the Doc entirely and build the body as a plain string if you don’t need the template’s formatting.
• Client matching is an exact string compare: p.client must equal c.name character for character. A stray space or a different capitalisation means that client’s projects silently won’t appear.
• dueDate must be a real date cell, not text. isDueNextWeek returns false for anything that isn’t a Date, so a text-formatted date will quietly drop out of the next-week list.
• The send loop has no rate limiting. For a handful of clients this is fine; with dozens, watch the daily GmailApp send quota and consider a short Utilities.sleep between sends.
• There’s no preview step — the script emails clients directly. Test it first by pointing the email column at your own address, or comment out the sendEmail call and check the logged bodies before going live.