Build an OKR tracker with progress rollups

Northwind tracks OKRs the way most teams do: a list of objectives, and under each one a handful of key results with a target and a current number. Owners update their key results week to week, but nobody wants to do the arithmetic to roll those numbers up into a single score per objective. So the leadership board drifts out of date, and the quarterly review starts with someone tallying percentages by hand.

This script does the rollup. It reads the objectives and key results, works out how far along each key result is, averages those into an objective-level score, and writes a clean board with one row per objective. Run it on a schedule and the OKR board is always current without anyone touching a formula.

What you’ll need

• An Objectives sheet with a header row and three columns: id, title, owner.
• A KeyResults sheet with a header row and four columns: objectiveId (which matches an id in Objectives), kr, target, current.
• A third spreadsheet — the OKR board — where the rollup is written. The script writes to its first tab and rebuilds it on every run, so keep it separate from your source data.

The script

// Source sheet of objectives: id, title, owner.
const OBJECTIVES_SHEET_ID = '1abcObjectivesId';

// Source sheet of key results: objectiveId, kr, target, current.
const KEY_RESULTS_SHEET_ID = '1abcKeyResultsId';

// The board this script rebuilds on every run.
const OKR_BOARD_SHEET_ID = '1abcOkrBoardId';

/**
 * Reads objectives and their key results, scores each objective as the
 * average progress of its key results, and writes a rollup board.
 */
function rollupOkrs() {
  // 1. Load both source sheets as arrays of objects keyed by header.
  const objectives = readSheet(OBJECTIVES_SHEET_ID);
  const krs = readSheet(KEY_RESULTS_SHEET_ID);

  if (!objectives.length) {
    Logger.log('No objectives found — nothing to roll up.');
    return;
  }

  // 2. Build one board row per objective.
  const rows = objectives.map((o) => {
    // Find every key result that belongs to this objective.
    const mine = krs.filter((k) => k.objectiveId === o.id);

    // Progress per key result, capped at 1 (100%) so an over-achieved
    // KR cannot drag the average above full marks.
    const scores = mine.map((k) =>
      Math.min(1, (k.current || 0) / (k.target || 1)));

    // The objective score is the mean of its key-result scores.
    const score = scores.length
      ? scores.reduce((a, b) => a + b) / scores.length
      : 0;

    return [o.title, o.owner, mine.length, Math.round(score * 100) + '%'];
  });

  // 3. Rebuild the board from scratch so it always reflects the latest data.
  const board = SpreadsheetApp.openById(OKR_BOARD_SHEET_ID).getSheets()[0];
  board.clear();
  board.getRange(1, 1, 1, 4)
    .setValues([['Objective', 'Owner', 'KRs', 'Score']]);
  if (rows.length) {
    board.getRange(2, 1, rows.length, 4).setValues(rows);
  }
  Logger.log('Rolled up ' + rows.length + ' objectives.');
}

/**
 * Reads the first tab of a spreadsheet and returns its rows as objects,
 * using the header row as keys.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. rollupOkrs calls readSheet for both source sheets. That helper reads the whole data range and turns each row into an object keyed by the header, so the rest of the code can use o.id and k.target instead of column numbers.
2. If there are no objectives, it logs a message and stops.
3. For each objective, it filters the key results down to the ones whose objectiveId matches that objective’s id.
4. It scores every key result as current / target, capped at 1 so a key result that has overshot its target still counts as 100% rather than inflating the average.
5. It averages those key-result scores into a single objective score. An objective with no key results scores 0.
6. It clears the board, writes a header row, and writes one row per objective with the title, owner, key-result count, and rounded percentage score.

Example run

Say the source sheets hold this:

Objectives

id	title	owner
O1	Grow trial signups	Priya
O2	Improve onboarding	Sam

KeyResults

objectiveId	kr	target	current
O1	Monthly signups	500	400
O1	Paid conversion %	20	25
O2	Time-to-first-value (days)	3	6

After a run, the board’s first tab holds:

Objective	Owner	KRs	Score
Grow trial signups	Priya	2	90%
Improve onboarding	Sam	1	50%

“Grow trial signups” averages 80% (400/500) and a capped 100% (25/20) to land at 90%. The over-achieved conversion key result is held at 100%, not 125%.

Trigger it

This is a scheduled job — run it daily so the board is fresh each morning:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose the rollupOkrs function, a Time-driven source, a Day timer, and an early-morning hour.
4. Save and approve the authorisation prompt.

Watch out for

• The cap means the score never shows over-achievement. If you want to reward a key result that beat its target, drop the Math.min(1, ...) — but be aware one runaway key result can then push an objective well past 100%.
• objectiveId values must match id values exactly, including type. A number in one sheet and a text string in the other will not match and the key result will be silently ignored — store both as the same type.
• Every key result counts equally. There is no weighting, so a minor key result pulls the average as hard as a critical one. If you need weights, add a weight column and compute a weighted mean instead of a plain average.
• A target of 0 would divide by zero, so the script falls back to 1. Avoid zero targets; they make the resulting score meaningless.
• The board is rebuilt with clear() on every run, which wipes any manual notes or formatting on that tab. Keep commentary on a separate tab.