Auto-rebuild grouped summaries on edit

Northwind tracks studio spending on an Expenses tab — one row per cost, with a category against each. The team wants a live rollup of total spend by category, but a pivot table has to be refreshed by hand and a SUMIF block needs maintaining as new categories appear. Either way, the summary is only as fresh as the last time someone remembered to update it.

This script keeps the rollup honest. Every time a cell on the Expenses tab changes, it recomputes the per-category totals from scratch and rewrites a Rollup tab. There is nothing to refresh and nothing to drag down — the summary is correct the moment the data is.

What you’ll need

• A Google Sheet with an Expenses tab that has a header row including a category column and an amount column.
• The amount column should hold plain numbers, not text — the script adds them up directly.
• The script creates the Rollup tab itself on the first run, so you do not need to make it in advance.

The script

// Tab names — the source of expense rows and the summary destination.
const SOURCE_SHEET = 'Expenses';
const ROLLUP_SHEET = 'Rollup';

/**
 * Installable onEdit trigger. Fires on every edit to the spreadsheet,
 * but only rebuilds the rollup when the Expenses tab changed.
 */
function onEdit(e) {
  // Guard: a manual run has no event object, so there is nothing to do.
  if (!e || !e.range) return;

  // Ignore edits to any tab other than Expenses.
  if (e.range.getSheet().getName() !== SOURCE_SHEET) return;

  rebuildCategoryRollup();
}

/**
 * Reads the Expenses tab, totals the amounts by category, and
 * rewrites the Rollup tab from scratch.
 */
function rebuildCategoryRollup() {
  const ss = SpreadsheetApp.getActive();
  const values = ss.getSheetByName(SOURCE_SHEET).getDataRange().getValues();

  // Split the header off and map column names to their indexes.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((k, i) => [k, i]));

  // Sum the amounts into a { category: total } map.
  const totals = {};
  for (const row of rows) {
    const category = row[col.category];
    if (!category) continue; // skip rows with no category
    totals[category] = (totals[category] || 0) + row[col.amount];
  }

  // Rebuild the Rollup tab from scratch on every edit.
  const out = ss.getSheetByName(ROLLUP_SHEET) || ss.insertSheet(ROLLUP_SHEET);
  out.clear();
  out.getRange(1, 1, 1, 2).setValues([['Category', 'Total']]);

  // Write one row per category, if there is anything to write.
  const data = Object.entries(totals);
  if (data.length) {
    out.getRange(2, 1, data.length, 2).setValues(data);
  }
}

How it works

1. onEdit is an installable trigger that runs on every edit to the spreadsheet. It first guards against a manual run, where there is no event object.
2. It checks which tab the edit happened on and returns immediately unless it was the Expenses tab — so edits elsewhere cost nothing.
3. When the Expenses tab did change, it calls rebuildCategoryRollup.
4. rebuildCategoryRollup reads the whole Expenses tab, splits off the header row, and builds a col lookup so columns are referenced by name.
5. It loops the rows, skipping any with no category, and accumulates each amount into a totals map keyed by category.
6. It finds or creates the Rollup tab, clears it, and writes a fresh header followed by one row per category — so the rollup always reflects the current data with no stale rows left behind.

Example run

The Expenses tab:

date	category	amount
2026-01-02	Software	120
2026-01-02	Travel	340
2026-01-03	Software	80
2026-01-03	Catering	55

The moment any of those cells is edited, the Rollup tab is rewritten:

Category	Total
Software	200
Travel	340
Catering	55

Add a new Travel row for 60 and Travel jumps to 400 instantly — no refresh.

Trigger it

A simple onEdit function cannot reliably write to other sheets, so this needs an installable trigger:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for onEdit, choose From spreadsheet as the source and On edit as the event type.
3. Approve the authorisation prompt. The rollup now rebuilds on every edit to the Expenses tab.

Watch out for

• onEdit triggers have a short runtime limit. A full rebuild is fine for hundreds or low thousands of rows; for very large sheets, recompute on a timer instead so a slow rebuild does not block editing.
• The script overwrites the Rollup tab entirely on every run. Do not add notes or extra formulas there — they will be wiped.
• Non-numeric values in the amount column will produce odd totals (string concatenation rather than addition). Keep the column formatted as numbers.
• A rapid burst of edits triggers a rebuild for each one. That is usually harmless, but for heavy paste operations consider debouncing or moving to a scheduled rebuild.
• Installable triggers run as the user who created them, not the user editing. Make sure that account has access to every tab involved.