Build a shared-expense splitter

When the Northwind team goes to an off-site, the bills land all over the place — one person covers dinner, another the taxis, a third the morning coffees. Working out who ends up owing whom is the kind of arithmetic everyone puts off, and the longer it sits the more likely someone is quietly out of pocket.

This script does the tally. From a single Expenses log — who paid, how much, and who the cost should be split among — it computes a running balance for every person and writes it to a Balances tab. A positive balance means the team owes that person; a negative balance means they owe the team. It is a small, offline Splitwise that lives in one sheet.

What you’ll need

• An Expenses sheet with a header row and three columns: paidBy (one name), amount (a number), and splitAmong (a comma-separated list of the names sharing that cost).
• Names spelled consistently across both paidBy and splitAmong — the script matches people by exact name.

The script

// The spreadsheet holding the Expenses log and Balances output.
const SPLIT_SHEET_ID = '1abcSplitSheetId';

/**
 * Reads the Expenses log, computes a net balance for every person,
 * and writes the result to a Balances tab sorted high to low.
 */
function computeBalances() {
  const ss = SpreadsheetApp.openById(SPLIT_SHEET_ID);

  // 1. Read the Expenses sheet and map header names to column indexes.
  const [h, ...rows] = ss.getSheets()[0].getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No expenses logged — nothing to settle.');
    return;
  }
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 2. Walk every expense, updating each person's running balance.
  const balances = {};
  for (const r of rows) {
    // Who shares this cost — split on commas, trim, drop blanks.
    const sharers = String(r[col.splitAmong])
      .split(',')
      .map((s) => s.trim())
      .filter(Boolean);
    if (sharers.length === 0) continue;

    const amount = Number(r[col.amount]) || 0;
    const share = amount / sharers.length;

    // The payer is owed the full amount they put in.
    balances[r[col.paidBy]] = (balances[r[col.paidBy]] || 0) + amount;

    // Each sharer owes their equal slice of it.
    for (const p of sharers) {
      balances[p] = (balances[p] || 0) - share;
    }
  }

  // 3. Rebuild the Balances tab, sorted from most owed to most owing.
  const out = ss.getSheetByName('Balances') || ss.insertSheet('Balances');
  out.clear();
  out.getRange(1, 1, 1, 2).setValues([['Person', 'Balance']]);

  const ranked = Object.entries(balances).sort((a, b) => b[1] - a[1]);
  if (ranked.length) {
    out.getRange(2, 1, ranked.length, 2).setValues(ranked);
  }
  Logger.log('Settled balances for ' + ranked.length + ' people.');
}

How it works

1. computeBalances opens the spreadsheet and reads the Expenses sheet, splitting off the header row. If there are no expense rows, it logs and stops.
2. It builds a col lookup mapping each header name to its column index, so the rest of the code reads columns by name rather than by number.
3. For each expense it splits splitAmong on commas into a clean list of sharers, then divides the amount equally among them to get one person’s share.
4. The payer’s balance goes up by the full amount they paid; every sharer’s balance goes down by their share. A person who both paid and shared is credited and debited correctly.
5. It rebuilds the Balances tab, sorts people from the most owed (largest positive) to the most owing (largest negative), and writes the result.

Example run

Three expenses from an off-site, logged in the Expenses sheet:

paidBy	amount	splitAmong
Awadesh	90	Awadesh, Bea, Carl
Bea	30	Awadesh, Bea, Carl
Carl	60	Awadesh, Carl

After a run, the Balances tab settles it:

Person	Balance
Awadesh	20
Carl	10
Bea	-30

Awadesh is owed 20, Carl is owed 10, and Bea owes 30 — and the numbers sum to zero, which is the check that the maths balanced.

Run it

This is an on-demand job — run it once everyone’s expenses are in:

1. In the Apps Script editor, select computeBalances and click Run.
2. Approve the authorisation prompt the first time.
3. Open the Balances tab to see who owes whom.

To let teammates run it themselves, add a custom menu to the sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Expenses')
    .addItem('Compute balances', 'computeBalances')
    .addToUi();
}

Watch out for

• People are matched by exact name. “Bea” and “bea” are two different people, and a typo in splitAmong silently creates a phantom debtor. Consider a dropdown data validation on the name columns.
• The split is always equal. If one person should carry a larger share, this model cannot express it — you would need a weight column and a weighted divide.
• amount must be a number. The Number(...) || 0 guard turns an unreadable value into zero rather than throwing, which means a bad cell quietly drops the expense.
• The balances should sum to (near) zero. A non-zero total is a sign of a malformed row — a useful sanity check to add.
• It tells you the net balances, not the smallest set of payments to settle them. Working out “Bea pays Awadesh 20, Bea pays Carl 10” is a separate step.
• clear wipes the whole Balances tab on every run, so keep nothing else on it.