Build a lightweight CRM pipeline board

A small studio rarely needs a full CRM subscription — what Northwind actually needs is to know how many deals are in play and what they are worth at each stage. A plain Sheet handles the data entry happily: one row per deal, a value, a stage. What it does not give you for free is the roll-up — the count and total value per stage that tells you whether the pipeline is healthy.

This script reads that pipeline sheet and rebuilds a separate board sheet with one row per stage, showing how many deals sit there and the money attached. Run it on a schedule or on every edit, and the board is always a current, glanceable summary — a CRM dashboard without the CRM.

What you’ll need

• A Google Sheet for the pipeline, with a first sheet whose headers include client, value, and stage. One row per deal.
• The stage column filled with values from a known set — lead, qualified, proposal, negotiation, won, lost. The script ignores any stage outside this list.
• A second Google Sheet (or a second tab) for the board. The script clears and rewrites its first sheet, so keep nothing else there.

The script

// The spreadsheet holding the raw deal rows.
const PIPELINE_SHEET_ID = '1abcPipelineId';

// The spreadsheet the rolled-up board is written to.
const BOARD_SHEET_ID = '1abcCrmBoardId';

// The pipeline stages, in the order they appear on the board.
const STAGES = ['lead', 'qualified', 'proposal', 'negotiation', 'won', 'lost'];

/**
 * Reads the deal pipeline and rebuilds the board sheet with a
 * count and total value for each stage.
 */
function rollupPipeline() {
  const [header, ...rows] = SpreadsheetApp.openById(PIPELINE_SHEET_ID)
    .getSheets()[0].getDataRange().getValues();

  // Bail out early if the pipeline has no deals yet.
  if (!rows.length) {
    Logger.log('No deals in the pipeline — nothing to roll up.');
    return;
  }

  // 1. Map header names to column indexes so lookups read clearly.
  const col = Object.fromEntries(header.map((key, i) => [key, i]));

  // 2. For each stage, count the matching deals and sum their value.
  const totals = STAGES.map((stage) => {
    const dealsInStage = rows.filter((r) => r[col.stage] === stage);
    const value = dealsInStage.reduce(
      (sum, r) => sum + (r[col.value] || 0),
      0
    );
    return [stage, dealsInStage.length, value];
  });

  // 3. Clear the board and write the fresh roll-up.
  const board = SpreadsheetApp.openById(BOARD_SHEET_ID).getSheets()[0];
  board.clear();
  board.getRange(1, 1, 1, 3)
    .setValues([['Stage', 'Count', 'Total value']]);
  board.getRange(2, 1, totals.length, 3).setValues(totals);
  Logger.log('Rolled up ' + rows.length + ' deals across ' +
    STAGES.length + ' stages.');
}

How it works

1. rollupPipeline opens the pipeline spreadsheet and splits its first sheet into a header row and the deal rows.
2. If there are no deal rows it logs and stops, leaving the board untouched.
3. It builds a col lookup from header name to index, so the rest of the code reads r[col.value] instead of a brittle numeric column.
4. For each stage in STAGES, it filters the deals to that stage, counts them, and sums the value column. The || 0 keeps a blank value cell from turning the total into NaN.
5. Iterating STAGES rather than the data means every stage appears on the board even when it currently holds zero deals — an empty negotiation row is itself a useful signal.
6. It clears the board sheet and writes the header plus one row per stage in a single setValues call, so the board always reflects the latest pipeline.

Example run

Say the pipeline sheet holds these deals:

client	value	stage
Acme	12000	proposal
Globex	8000	lead
Initech	15000	won
Umbrella	5000	lead
Hooli	20000	negotiation

After a run, the board sheet reads:

Stage	Count	Total value
lead	2	13000
qualified	0	0
proposal	1	12000
negotiation	1	20000
won	1	15000
lost	0	0

Two deals worth £13,000 sitting in lead, nothing in qualified — the board makes the gap obvious at a glance.

Trigger it

Keep the board current without anyone clicking anything:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose rollupPipeline, event source Time-driven, an Hour timer for a steady refresh.
3. Save and approve the authorisation prompt.

For an instant refresh as deals are edited, add an installable onEdit trigger on the pipeline spreadsheet instead of (or as well as) the hourly one.

Watch out for

• Stage names must match STAGES exactly, including case. A deal in Lead or proposal with a trailing space is silently dropped from every total — add a data-validation dropdown on the stage column to prevent typos.
• The value column must hold plain numbers. A value entered as £12,000 is text and contributes 0 to the total — format the column as a number and let the sheet show the currency symbol.
• board.clear() wipes the board sheet’s first tab completely, formatting and all. Keep notes or charts on a different tab.
• The roll-up is a snapshot from the last run. Between runs the board can lag the pipeline — keep the trigger in place, or add an onEdit trigger so edits refresh it immediately.
• This sums every deal regardless of age. To report only the current quarter, add a date column to the pipeline and filter rows before the roll-up.