Staffing conversations need a grid of people versus weeks. Long assignment lists do not read well in meetings.
resourceAllocationMatrix aggregates Assignments hours by resource and weekStart into AllocationMatrix.
Week dates normalize to yyyy-MM-dd; multiple assignment rows for the same cell sum.
Conditional format totals over 40 to highlight overallocation in the Sheet UI.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Assignments | A resource | Person/team |
| Assignments | B weekStart | Week date key |
| Assignments | C hours | Allocated hours |
| AllocationMatrix | rows×cols | Generated grid |
| AllocationMatrix | last col | Row total |
What this script does
Aggregates assignment hours into a resource/week matrix with totals.
Prerequisites
Assignments sheet filled; weekStart consistent (Mondays recommended).
- Numeric hours
- Stable resource names
- Matrix sheet disposable
Walkthrough
Two rows same person/week 10+5 → matrix cell 15.
Edge cases
String week keys sort lexicographically — use ISO dates.
- Missing resource/week skipped
- clearContents each rebuild
How to test
Three resources, two weeks; verify totals column.
Hardening for production
Add capacity column and variance; filter by team.
Variations
Month columns instead of weeks; percent allocation.
Full code: resourceAllocationMatrix()
Maintain Assignments, then run resourceAllocationMatrix() before planning meetings.
/**
* Build a resource × week allocation matrix (hours) from Assignments.
*/
const ASSIGN = "Assignments"; // A resource, B weekStart, C hours
const MATRIX = "AllocationMatrix";
function resourceAllocationMatrix() {
const ss = SpreadsheetApp.getActive();
const rows = ss.getSheetByName(ASSIGN).getDataRange().getValues();
const resources = {};
const weeks = {};
for (let i = 1; i < rows.length; i++) {
const r = String(rows[i][0] || "");
const w = normalizeWeek_(rows[i][1]);
const h = Number(rows[i][2]) || 0;
if (!r || !w) continue;
resources[r] = true;
weeks[w] = true;
const key = r + "||" + w;
resources._hours = resources._hours || {};
resources._hours[key] = (resources._hours[key] || 0) + h;
}
const resourceList = Object.keys(resources).filter(function (k) { return k !== "_hours"; }).sort();
const weekList = Object.keys(weeks).sort();
const hours = resources._hours || {};
const out = [["resource"].concat(weekList).concat(["total"])];
resourceList.forEach(function (r) {
let total = 0;
const line = [r];
weekList.forEach(function (w) {
const v = hours[r + "||" + w] || 0;
total += v;
line.push(v);
});
line.push(total);
out.push(line);
});
const sheet = ss.getSheetByName(MATRIX) || ss.insertSheet(MATRIX);
sheet.clearContents();
sheet.getRange(1, 1, out.length, out[0].length).setValues(out);
}
function normalizeWeek_(v) {
if (v instanceof Date) {
return Utilities.formatDate(v, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
return String(v || "");
}- Line 14: Formats Date week starts as yyyy-MM-dd.
- Line 20: Accumulates hours keyed by resource||week.
- Line 28: Row sum of hours.
- Line 5: Output sheet recreated contents.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: allocation matrix
- 1weekStart values normalized
- 2Resource names consistent
- 3Hours numeric
- 4Matrix not used for manual notes
- 5Overallocation threshold defined for formatting
- 6Refresh after assignment edits
Frequently asked questions
You can — this script materializes values for export and tools that do not read pivots well.
Join a Capacity sheet and subtract totals.
Store FTE capacity separately; compare matrix totals to capacity.
Wide sheets get unwieldy — filter Assignments to a rolling 12 weeks first.
Normalize aliases before aggregation.
Yes — stacked bar by week using the matrix.
formatDate uses the script timezone — set it in project settings.