Automating Weekly Reports with Apps Script Triggers

A weekly report is more about collection than content. Gathering numbers scattered across places, formatting them the same way, and sending them out — that repeats every week. Move the repetition onto a trigger, and what’s left for the person is reading and judging the report.

This is a demonstration draft.

A time-based trigger

Apps Script lets you create triggers in code. For a report that runs Monday mornings, you set it up like this.

function installWeeklyTrigger() {
  ScriptApp.newTrigger("buildWeeklyReport")
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(8)
    .create();
}

Creating triggers in code means they reproduce without any clicking when you move to another environment.

Funnel collection into one shape

The heart of a report is “which numbers, from where, and how.” Even with several sources, unifying the collection into a single shape makes everything downstream easier.

function collectMetrics() {
  return {
    week: weekLabel(),
    inquiries: countRows("Inquiries"),
    delivered: countRows("Done"),
    pending: countRows("Pending"),
  };
}

function countRows(sheetName) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  return Math.max(0, sheet.getLastRow() - 1); // exclude header
}

Output to a document

Fill the collected values into a Google Docs template. Define placeholders and substitute them, and the format stays consistent week to week.

function buildWeeklyReport() {
  const metrics = collectMetrics();
  const doc = DocumentApp.create(`Weekly Report — ${metrics.week}`);
  const body = doc.getBody();
  body.appendParagraph(`Weekly Report (${metrics.week})`).setHeading(
    DocumentApp.ParagraphHeading.HEADING1,
  );
  body.appendParagraph(`Inquiries: ${metrics.inquiries}`);
  body.appendParagraph(`Delivered: ${metrics.delivered}`);
  body.appendParagraph(`Pending: ${metrics.pending}`);
  doc.saveAndClose();
}

Don’t let failures pass quietly

The most dangerous thing in automation is a silent failure. If a trigger stops and no one notices, you only realize the report is missing much later. So I leave a short confirmation signal at the end of a run.

function buildWeeklyReport() {
  try {
    // ... the logic above ...
  } catch (err) {
    GmailApp.sendEmail(Session.getActiveUser().getEmail(),
      "Weekly report failed", String(err));
    throw err;
  }
}

Wrapping up

Report automation doesn’t need to be fancy. Funnel collection into one shape, pin the output format, and make failures visible — that’s enough. The time you get back goes into interpreting the numbers.