Build a budget-vs-actual variance tracker

Northwind sets a monthly budget for each cost category — software, contractors, travel, and so on. The trouble is that the budget lives in one sheet and the actual spend trickles into another, so nobody knows they have gone over until the month is closed and it is too late to do anything about it.

This script joins the two together. Every morning it totals the current month’s expenses by category, compares them against the budget, and writes a single variance table. Categories more than 10% over budget turn red, so an overspend is obvious the moment you open the sheet rather than at month-end.

What you’ll need

• A Budget sheet with a header row and columns category and budget.
• An Expenses sheet with a header row and columns date, amount and category. The category values must match the Budget sheet exactly.
• A Variance sheet — the script clears and rewrites it on every run, so it can start empty.

The script

// The three spreadsheets this tracker reads from and writes to.
const EXPENSES_SHEET_ID = '1abcExpensesSheetId';
const BUDGET_SHEET_ID = '1abcBudgetSheetId';
const VARIANCE_SHEET_ID = '1abcVarianceSheetId';

// A category is flagged red once it is this far over budget.
const OVERSPEND_THRESHOLD_PCT = 10;

/**
 * Totals the current month's expenses by category, compares them
 * against budget, and rewrites the Variance sheet with the result.
 */
function refreshVariance() {
  const expenses = readSheet(EXPENSES_SHEET_ID);
  const budget = readSheet(BUDGET_SHEET_ID);

  if (!budget.length) {
    Logger.log('No budget rows — nothing to compare.');
    return;
  }

  // 1. Work out the first day of this month and the first of next month.
  const month = new Date();
  month.setDate(1);
  month.setHours(0, 0, 0, 0);
  const nextMonth = new Date(month);
  nextMonth.setMonth(nextMonth.getMonth() + 1);

  // 2. Sum every expense that falls inside the current month, by category.
  const actuals = new Map();
  for (const e of expenses) {
    if (e.date < month || e.date >= nextMonth) continue;
    actuals.set(e.category, (actuals.get(e.category) || 0) + e.amount);
  }

  // 3. Build one row per budgeted category: budget, actual, variance, %.
  const rows = budget.map((b) => {
    const actual = actuals.get(b.category) || 0;
    const variance = actual - b.budget;
    const pct = b.budget === 0 ? 0 : (variance / b.budget) * 100;
    return [b.category, b.budget, actual, variance, pct];
  });

  // 4. Clear the Variance sheet and write the header plus the rows.
  const sheet = SpreadsheetApp.openById(VARIANCE_SHEET_ID).getSheets()[0];
  sheet.clearContents();
  sheet.getRange(1, 1, 1, 5)
    .setValues([['Category', 'Budget', 'Actual', 'Variance', '% vs budget']]);
  sheet.getRange(2, 1, rows.length, 5).setValues(rows);

  // 5. Colour-code the rows that are over the overspend threshold.
  highlightOverspends(sheet, rows);
}

/**
 * Paints the % column red for any category over the overspend threshold.
 */
function highlightOverspends(sheet, rows) {
  rows.forEach((row, i) => {
    const pct = row[4];
    const cell = sheet.getRange(i + 2, 5);
    cell.setBackground(pct > OVERSPEND_THRESHOLD_PCT ? '#f4cccc' : null);
  });
}

/**
 * Reads a sheet into an array of objects keyed by the header row.
 */
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. refreshVariance reads both source sheets into arrays of objects with readSheet, and stops early if there are no budget rows to compare.
2. It builds two dates — the first of this month and the first of next month — to define the window of expenses that count.
3. It loops through every expense, skips anything outside that window, and accumulates the total spend per category into a Map.
4. For each budgeted category it looks up the actual spend (defaulting to 0), then computes the variance and the percentage over or under budget.
5. It clears the Variance sheet and writes a fresh header plus one row per category, so the table always shows the current month.
6. highlightOverspends paints the % vs budget cell red for any category more than OVERSPEND_THRESHOLD_PCT over budget, and clears it otherwise.

Example run

Given a Budget sheet and this month’s expenses, the Variance sheet ends up looking like this:

Category	Budget	Actual	Variance	% vs budget
Software	2000	2100	100	5
Contractors	8000	9400	1400	17.5
Travel	1500	900	-600	-40

Contractors is 17.5% over, past the 10% threshold, so its % vs budget cell turns red. Software is over but only slightly, so it stays plain. Travel is under budget and stays plain too.

Trigger it

Run this on a daily time-driven trigger so the table is fresh each morning:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose refreshVariance, Time-driven, Day timer, 6am to 7am.

Watch out for

• Category names must match exactly between the two sheets. “Travel” and “travel ” (with a trailing space) are treated as different categories, and the spend silently goes missing. Trim and standardise as you enter data.
• The date column must be a real date, not text. If expenses are pasted in as strings, the month comparison fails and every expense is skipped.
• A category that exists in Expenses but not in Budget is simply ignored — its spend never appears. Keep the Budget sheet complete.
• The script only ever shows the current month. Once the month rolls over the table resets to zero actuals. Archive last month’s table first if you need a record.
• setBackground is applied cell by cell. For a very large category list, build a real conditional-formatting rule once instead, so the colours survive even if the script is disabled.