Build a capacity-planning resource grid

Northwind books people onto projects a week at a time, and each booking is a separate row in an Allocations sheet. That long list answers “what is on this project” easily enough, but it cannot answer the question managers actually ask: is anyone overloaded next month, and who has spare capacity?

This script flips the list into a grid. People become rows, weeks become columns, and each cell holds the total hours that person is booked for that week. One look down a column tells you the week’s load; one look across a row tells you whether someone is buried or free.

What you’ll need

• An Allocations sheet with a header row and columns person, project, hours and weekStart. The weekStart column must hold real dates (the Monday of each week works well).
• A Capacity sheet — the script clears and rewrites it on every run, so it can start empty.

The script

// Source allocations and the grid this script writes.
const ALLOCATIONS_SHEET_ID = '1abcAllocationsId';
const CAPACITY_SHEET_ID = '1abcCapacityId';

// Date format used for the week-column headers.
const WEEK_FORMAT = 'yyyy-MM-dd';

/**
 * Reads the flat Allocations list and rewrites the Capacity sheet as a
 * grid of people (rows) by weeks (columns), with total hours in cells.
 */
function buildCapacityGrid() {
  const [h, ...rows] = SpreadsheetApp.openById(ALLOCATIONS_SHEET_ID)
    .getSheets()[0].getDataRange().getValues();

  if (!rows.length) {
    Logger.log('No allocations to grid — nothing to do.');
    return;
  }

  // Map header names to column indexes so the code reads clearly.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 1. Collect the unique people, sorted alphabetically for stable rows.
  const people = [...new Set(rows.map((r) => r[col.person]))].sort();

  // 2. Collect the unique weeks, formatted and sorted for stable columns.
  const weeks = [...new Set(rows.map((r) =>
    Utilities.formatDate(r[col.weekStart], 'GMT', WEEK_FORMAT)))].sort();

  // 3. Build the grid one row at a time, starting with the header.
  const grid = [['Person', ...weeks]];
  for (const person of people) {
    // For each week, sum the hours of this person's matching allocations.
    const cells = weeks.map((w) =>
      rows
        .filter((r) => r[col.person] === person &&
          Utilities.formatDate(r[col.weekStart], 'GMT', WEEK_FORMAT) === w)
        .reduce((sum, r) => sum + (r[col.hours] || 0), 0)
    );
    grid.push([person, ...cells]);
  }

  // 4. Clear the Capacity sheet and write the finished grid in one go.
  const sheet = SpreadsheetApp.openById(CAPACITY_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, grid.length, grid[0].length).setValues(grid);
}

How it works

1. buildCapacityGrid reads the whole Allocations sheet, splitting the header off from the data rows, and stops early if there are no rows.
2. It builds a col lookup so columns are referenced by name (col.person) rather than by a fragile numeric index.
3. It gathers the unique list of people and sorts it — these become the grid’s rows, in a stable order from run to run.
4. It gathers the unique list of week-start dates, formatted as yyyy-MM-dd strings and sorted — these become the grid’s columns.
5. For each person it walks the weeks, and for each week filters the allocations down to that person and that week, then sums the hours.
6. It assembles the header row plus one row per person and writes the entire grid to the Capacity sheet in a single setValues call.

Example run

The Allocations sheet holds individual bookings:

person	project	hours	weekStart
Amara	Rebrand	20	2026-06-01
Amara	Website	15	2026-06-01
Amara	Rebrand	30	2026-06-08
Ben	Website	35	2026-06-01

After a run, the Capacity sheet shows the rolled-up grid:

Person	2026-06-01	2026-06-08
Amara	35	30
Ben	35	0

Amara’s two bookings in the first week are added together; Ben has nothing in the second week, so the cell reads 0.

Trigger it

Run this on a daily time-driven trigger so the grid keeps pace with new bookings:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose buildCapacityGrid, Time-driven, Day timer, and a time that suits — early morning keeps the grid fresh before the day starts.

Watch out for

• weekStart must be a real date. If the column holds text, formatDate throws and the run stops. Format the column as a date in the sheet.
• All bookings for the same week must share the exact same weekStart value. If one row says the 1st and another says the 2nd, they land in separate columns. Always snap bookings to the Monday of the week.
• The cell sum filters every allocation row for every person and week. That is fine for a normal team, but with thousands of allocations it gets slow — build a Map keyed by person|week instead.
• The grid only shows weeks that appear in the data. A week with no bookings has no column at all, so a quiet week can look like it does not exist.
• A blank or zero cell means “no hours booked”, not “no capacity”. The grid shows load, not free time — compare against each person’s working hours yourself.