Copy Google Calendar events into a sheet with hours totalled

Short answer: Use a free Google Apps Script that lists every event in a date range with its start, end and hours, then totals the hours for each event name. Handy for timesheets and client billing. It runs in your own Google account; nothing is sent to us. Setup takes 4 steps: Make a Settings tab; Paste the script; Copy the events; Read the totals.

How to set it up

1. Make a Settings tab. Open a new Google Sheet and add a tab called Settings. B1 is the calendar ID (blank for your main calendar). Put the first date in B2 and the last date in B3.
2. Paste the script. Open Extensions, then Apps Script, paste in the code from our email, and save. Reload the sheet and a Calendar menu appears.
3. Copy the events. Choose Calendar, then Copy events. The Events tab lists each event's date, start, end, hours, name, location and guests. All-day events show as All day with 0 hours.
4. Read the totals. The Hours by event tab adds up the hours for each event name. Name events consistently, such as the client's name, and you get hours per client.

Want this set up for your business? Free written scope in 20 minutes

Get the script

Good to know

• Each run replaces both tabs, so change the dates and run it again for a new period.
• Times use the time zone set in the Apps Script project (Project Settings).

Questions

What does this script do?

It is a free Google Apps Script that lists every event in a date range with its start, end and hours, then totals the hours for each event name. Handy for timesheets and client billing.

How do I set it up?

In 4 steps: 1. Make a Settings tab; 2. Paste the script; 3. Copy the events; 4. Read the totals. The full steps are on this page.

Is it free, and does it send my data anywhere?

It is free: enter your email on this page and we email you the script (Code.gs). It runs in your own Google account; nothing is sent to us.

Can you install it for me?

Yes. We set it up in your Google account in a short Connect session, connect it to your sheet and check that it runs.

Want it wired into QuickBooks, your CRM or your payment system? Send us a short brief.