# Google Calendar to a timesheet: a script tested on a sample year

> Google exports a calendar as .ics, where a weekly meeting is one entry. Read naively, our sample year lost 100 of its 434.75 hours; our script got all of them.

- Published: 2026-10-31
- Author: 28creation Desk
- Format: Guide
- URL: https://28creation.com/articles/google-calendar-to-timesheet/

## The short read

**Read entry by entry, a sample year of calendar events totaled 334.5 hours instead of the real 434.75.**

- Google Calendar exports .ics files, which store a repeating meeting once with its rule, its skipped dates and its moved instances.
- Our 152-line Python script expanded the repeats and matched all 48 month-and-project totals in an answer key we built for a made-up year.

### What you can do with this

> Before you bill from calendar hours, put the project in square brackets in each event title, export the .zip, run the script with your address, and read its check lines.

---

Google Calendar exports `.ics` files, not a spreadsheet, and in that format a weekly meeting is one entry with a rule, not 52 rows. Add up the entries as they stand and the hours come out wrong: on a sample year we built, 334.5 hours against an answer key of 434.75.

This guide turns the export into hours per project per month, as a CSV file that Excel or Google Sheets can open. It explains the five traps in the file and gives a 152-line Python script that handles them. Then it shows what the script found on a made-up year of 424 events with a known answer.

It ends with a Sheets route for people who would rather not run Python. Google's pages and the standards were read on October 26, 2026.

## Google exports calendar files, not a spreadsheet

Google's [export page](https://support.google.com/calendar/answer/37111?hl=en) gives two routes, both "on a computer" only, not in the app. For every calendar at once: **Settings**, then **Import & export**, then **Export**, and "a .zip file downloads". The page says you open the .zip "to access the individual .ics files". For one calendar: point at it under **My calendars**, choose **Settings and sharing**, then **Export calendar**.

Two limits apply. You need the "Make changes and manage sharing" permission on each calendar you export. On a work or school account, "your administrator may restrict export settings".

Google does have a built-in summary, [Time Insights](https://support.google.com/calendar/answer/10738043?hl=en), but it will not make a timesheet. It is for work or school accounts and covers only the primary calendar. It sorts time by kind, such as focus time, 1:1 meetings and meetings with 3 or more guests, not by client or project.

A meeting counts only if you answered Yes, it has "at least one other guest", it is marked Busy and it is "less than 8 hours long". The help page says nothing about exporting the figures.

## A repeating meeting is one entry in the file

The `.ics` format is defined in [RFC 5545](https://www.rfc-editor.org/rfc/rfc5545). An event's `DTSTART` "specifies the very first instance in the recurrence set", and an `RRULE` line generates the rest. Dates removed from a series go in `EXDATE`.

An instance that was moved or changed becomes its own entry with a `RECURRENCE-ID`, whose value "is the original value of the DTSTART property of the recurrence instance". So the file never lists the dates a meeting actually happened; a program has to work them out.

Our sample year shows the size of the gap. Its two calendar files hold 253 event entries, which stand for 424 timed meetings and two all-day events once the repeats are worked out. A weekly Monday review was one entry plus two changed instances, standing for 48 meetings. A weekday standup was one entry for 120.

## Four more traps sit in the hours themselves

**Daylight saving time.** A night job from 1:00 to 4:00 on March 8, 2026, in New York lasts 2 hours, because the clocks jump forward. One from 0:30 to 3:30 on November 1 lasts 4. Python gets both wrong if you subtract the times as they come out of the file.

Its [datetime documentation](https://docs.python.org/3/library/datetime.html) says that when both times share one time zone object, "the tzinfo attributes are ignored" and "no time zone adjustments are done". We ran it: both jobs came out at 3 hours. Converting to UTC first gives 2 and 4.

Over the full year the two errors cancel, which is why they are easy to miss, but March and November are each off by an hour.

**All-day events.** RFC 5545 stores them as a date with no time, and the end date is "the non-inclusive end of the event". A vacation from July 6 to 10 ends on July 11 in the file. Count all-day events as 24-hour days and our five-day vacation alone adds 120 hours. The script leaves them out of the hours and lists them separately.

**Declined and canceled meetings.** RFC 5545 stores each guest's answer in a `PARTSTAT` parameter, and a No is `DECLINED`. If a declined invitation is in your export, it still has a start and an end. A canceled instance of a series is an entry with `STATUS:CANCELLED`. The expansion library we used returned the canceled instance as a normal meeting, so the script skips it on purpose.

**The same meeting twice.** The .zip holds all your calendars. If one event sits in two of them under the same `UID`, it is one meeting. The script counts each `UID` at each start time once.

One rarer case: an event with a start time and no end. RFC 5545 says it "ends on the same calendar date and time of day" it starts, so it lasts zero hours. The script counts it as zero and prints a warning.

## Our test: a made-up year with an answer key

We generated two calendar files for 2026 in the New York time zone and kept an answer key of hours per project per month. The key was computed by listing every meeting directly in Python, without any calendar library, so it could catch a mistake in one. The sample, all invented:

- A weekly review every Monday, 10:00 to 11:00, across both clock changes. Three holidays removed with `EXDATE`, one meeting moved to a Tuesday and lengthened to 2 hours, one canceled.
- A 15-minute weekday standup, 120 times from February 2.
- A month-end close on the last day of each month, written as `BYMONTHDAY=-1`.
- 240 one-off meetings at random times, stored in UTC, tagged with three made-up clients or left untagged.
- The two daylight saving night jobs, an evening that runs past midnight, two all-day events, one declined invitation and one meeting present in both calendars.

The table shows what each way of adding up the file got right. The key has 48 cells: four project tags times 12 months.

| Method | Total hours | Acme, the weekly-review client | Cells right |
|---|---|---|---|
| Answer key | 434.75 | 132.75 | 48 of 48 |
| Each entry once, no expansion | 334.50 | 60.25 | 26 of 48 |
| Expanded, times subtracted as stored | 434.75 | 132.75 | 46 of 48 |
| Expanded, declined, canceled and duplicate kept | 437.75 | 133.75 | 45 of 48 |
| Expanded, all-day events as 24-hour days | 578.75 | 156.75 | 46 of 48 |
| The script below | 434.75 | 132.75 | 48 of 48 |

The row for times subtracted as stored is the dangerous one. Its total matches, so a spot check of the year would pass it.

## Step 1: put the project in the event title

The script needs to know which project an event belongs to, and a calendar export has no project field. The simplest convention is a tag in square brackets at the start of the title: `[Acme] Weekly review`. The script takes the text inside the first pair of brackets. Events without one land under `(untagged)`, so you can see how much time you have not assigned.

If you already use another convention, such as `Acme:` at the start, pass your own pattern: `--tag "^([^:]+):"`. Group 1 of the pattern is the tag.

## Step 2: export the calendars

Use **Settings**, **Import & export**, **Export** on a computer, as above. Keep the .zip as it is; the script reads it directly. Exporting one calendar gives a single `.ics` file, which works too.

## Step 3: install two libraries and run the script

The script needs Python 3.10 or newer, because [icalendar 7.3.0](https://pypi.org/project/icalendar/) requires it. That library (BSD-2-Clause license) reads the file. [recurring-ical-events 3.8.2](https://pypi.org/project/recurring-ical-events/) (LGPL-3.0 or later) works out the repeats. Its [compatibility page](https://recurring-ical-events.readthedocs.io/en/latest/reference/compatibility.html) lists `RRULE`, `RDATE`, `EXDATE`, edited instances and daylight saving time as supported.

On macOS or Linux:

```sh
python3 -m venv .venv
source .venv/bin/activate
pip install icalendar==7.3.0 recurring-ical-events==3.8.2
python calendar_hours.py calendar-export.zip --start 2026-01-01 --end 2027-01-01 --me you@example.com --out hours.csv
```

On Windows, activate with `.venv\Scripts\activate` instead. `--end` is the day after the last day you want, so a month is `--start 2026-10-01 --end 2026-11-01`. `--me` is the address your calendar invitations go to; without it, declined meetings count. Days and months follow the time zone named in the first file's `X-WR-TIMEZONE` line (UTC if there is none), or the one you pass with `--tz America/Chicago`.

## The script

Save this as `calendar_hours.py`.

```python
"""Total hours per project tag from Google Calendar .ics exports.

Usage:
  python calendar_hours.py export.zip --start 2026-01-01 --end 2027-01-01 \
      --me you@example.com --out hours.csv

Accepts .ics files or the .zip that Google Calendar's "Export" button downloads.
The tag is the text in the first [square brackets] of an event's title.
"""
import argparse
import csv
import re
import sys
import zipfile
from collections import defaultdict
from datetime import date, datetime, time, timedelta, timezone
from pathlib import Path
from zoneinfo import ZoneInfo

import icalendar
import recurring_ical_events

UTC = timezone.utc


def load_calendars(paths):
    """Yield (name, calendar) for every .ics file, including those inside .zip files."""
    for p in map(Path, paths):
        if p.suffix.lower() == ".zip":
            with zipfile.ZipFile(p) as z:
                for name in sorted(z.namelist()):
                    if name.lower().endswith(".ics"):
                        yield name, icalendar.Calendar.from_ical(z.read(name))
        else:
            yield p.name, icalendar.Calendar.from_ical(p.read_bytes())


def declined_by(event, me):
    """True if the attendee with address `me` answered No."""
    if not me:
        return False
    attendees = event.get("ATTENDEE", [])
    if not isinstance(attendees, list):
        attendees = [attendees]
    for a in attendees:
        if str(a).lower().removeprefix("mailto:") == me:
            return str(a.params.get("PARTSTAT", "")).upper() == "DECLINED"
    return False


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("files", nargs="+")
    ap.add_argument("--start", required=True, help="first day, YYYY-MM-DD")
    ap.add_argument("--end", required=True, help="day after the last day, YYYY-MM-DD")
    ap.add_argument("--tz", help="time zone for days and months (default: the calendar's)")
    ap.add_argument("--me", help="your address; events you declined are skipped")
    ap.add_argument("--tag", default=r"\[([^\]]+)\]", help="regex; group 1 is the tag")
    ap.add_argument("--out", default="hours.csv", help="one row per counted event")
    args = ap.parse_args()

    try:
        calendars = list(load_calendars(args.files))
    except (ValueError, zipfile.BadZipFile, OSError) as err:
        sys.exit(f"Could not read the input: {err}")
    if not calendars:
        sys.exit("No .ics files found in the input.")
    tzname = args.tz or str(calendars[0][1].get("X-WR-TIMEZONE", "UTC"))
    tz = ZoneInfo(tzname)
    start = datetime.combine(date.fromisoformat(args.start), time(), tz)
    end = datetime.combine(date.fromisoformat(args.end), time(), tz)
    me = (args.me or "").lower()
    tag_re = re.compile(args.tag)

    rows, all_day, seen = [], [], set()
    skipped = defaultdict(int)
    for cal_name, cal in calendars:
        # Ask for a wider window, then keep only events that START inside the range,
        # so an event is never counted in two reports.
        query = recurring_ical_events.of(cal).between(start - timedelta(days=2), end + timedelta(days=2))
        for ev in query:
            s = ev["DTSTART"].dt
            e = ev["DTEND"].dt if "DTEND" in ev else s
            title = str(ev.get("SUMMARY", "")).strip()
            if str(ev.get("STATUS", "")).upper() == "CANCELLED":
                skipped["cancelled"] += 1
                continue
            if declined_by(ev, me):
                skipped["declined"] += 1
                continue
            if not isinstance(s, datetime):  # all-day: a DATE, not a time
                if start.date() <= s < end.date():
                    all_day.append((s, e, title))
                continue
            if s.tzinfo is None:  # floating time: read it in the report's zone
                s, e = s.replace(tzinfo=tz), e.replace(tzinfo=tz)
            if not start <= s < end:
                continue
            key = (str(ev.get("UID", "")), s.astimezone(UTC))
            if key in seen:  # the same meeting exported from two calendars
                skipped["duplicate"] += 1
                continue
            seen.add(key)
            # Convert to UTC before subtracting: Python ignores the offset when both
            # times share one tzinfo, which is wrong across a daylight saving change.
            hours = (e.astimezone(UTC) - s.astimezone(UTC)).total_seconds() / 3600
            m = tag_re.search(title)
            tag = m.group(1).strip() if m else "(untagged)"
            local_s = s.astimezone(tz)
            rows.append([local_s.date().isoformat(), local_s.strftime("%H:%M"),
                         e.astimezone(tz).strftime("%Y-%m-%d %H:%M"), round(hours, 4),
                         tag, title, cal_name])

    if not rows and not all_day:
        sys.exit("No events in that date range.")
    rows.sort()
    with open(args.out, "w", newline="", encoding="utf-8-sig") as f:  # BOM: Excel reads UTF-8
        w = csv.writer(f)
        w.writerow(["date", "start", "end", "hours", "tag", "title", "calendar"])
        w.writerows(rows)

    totals = defaultdict(float)
    for r in rows:
        totals[(r[0][:7], r[4])] += r[3]
    print(f"Time zone {tzname}; {len(rows)} timed events written to {args.out}")
    print("month    tag            hours")
    for (month, tag), h in sorted(totals.items()):
        print(f"{month}  {tag:<13} {h:7.2f}")
    for tag in sorted({t for _, t in totals}):
        print(f"total    {tag:<13} {sum(h for (_, t), h in totals.items() if t == tag):7.2f}")

    zero = [r for r in rows if r[3] <= 0]
    if zero:
        print(f"Check: {len(zero)} event(s) with no length, e.g. {zero[0][0]} {zero[0][5]!r}")
    by_start = sorted((datetime.fromisoformat(f"{r[0]}T{r[1]}"), datetime.fromisoformat(r[2]), r[5])
                      for r in rows)
    clashes, latest_end = 0, None
    for s, e, _ in by_start:
        if latest_end and s < latest_end:
            clashes += 1
        latest_end = max(latest_end or e, e)
    if clashes:
        print(f"Check: {clashes} event(s) start before an earlier one ends (double-booked?)")
    if all_day:
        days = sum((e - s).days if isinstance(e, date) and e > s else 1 for s, e, _ in all_day)
        print(f"Not counted: {len(all_day)} all-day event(s) covering {days} day(s)")
    for reason, n in sorted(skipped.items()):
        print(f"Skipped: {n} {reason}")


if __name__ == "__main__":
    main()
```

Three choices in it are worth knowing. An event counts in the period where it starts, so a meeting from 22:00 to 01:00 is counted whole on its first day, never split or counted twice. The file starts with a byte-order mark: as our [guide to CSV imports](/articles/excel-csv-dates-leading-zeros/) explains, Excel's import only recognizes UTF-8 automatically when the file has one, and accented names depend on it. Running it again overwrites the output, so a repeated run never doubles the rows.

## Step 4: read the output before you trust it

On our sample year it printed a table per month and project, then the totals and the checks:

```text
total    (untagged)      85.50
total    Acme           132.75
total    Beta           117.50
total    Gamma           99.00
Check: 50 event(s) start before an earlier one ends (double-booked?)
Not counted: 2 all-day event(s) covering 6 day(s)
Skipped: 1 cancelled
Skipped: 1 declined
Skipped: 1 duplicate
```

Every one of the 48 month-and-project figures matched the answer key. Three runs on the 424 events took 0.31 to 0.40 seconds each.

The check lines are the part to read. The 50 overlaps are real in our sample, because the random one-off meetings often landed on top of the standing ones.

In your calendar an overlap means two events were counted for the same hour. Decide which one you actually attended, then rename or delete the other. "No length" means an event without an end time. The all-day count tells you about vacations and offsites the hours leave out.

`hours.csv` has one row per meeting: date, start, end, hours, tag, title and the calendar it came from. Open it in Excel or Sheets and build a pivot table with tag as rows and date grouped by month as columns. Or filter one client and one month and attach the rows to the invoice.

We also fed the script an empty .zip, a range with no events and a file that is not a calendar. Each stopped with a one-line message and exit code 1. A separate small file confirmed that an event with a `DURATION` instead of an end time is counted correctly and that an event with no end is flagged.

## Without Python: a script inside Google Sheets

If you work in Sheets, Apps Script can read the calendar directly, with no export. We could not run this, because the sandbox we worked in had no Google account. Treat it as a starting point and check its output against a week you know.

Google's reference says [`getEvents(startTime, endTime)`](https://developers.google.com/apps-script/reference/calendar/calendar) "returns events that start during the given time range, end during the time range, or encompass the time range". So the code below drops events that started before the range, the same rule as the Python script. Times are read in "the script's time zone, which may be different from the calendar's time zone".

[`CalendarEvent`](https://developers.google.com/apps-script/reference/calendar/calendar-event) has `isAllDayEvent()` and `getMyStatus()`, and the [`GuestStatus`](https://developers.google.com/apps-script/reference/calendar/guest-status) value `NO` means "the guest has indicated they are not attending".

```js
// Untested. Bind it to the spreadsheet that should get the hours.
function calendarHours() {
  const start = new Date(2026, 0, 1); // January 1, in the script's time zone
  const end = new Date(2027, 0, 1);   // the day after the last day
  const rows = [['start', 'hours', 'tag', 'title']];
  for (const ev of CalendarApp.getDefaultCalendar().getEvents(start, end)) {
    if (ev.isAllDayEvent()) continue;
    if (ev.getMyStatus() === CalendarApp.GuestStatus.NO) continue;
    if (ev.getStartTime() < start) continue; // began before the range
    const hours = (ev.getEndTime().getTime() - ev.getStartTime().getTime()) / 3600000;
    const m = ev.getTitle().match(/\[([^\]]+)\]/);
    rows.push([ev.getStartTime(), hours, m ? m[1].trim() : '(untagged)', ev.getTitle()]);
  }
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}
```

A JavaScript `Date` holds milliseconds since January 1, 1970, UTC, which [MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date) calls "timezone-agnostic", so the daylight saving trap does not apply here. This version reads only your default calendar and does not look for the same meeting in two calendars. The reference also does not say in so many words that each repeat of a series comes back as its own event, one more reason to test it on a known week.

## When a calendar is the wrong source

A calendar records what you planned, not what happened. A meeting that ran 20 minutes over, or a work block you spent on something else, is wrong in the file and wrong in the totals. For billing by the minute, a timer or a tracker is the better record.

Our comparison of [automatic time trackers](/articles/time-trackers-repeat-work/) shows where five of them keep their logs. The calendar route fits when your day is mostly booked time: client calls, workshops, scheduled focus blocks. It also fits when you need a fast estimate for a month that has already gone.

Two smaller limits. The script trusts your tags, so an untagged meeting is never guessed into a project. And it reads a snapshot: events changed after the export are not in it, so export again before each billing run instead of reusing last month's file.

If the monthly run becomes a habit, it is a candidate for a schedule. Our piece on [scheduled scripts that fail silently](/articles/tiny-automation-in-code/) covers what to add before it runs without you.

## How we made this

On October 26, 2026, we read Google's export and Time Insights help pages, the Apps Script reference, RFC 5545, Python's datetime documentation and both libraries' pages. We generated a sample year of 424 meetings in two calendar files with an answer key. We ran the script on it with Python 3.11.17, icalendar 7.3.0 and recurring-ical-events 3.8.2, and ran simpler versions on the same files for the comparison rows. We had no Google account, so we did not export a real calendar, which may contain fields our files lack, and the Apps Script code is untested.

## Sources

- Google Calendar Help, [Export events from your Google Calendar](https://support.google.com/calendar/answer/37111?hl=en), read October 26, 2026
- Google Calendar Help, [View how you spend time in meetings](https://support.google.com/calendar/answer/10738043?hl=en), read October 26, 2026
- IETF, [RFC 5545: Internet Calendaring and Scheduling Core Object Specification (iCalendar)](https://www.rfc-editor.org/rfc/rfc5545), sections 3.3.5, 3.6.1, 3.8.4.4 and 3.8.5.1, read October 26, 2026
- Python documentation, [datetime: basic date and time types](https://docs.python.org/3/library/datetime.html), read October 26, 2026
- PyPI, [icalendar](https://pypi.org/project/icalendar/) and [recurring-ical-events](https://pypi.org/project/recurring-ical-events/), read October 26, 2026
- recurring-ical-events, [Compatibility and feature list](https://recurring-ical-events.readthedocs.io/en/latest/reference/compatibility.html), read October 26, 2026
- Google Apps Script reference, [Calendar](https://developers.google.com/apps-script/reference/calendar/calendar), [CalendarEvent](https://developers.google.com/apps-script/reference/calendar/calendar-event) and [GuestStatus](https://developers.google.com/apps-script/reference/calendar/guest-status), read October 26, 2026
- MDN Web Docs, [Date](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date), read October 26, 2026
- 28creation test, October 26, 2026: two generated calendar files for 2026 (253 entries, 424 timed meetings) scored against an answer key computed without a calendar library