Record time and activities in Calendar and Sheets

Coding level: Beginner
Duration: 15 minutes
Project type: Automation with a custom menu

About this solution

Keep track of time spent on projects for customers. You can record your project-related time in Google Calendar, then sync it with Google Sheets to create a timesheet or import your activity into another timesheet management system. You can categorize your time by customer, project, and task.

Events in Calendar and Sheets

How it works

The script provides a sidebar that lets you select the calendars to sync, the time period to sync with, and whether to overwrite event titles and descriptions with information entered in the spreadsheet. Once those settings are configured, you can sync events and view your activities on a dashboard.

The script brings in events from the calendars and time period you specify from Calendar to the spreadsheet. You can add customers, projects, and tasks to the categories sheet and then tag the events accordingly in the hours sheet. This way, when you view the dashboard sheet, you can view total time by customer, project, and task.

Apps Script services

This solution uses the following services:

  • HTML Service: Builds the sidebar used to configure synchronization settings.
  • Properties Service: Stores the settings the user selects on the sidebar.
  • Calendar Service: Sends the event information to the spreadsheet.
  • Spreadsheet Service: Writes the events to the spreadsheet, and if configured, sends updated title and description information to Calendar.

Before you begin

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts might require administrator approval)
  • A web browser with access to the internet

Try it

Step 1: Create a new calendar

If you plan to use an existing calendar, you can skip this step.

  1. Go to calendar.google.com.
  2. Next to Other calendars, click Add other calendars > Create new calendar.
  3. Name your calendar and click Create calendar.
  4. Add some events to the calendar.

Step 2: Set up the spreadsheet

  1. Click the button below to make a copy of the Record time and activities sample spreadsheet.
    Make a copy

  2. Click myTime > Settings. You might need to refresh the page for this custom menu to appear.

  3. When prompted, click Review permissions and authorize the script.

  4. Click myTime > Settings again.

  5. From the list of available calendars, select the calendar you created and any other calendars you want to sync.

  6. Configure the rest of the settings and click Save.

  7. Click myTime > Sync calendar events.

Step 3: Set up the dashboard

  1. Go to the Categories sheet.
  2. Add customers, projects, and tasks.
  3. Go to the Hours sheet.
  4. For each synced event, select the customer, project, and task.
  5. Go to the Dashboard sheet.
    • The first section provides daily totals. To update the list of dates for the daily totals, change the date in cell A1.
    • The next section provides weekly totals and corresponds to the date selected in A1.
    • The last three sections provide overall totals by task, project, and customer.

Open the Apps Script project

The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.

Contributors

This sample was created by Jasper Duizendstra, Google Cloud Architect and Google Developer Expert. Find Jasper on Twitter @Duizendstra.

This sample is maintained by Google with the help of Google Developer Experts.