Build a meeting-cost calculator

A meeting feels free because nobody hands over money for it — but six people in a room for an hour is six hours of paid time, and that adds up fast. Northwind wanted that hidden cost to show up somewhere people would actually see it, so the office manager keeps a sheet of internal meetings and wanted a column that turns each one into a dollar figure.

This is a custom spreadsheet function. Type =MEETING_COST(...) in a cell, give it the attendee list and the duration, and it multiplies each person’s hourly rate by the time spent. The cost stops being abstract and starts being a number in column D that anyone can sort by.

What you’ll need

• A Google Sheet logging meetings, with one column for the attendee emails (comma-separated in a single cell) and one for the duration in minutes.
• The hourly rates for your team. The script keeps them in a HOURLY map keyed by email address — edit that map to match your own people.
• Nothing else. A custom function lives in the spreadsheet’s bound script and needs no triggers or API keys.

The function

// Loaded hourly cost per person, keyed by email address.
// Edit this map to match your team — anyone missing counts as zero.
const HOURLY = {
  '[email protected]': 120,
  '[email protected]': 95,
  '[email protected]': 95,
};

/**
 * Custom spreadsheet function. Returns the total loaded cost of a
 * meeting given a comma-separated attendee list and a duration.
 *
 * @param {string} attendees  Emails in one cell, separated by commas.
 * @param {number} durationMinutes  Length of the meeting in minutes.
 * @return {number} The total cost across all attendees.
 * @customfunction
 */
function MEETING_COST(attendees, durationMinutes) {
  // 1. No attendees or no duration means no cost — return zero.
  if (!attendees || !durationMinutes) return 0;

  // 2. Split the attendee cell into a clean list of email addresses.
  const list = String(attendees)
    .split(',')
    .map((email) => email.trim())
    .filter(Boolean);

  // 3. Convert the duration to hours so it lines up with hourly rates.
  const hours = durationMinutes / 60;

  // 4. Add up each attendee's rate x hours. Unknown emails count as zero.
  return list.reduce((sum, email) => sum + (HOURLY[email] || 0) * hours, 0);
}

How it works

1. MEETING_COST first guards against empty input — if either the attendee cell or the duration is blank, it returns 0 so the column never shows an error.
2. It coerces the attendee cell to a string and splits it on commas, trimming whitespace and dropping any empty fragments left by a trailing comma.
3. It converts the duration from minutes to hours, since the HOURLY map is priced per hour.
4. It runs a reduce over the attendee list, looking each email up in HOURLY and adding rate * hours to the running total. An email that is not in the map contributes 0 rather than breaking the calculation.

Example run

With the HOURLY map above, suppose the sheet looks like this:

Meeting	Attendees	Duration (min)	Cost
Sprint planning	[email protected], [email protected]	60	=MEETING_COST(B2, C2)
Client review	[email protected], [email protected], [email protected]	90	=MEETING_COST(B3, C3)

The Cost column resolves to:

Meeting	Cost
Sprint planning	215
Client review	465

Sprint planning is one hour of a 120 and a 95 rate. The client review is 1.5 hours across three people: (120 + 95 + 95) * 1.5 = 465.

Run it

There is nothing to schedule — a custom function recalculates whenever its inputs change:

1. Open the meeting sheet, then Extensions -> Apps Script.
2. Paste the code in, edit the HOURLY map for your team, and save.
3. Back in the sheet, put =MEETING_COST(B2, C2) in the cost column and fill it down. The first call may show Loading… for a moment while the script authorises.

Watch out for

• The attendee emails in the sheet must match the keys in HOURLY exactly, including case. A typo or a stray space silently drops that person’s cost to zero — which makes a meeting look cheaper than it really is.
• The HOURLY map is hard-coded in the script. When someone joins, leaves, or gets a raise, the map has to be edited by hand. For a larger team, read the rates from a Team rates tab instead.
• Custom functions cannot call services that need authorisation, send email, or read other users’ data. This one only does arithmetic, so it is fine — but do not try to extend it into anything that touches the network.
• The figure is loaded hourly cost, not just salary. Decide once whether your rates include overheads like benefits and software, and keep every entry on the same basis so the numbers are comparable.
• A custom function recalculates on every edit to its inputs. With thousands of meeting rows this can feel sluggish; for a large log, compute the column with a normal script on a trigger instead.