Build a team-capacity view from calendars

Every Monday at Northwind someone asks the same question in standup: who has bandwidth this week? People guess, the answer is always “I’m slammed”, and the project that actually needs an extra pair of hands keeps slipping. The data is right there in everyone’s calendars — it just needs to be added up.

This script reads the next seven days of meetings from each person’s calendar and writes a one-row-per-person summary to a Capacity sheet: meetings booked and hours booked. It is the simplest possible capacity view, but it is honest data instead of standup theatre.

What you’ll need

• A Team sheet with two columns: name and calendarId. For people on the same Workspace you can use their email as the calendar ID; for shared calendars use the calendar’s ID from Calendar settings.
• A Capacity sheet for the output. The script clears it on every run, so do not store anything else on the first tab.
• The script needs read access to each calendar. For team members, ask them to share their calendar with the script account, or run the script under an account that already has access.

The script

// Sheet that lists the team and their calendar IDs.
const TEAM_SHEET = '1abcTeamCalendarsId';

// Sheet where the weekly capacity view is written.
const CAPACITY_SHEET = '1abcCapacityId';

// How many days ahead to count meetings for.
const WINDOW_DAYS = 7;

/**
 * Reads each team member's calendar for the next week, totals meeting
 * hours, and writes one row per person to the Capacity sheet.
 */
function teamCapacity() {
  const team = readSheet(TEAM_SHEET);
  const start = new Date();
  const end = new Date(start.getTime() + WINDOW_DAYS * 86400000);
  const rows = [];

  // 1. Walk the team. Skip anyone whose calendar we cannot reach so
  //    one missing share does not break the whole run.
  for (const p of team) {
    const cal = CalendarApp.getCalendarById(p.calendarId);
    if (!cal) {
      Logger.log('No calendar access for ' + p.name + ' — skipping.');
      rows.push([p.name, 'n/a', 'n/a']);
      continue;
    }

    // 2. Pull events in the window and total their duration in hours.
    const events = cal.getEvents(start, end);
    const hours = events.reduce((s, e) =>
      s + (e.getEndTime() - e.getStartTime()) / 3600_000, 0);

    rows.push([p.name, events.length, hours.toFixed(1)]);
  }

  // 3. Rebuild the Capacity sheet from scratch so old rows never linger.
  const sheet = SpreadsheetApp.openById(CAPACITY_SHEET).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 3).setValues([['Person', 'Meetings', 'Hours']]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, 3).setValues(rows);
  }
}

/**
 * Reads a sheet into an array of plain objects keyed by header row.
 * Cuts the column-index ceremony out of the main function.
 */
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. teamCapacity reads the Team sheet via the readSheet helper, which turns each row into a { name, calendarId } object.
2. It computes a seven-day window starting now. The window slides with each run, so a Monday morning trigger always shows the week ahead.
3. For each person it opens their calendar by ID. If the calendar is unreachable — usually a missing share — it logs the name and writes n/a in the row instead of crashing the run.
4. It calls getEvents(start, end) and reduces the events to a total in hours by subtracting end from start (millisecond math, then divided by 3,600,000).
5. It clears the Capacity sheet, writes a header row, and pushes the data in a single setValues call.

Example run

Say the Team sheet contains:

name	calendarId
Priya	[email protected]
Sam	[email protected]
Jordan	[email protected]

After a Monday morning run the Capacity sheet looks like:

Person	Meetings	Hours
Priya	18	22.5
Sam	6	4.0
Jordan	12	14.5

Sam has the bandwidth. That is the row the project lead is looking for.

Trigger it

Run it Monday morning so the view is fresh for standup:

1. In the Apps Script editor, open Triggers (clock icon).
2. Add a trigger for teamCapacity, event source Time-driven, type Week timer, day Monday, time 7am to 8am.
3. Approve the authorisation prompt the first time.

A weekly run is enough — capacity that shifts mid-week is usually obvious without a report.

Watch out for

• Hours counted include every event on the calendar, including ones the person declined. To exclude declines, filter on e.getMyStatus() !== CalendarApp.GuestStatus.NO.
• All-day events count as 24 hours each, which can blow out the totals. If you want to ignore them, skip events where e.isAllDayEvent() returns true.
• The script needs read access to every calendar. The cleanest setup is to use a shared “Northwind Reporting” account that the team has granted read access to.
• This is week-ahead capacity, not retrospective utilisation. Swap the window to the past seven days and you have the start of a “how did we actually spend the week” report.