Email managers a weekly response summary

Northwind’s managers do not want to read every form submission, but they do want a feel for the week — how many came in, what people raised, the occasional verbatim quote that captures the mood. Doing that by hand on a Friday afternoon is a 30-minute job that always slips, and the summary nobody sends is the summary nobody reads.

This script turns a response sheet into a one-paragraph email digest every Monday morning. It filters last week’s submissions, counts them by category, pulls a handful of representative quotes, and lands it in the managers’ inbox before standup.

What you’ll need

• A Google Sheet linked to a Google Form, with the standard layout: column A is the timestamp, column B is the email or name, column C is the category, and column D is the open-text response.
• The sheet’s ID saved in RESPONSES_SHEET.
• The managers’ alias in RECIPIENTS — a Google Group works best so you can manage membership outside the script.

The script

// Sheet that captures form submissions. Column A = timestamp, C = category,
// D = open-text comment.
const RESPONSES_SHEET = '1abcResponsesId';

// Where the digest is sent. A Google Group keeps membership out of code.
const RECIPIENTS = '[email protected]';

// Look-back window in days. Seven gives a clean week.
const WINDOW_DAYS = 7;

// How many sample quotes to include per category.
const QUOTES_PER_CATEGORY = 2;

// One day in milliseconds — keeps the cutoff calculation readable.
const ONE_DAY_MS = 24 * 60 * 60 * 1000;

/**
 * Build and send a digest of the last week's form responses.
 */
function weeklyFormSummary() {
  const cutoff = new Date(Date.now() - WINDOW_DAYS * ONE_DAY_MS);

  // 1. Read every row, drop the header, and keep only rows whose timestamp
  //    falls inside the look-back window.
  const sheet = SpreadsheetApp.openById(RESPONSES_SHEET).getSheets()[0];
  const [, ...rows] = sheet.getDataRange().getValues();
  const recent = rows.filter((r) => r[0] instanceof Date && r[0] >= cutoff);

  if (!recent.length) {
    Logger.log('No responses in the last week — nothing to send.');
    return;
  }

  // 2. Count by category (column C). Missing categories become "uncategorised"
  //    so they still show up in the digest.
  const byCategory = {};
  const quotesByCategory = {};
  for (const r of recent) {
    const cat = r[2] || 'uncategorised';
    byCategory[cat] = (byCategory[cat] || 0) + 1;
    if (!quotesByCategory[cat]) quotesByCategory[cat] = [];
    const comment = r[3];
    if (comment && quotesByCategory[cat].length < QUOTES_PER_CATEGORY) {
      quotesByCategory[cat].push(String(comment).trim());
    }
  }

  // 3. Sort categories by count, descending — the busy ones go first so the
  //    managers see what mattered most without scrolling.
  const ordered = Object.entries(byCategory)
    .sort(([, a], [, b]) => b - a);

  // 4. Build the plain-text body. One block per category: count, then the
  //    sample quotes bullet-listed underneath.
  const blocks = ordered.map(([cat, count]) => {
    const quotes = (quotesByCategory[cat] || [])
      .map((q) => `  - "${q}"`)
      .join('\n');
    return `${cat}: ${count}\n${quotes}`;
  });

  const body =
    `Total responses: ${recent.length}\n` +
    `Window: last ${WINDOW_DAYS} days\n\n` +
    blocks.join('\n\n');

  // 5. Send it. One email, one summary, one inbox.
  GmailApp.sendEmail(RECIPIENTS, `Forms summary — last ${WINDOW_DAYS} days`, body);
  Logger.log(`Sent digest of ${recent.length} responses to ${RECIPIENTS}.`);
}

How it works

1. weeklyFormSummary computes a cutoff WINDOW_DAYS ago and reads every row of the response sheet, dropping the header.
2. It filters to rows whose timestamp is a Date and falls inside the window. The instance check guards against blank rows and any cells where a timestamp got pasted as text.
3. If no rows survived the filter, it logs and returns — the managers should not get a “we sent nothing this week” email.
4. It walks the rows once, counting per category and keeping the first QUOTES_PER_CATEGORY comments seen for each. One pass is enough because we never need to revisit a row.
5. It sorts categories by count descending so the busiest topics lead the email. A long tail of single-response categories sinks to the bottom.
6. It builds a plain-text body — total at the top, then one block per category with the sample quotes underneath — and sends it via GmailApp.sendEmail.

Example run

After a week of 18 responses, the email body looks like:

Total responses: 18
Window: last 7 days

billing: 7
  - "Why was I charged twice?"
  - "Refund still not through."

onboarding: 5
  - "Setup walkthrough was great."
  - "Got stuck on step 3."

feature-request: 4
  - "Could we have a dark mode?"
  - "Sortable columns please."

uncategorised: 2
  - "Thanks!"

The execution log shows: Sent digest of 18 responses to [email protected].

Trigger it

The whole point is that the digest lands on Monday morning by itself.

1. In the Apps Script editor, open Triggers (clock icon, left sidebar).
2. Click Add trigger and choose weeklyFormSummary.
3. Event source: Time-driven. Type: Week timer, Day: Every Monday, Time: 8am to 9am.
4. Save and approve the authorisation prompts.

Watch out for

• Column positions are baked in. The script reads column A as timestamp, column C as category, column D as the comment. If you reorder the form questions, the destination sheet renumbers columns and the digest goes wrong without complaint — re-check the indices after any form edit.
• Long comments make for long emails. The script trims whitespace but does not truncate. If respondents paste paragraphs, slice each quote to a sensible length (160 chars or so) before adding it to quotesByCategory.
• Sampling is biased toward the top of the sheet. The first two comments per category are always picked — that is fine for a flavour digest but a poor basis for analysis. For real clustering, use Build an AI survey-response analyzer.
• One inbox per group. Sending to a personal email instead of a Google Group means every membership change is a code change. Use an alias and let Workspace admin handle the roster.