Turn Google Calendar into a timesheet in Google Sheets
If your calendar already shows what you worked on, you do not need to fill in a timesheet by hand. Put the events in a sheet, and the hours become a column you can add up: a simple hours tracker for client work, with no extra app.
The short version
- Google Calendar has no “export to spreadsheet” button. Its own export makes an .ics file, which is not a table.
- An add-on for Google Sheets can copy your events into rows, with the start, the end and the hours for each one.
- Once the hours are in a column, one formula totals them by client or by project.
What you need
- A Google account with your work in Google Calendar.
- Google Sheets in a browser on a computer. The Sheets mobile apps do not run add-ons.
- DropScribe Calendar Export, an add-on for Google Sheets. It is free for 14 days with everything unlocked. After that, the Free plan exports 1 calendar and 50 events at a time.
Step 1: Export your events to a sheet
- Install DropScribe Calendar Export from the Google Workspace Marketplace and approve the permissions Google shows you.
- Open a spreadsheet and follow the menu below. The panel opens on the right.
Extensions then DropScribe Calendar Export then Open
- Under Calendars, tick the ones that hold your work.
- Under Date range, choose This month, Last month or Last 30 days, or click Pick dates for your own range.
- Under Save to, choose The same sheet. The export then always lands in a sheet named Calendar export, which keeps the formulas in step 2 simple.
- Click Export events.
One row per event. Hours is the end time minus the start time.
The sheet has ten columns: Calendar, Title, Start, End, Hours, All day, Location, Description, Guests and Link. The last row adds up the Hours column.
Try it on this month's calendar
Free for 14 days with everything unlocked. No card needed.
Step 2: Total the hours by client or project
The total at the bottom covers everything you exported. A timesheet usually needs it split up. Add a new sheet for your summary, so the next export does not overwrite it, and use one of these two formulas.
If each client has their own calendar
Column A of the export holds the calendar name and column E holds the hours. This formula lists every calendar with its total:
=QUERY('Calendar export'!A:E, "select A, sum(E) where A is not null group by A label sum(E) 'Hours'", 1)If the client's name is in the event title
Column B holds the title. This formula adds up the hours of every event with Northwind anywhere in its title:
=SUMIF('Calendar export'!B:B, "*Northwind*", 'Calendar export'!E:E)To turn hours into an amount to bill, multiply by your hourly rate, for example =B2*75.
If your spreadsheet uses a comma as the decimal mark, type a semicolon instead of the comma between the parts of each formula.
Step 3: Keep it up to date
On the Free plan, open the panel and click Export events whenever you want fresh numbers. Your summary sheet updates by itself, because it reads from the Calendar export sheet.
On Pro, switch on Daily refresh in the panel and choose an hour. The export then runs again every day, so the timesheet is current whenever you open it.
Things that trip people up
- All-day events have no hours. They are listed, but the Hours cell is left empty, so a day off does not add 24 hours to your total.
- Repeating events get one row each time they happen. A weekly meeting shows up four or five times in a month.
- Overlapping events both count. Two meetings at the same time each keep their own hours.
- Times follow the spreadsheet's time zone. You can see or change it in Google Sheets under File, then Settings.
- Free and busy calendars are left out. Only calendars where you can see the event details can be exported.
Questions
Does Google Calendar have an hours calculator?
No. Google Calendar shows when each event starts and ends, but it does not add the hours up. Once your events are exported to a sheet, the Hours column and its total work as an hours calculator for any calendar and any date range.
Can Google Calendar total my hours on its own?
Not in a form you can use for a timesheet. The Time Insights panel, which some work and school accounts have, shows how your time was split across meetings. It cannot send those numbers to a spreadsheet or group them by client.
Does exporting change anything in my calendar?
No. An export only reads your events and writes them into the spreadsheet. The add-on has no feature that edits or deletes an event.
Is it free?
Every install starts with 14 days of everything unlocked, with no card. After that the Free plan exports 1 calendar and 50 events at a time. Pro raises that to 20 calendars and 5,000 events and adds daily refresh, for $9.99 a month or $59 a year.
Where do my events go?
From Google Calendar to your Google Sheet, inside your own Google account. DropScribe runs no server for the add-on, so your events never reach us.