Build a meeting-time and cost auditor

Everyone at Northwind has a sense that there are too many meetings, but a feeling is hard to act on. What the leadership team wanted was a number: how many person-hours, and how many pounds, went into meetings last week — tracked over time so they could see whether anything was actually improving.

This script audits the past seven days of the calendar. For every event it multiplies each guest’s time by their hourly rate, totals the hours and the cost across the whole week, and appends one summary row to an audit sheet. Run it every Monday and the sheet becomes a running ledger of the studio’s meeting load.

What you’ll need

• A Team rates sheet with a header row and two columns: email and hourly (the loaded cost per hour for that person).
• A Meeting audit sheet — a single tab with columns for the run date, the event count, the total person-hours, and the total cost. The script only appends rows, so an empty sheet with a header is enough.
• The IDs of both spreadsheets, pasted into the config constants below.
• Access to the calendar you want to audit. The script uses the script owner’s default calendar.

The script

// Spreadsheet that holds per-person hourly rates (columns: email, hourly).
const TEAM_RATES_ID = '1abcTeamRatesId';

// Spreadsheet that the weekly summary row is appended to.
const MEETING_AUDIT_ID = '1abcMeetingAuditId';

// How many days back to audit on each run.
const AUDIT_DAYS = 7;

/**
 * Audits the past AUDIT_DAYS of calendar events, totalling the
 * person-hours and cost, and appends one summary row to the audit sheet.
 */
function auditMeetingCosts() {
  // 1. Work out the window: AUDIT_DAYS ago up to now.
  const end = new Date();
  const start = new Date();
  start.setDate(start.getDate() - AUDIT_DAYS);

  // 2. Pull every event in that window from the default calendar.
  const events = CalendarApp.getDefaultCalendar().getEvents(start, end);
  if (!events.length) {
    Logger.log('No events in the audit window — nothing to record.');
    return;
  }

  // 3. Load the hourly rates into an email -> rate lookup.
  const rates = Object.fromEntries(
    readSheet(TEAM_RATES_ID).map((row) => [row.email, row.hourly])
  );

  // 4. Walk every event, accumulating hours and cost.
  let totalHours = 0;
  let totalCost = 0;
  for (const event of events) {
    // Length of the meeting in minutes.
    const minutes = (event.getEndTime() - event.getStartTime()) / 60_000;

    // Every guest on the invitation.
    const guests = event.getGuestList().map((g) => g.getEmail());

    // Cost = sum of each guest's rate x their hours. Unknown guests cost 0.
    const cost = guests.reduce(
      (sum, email) => sum + (rates[email] || 0) * (minutes / 60),
      0
    );

    totalHours += (guests.length * minutes) / 60;
    totalCost += cost;
  }

  // 5. Append a single summary row for this week's run.
  SpreadsheetApp.openById(MEETING_AUDIT_ID).getSheets()[0]
    .appendRow([new Date(), events.length, totalHours, totalCost]);

  Logger.log('Audited ' + events.length + ' events: ' +
    totalHours.toFixed(1) + ' hours, cost ' + totalCost.toFixed(2));
}

/**
 * Reads a sheet into an array of objects, keyed by its header row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((row) =>
    Object.fromEntries(header.map((key, i) => [key, row[i]]))
  );
}

How it works

1. auditMeetingCosts builds a date window running from AUDIT_DAYS (7) ago up to the moment it runs.
2. It fetches every calendar event in that window. If there are none, it logs a message and stops so no empty row is appended.
3. It calls readSheet to load the Team rates sheet and turns it into a lookup mapping each email to its hourly rate.
4. For each event it works out the duration in minutes, lists every guest, and sums each guest’s rate * hours. A guest with no entry in the rates sheet costs 0 rather than breaking the total.
5. It accumulates totalHours — guest count times duration, so a one-hour meeting of five people is five person-hours — and totalCost across all events.
6. It appends one row to the audit sheet: the run date, the number of events, the total person-hours, and the total cost.

Example run

Say the past week held three meetings and the Team rates sheet prices the attendees. After the script runs, a row lands on the Meeting audit sheet:

Run date	Events	Person-hours	Total cost
2025-07-07	14	38.5	4120.00
2025-07-14	11	29.0	3185.00
2025-07-21	17	45.5	4890.00

Each Monday adds a line. After a couple of months the sheet draws its own trend — and a chart on top of it shows leadership whether the meeting load is rising or falling.

Trigger it

This is a weekly job, so schedule it to run every Monday morning:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose auditMeetingCosts, a Time-driven source, a Week timer, set the day to Monday and an early hour such as 7am–8am.
4. Save, and approve the calendar and spreadsheet authorisation prompt the first time.

Watch out for

• The script audits the script owner’s default calendar only. Meetings on shared or secondary calendars are not counted — open those explicitly with CalendarApp.getCalendarById if you need them.
• getGuestList does not include the organiser unless they are also listed as a guest. If your organisers should be counted, add the event’s creator to the guest set manually.
• Recurring meetings are counted as one event per occurrence in the window, which is what you want — but all-day events and out-of-office blocks are counted too. Filter those out by event title if they distort the totals.
• A guest whose email is missing from Team rates is costed at zero, which quietly understates the bill. Check the log occasionally and keep the rates sheet current as the team changes.
• Run the trigger on a fixed weekly cadence. If a run is missed, the next run still only looks back seven days, so that week’s meetings never get recorded.