Client retainer tracker

A starter kit for tracking client retainers the way Northwind Studios does: one Clients sheet, a script that totals logged hours against each retainer, and a weekly Slack summary so nothing slips.

What’s in the kit

• A Clients sheet — columns: name, email, monthlyRetainer, hoursLogged, status.
• An onOpen menu to recalculate usage on demand.
• A time-based trigger that posts a weekly summary to #alerts.

Set up the sheet

1. Create a sheet named Clients with the columns above.
2. Open Extensions → Apps Script and paste the code below.
3. Add your Slack webhook under Project Settings → Script Properties as SLACK_WEBHOOK.

The script

const CLIENTS_SHEET = 'Clients';

/** Add a Northwind menu when the sheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Northwind')
    .addItem('Post retainer summary', 'postRetainerSummary')
    .addToUi();
}

/** Total each client's usage and post a summary to Slack. */
function postRetainerSummary() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(CLIENTS_SHEET);
  const rows = sheet.getDataRange().getValues().slice(1); // drop header row

  const overBudget = rows
    .filter(([, , retainer, hours]) => Number(hours) * 100 > Number(retainer))
    .map(([name]) => `• ${name}`);

  const text = overBudget.length
    ? `Clients over retainer this week:\n${overBudget.join('\n')}`
    : 'All Northwind clients are within retainer this week. 🎉';

  const url = PropertiesService.getScriptProperties().getProperty('SLACK_WEBHOOK');
  UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({ text }),
  });
}

Schedule it

Add a weekly trigger so the summary posts itself:

function createWeeklyTrigger() {
  ScriptApp.newTrigger('postRetainerSummary')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

Run createWeeklyTrigger once and every Monday at 9am the summary lands in #alerts.