Generate a timesheet from calendar events

Filling in a weekly timesheet at Northwind is the kind of admin nobody enjoys. By Friday afternoon people are reconstructing their week from memory, guessing which meetings were for which client, and rounding to the nearest hour because the truth is fuzzy. The data is right there on the calendar — it just needs to be tallied.

This script reads the past seven days of events, groups them by a client tag in the title (the convention is [Acme] Design review), and appends one row per client to a Timesheets sheet. Friday afternoons get their hour back, and the numbers are honest because they came from the calendar.

What you’ll need

• The convention: tag any billable event with the client in square brackets at the start of the title, e.g. [Acme] Design review. Untagged events get bucketed under untagged so you can see how much time leaked out.
• A Timesheets Google Sheet for the output. The script appends rows; it does not clear, so you get a running log of weeks.
• The script runs against CalendarApp.getDefaultCalendar() — the calendar of whoever owns the script. Each person runs their own copy.

The script

// Sheet that holds the running timesheet log.
const TIMESHEET_SHEET = '1abcTimesheetsId';

// How many days back to scan. Seven gives a "past week" view.
const WINDOW_DAYS = 7;

/**
 * Reads the past week of calendar events, groups them by [tag] in the
 * title, and appends one row per client to the Timesheets sheet.
 */
function buildTimesheet() {
  const end = new Date();
  const start = new Date();
  start.setDate(start.getDate() - WINDOW_DAYS);

  const events = CalendarApp.getDefaultCalendar().getEvents(start, end);
  if (!events.length) {
    Logger.log('No events in the window — nothing to log.');
    return;
  }

  // 1. Tally hours per [tag]. Anything without a leading bracket is
  //    bucketed under "untagged" so we can see admin overflow.
  const totals = new Map();
  for (const event of events) {
    const tag = (event.getTitle().match(/^\[([^\]]+)\]/) || [, 'untagged'])[1];
    const hours = (event.getEndTime() - event.getStartTime()) / 3600_000;
    totals.set(tag, (totals.get(tag) || 0) + hours);
  }

  // 2. Sort by hours descending — biggest clients at the top of the
  //    weekly entry — and format the date once for every row.
  const weekStart = Utilities.formatDate(start, 'GMT', 'yyyy-MM-dd');
  const rows = [...totals.entries()]
    .sort((a, b) => b[1] - a[1])
    .map(([client, hours]) => [weekStart, client, hours.toFixed(2)]);

  // 3. Append to the running log. Headers, if any, are left untouched.
  const sheet = SpreadsheetApp.openById(TIMESHEET_SHEET).getSheets()[0];
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 3).setValues(rows);
  Logger.log('Logged ' + rows.length + ' client rows for week starting ' + weekStart + '.');
}

How it works

1. buildTimesheet builds a seven-day window ending now and asks the default calendar for every event in that range.
2. If there are no events it logs and returns early — no point appending an empty row.
3. For each event it pulls the tag out of the title with a small regex: [Acme] Design review becomes Acme. Anything without a leading [tag] falls through to untagged.
4. It adds the event’s duration (end minus start, divided into hours) to a Map keyed by tag. The Map makes the running total trivial.
5. It sorts clients by hours descending, formats the week-start date once, and produces a row of [weekStart, client, hours] per tag.
6. It appends the rows to the existing log — no clearing — so you build up a history of weeks over time.

Example run

Say last week’s calendar contains, among other things, these tagged events:

• [Acme] Design review — 1 hour
• [Acme] Standup — 0.5 hours x 4
• [Globex] Workshop — 3 hours
• [Globex] Demo prep — 1.5 hours
• Internal: team lunch — 1 hour (no tag)

After buildTimesheet runs, the Timesheets sheet has these new rows appended:

Week of	Client	Hours
2025-07-18	Acme	3.00
2025-07-18	Globex	4.50
2025-07-18	untagged	1.00

The untagged row is the useful one. It is the time that did not bill, and it shows you whether you are leaking too much of the week into admin.

Trigger it

Run it Friday afternoon so the week is fresh in memory if anything looks off:

1. In the Apps Script editor, open Triggers (clock icon).
2. Add a trigger for buildTimesheet, event source Time-driven, type Week timer, day Friday, time 4pm to 5pm.
3. Approve the authorisation prompt the first time.

Watch out for

• The tag convention is everything. Without [Client] ... at the front of the title, time goes into untagged. A short doc on the team wiki saves a surprising amount of grief.
• All-day events count as 24 hours each. Strip them out with a e.isAllDayEvent() filter if you do not want them in the totals.
• Declined meetings still appear in getEvents. Filter on e.getMyStatus() !== CalendarApp.GuestStatus.NO if you want to count only meetings you actually attended.
• The window is a calendar week, not a working week. The script reads the last seven days from “now”; events from the previous Saturday and Sunday are included. Run it Friday afternoon, not Monday, and the boundary lines up.
• Hours are in decimal (3.50, not 3:30) because spreadsheets sum decimals cleanly. If you need duration formatting for an invoice, do it in the spreadsheet with TEXT(A1/24, "[h]:mm").