Automation · Calendar time tracking via Google Apps Script

Where a team's 40-hour week goes, from one calendar.

Our team logged time in a separate tool, and most of it was guesswork at the end of the week. I wanted to see how each person's week split across the work that actually matters: task execution, review, client calls, monitoring and reporting. I built a Google Apps Script that reads each person's calendar, tags every block, and sends the answer by email.

Google Apps ScriptGoogle CalendarGoogle SheetsGmail
40htarget per person per week
0servers to host (runs inside Google Apps Script and Sheets)
8 PMautomatic daily, weekly and monthly emails
95%of logged time tagged to a role umbrella, in a sample week
Inside the tool

What the team sees.

The weekly and daily emails from a sample week, and the script behind them. Names, company and client details are anonymised.

Weekly email showing hours per person against the 40-hour target, split by umbrella
1 · Weekly emailHours per person against the 40h target, split by umbrella, with out-of-office time shown separately.
Planned-vs-actual table in the weekly email, with each umbrella marked within 20 percent of plan or off plan
2 · Planned vs actualLogged hours against each role's weekly plan per umbrella, within ±20% or off plan.
Daily email showing hours per person against an 8-hour target for one day
3 · Daily emailThe same view for one day against an 8h target, sent at 8 PM on weekdays.
Apps Script editor showing the script header with the tagging rules and the preview, test and live report functions
4 · Apps ScriptThe script's header: tagging rules, then the preview, test and live functions for each report.
The problem

Hours logged, work unseen.

Time reports were manual, always late, and told me hours but not what kind of work they were. A senior person's week looks very different from an analyst's, and the same total hides that. I needed one number per umbrella, per person, and an easy way to spot who was over or under plan.

The flow

Six steps, every evening.

Each box is one step. Press Run workflow to watch a report being built, or click any step to read what it does.

Scroll sideways to see the whole flow →

Step 1

Design decisions

Accurate by default, easy to change.

accuracy

Overlaps counted once

Each slice of time goes to one umbrella, so totals never exceed the working window.

simplicity

Codes in the title

A two- to four-letter code at the start of an event is enough. No separate form to fill in.

team

Plans per role

A senior analyst's review time counts toward task execution, as in the role plan. Analysts get their own plan.

control

Settings in a sheet

Codes, keywords and weekly plans live in a Tags tab. Changes take effect on the next run, with no code edits.

When something breaks

Gaps show up, not zeros.

untagged

Untagged is listed

Blocks with no code and no rule are counted as Untagged and listed in the email, so they're easy to fix.

calendar

Unshared calendars named

If a calendar isn't shared, the report says so instead of showing zeros.

declined

Declined events skipped

Events the person declined are not counted.

safety

Test mode first

Test mode sends every email to me only, so changes can be checked before the team gets them.

What changed

Time reporting became a short read.

Time reporting moved from a manual task to a short read each week. Each person can see their own split against plan, and the senior manager gets one email with the whole team. Setting it up for a new team member takes [FILL] minutes.