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

Objectives

• Understand what the solution does.
• Understand what the Apps Script services do within the solution.
• Set up your environment.
• Set up the script.
• Run the script.

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.

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.

Prerequisites

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.

Set up your environment

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.

Set up the script

Click the following button to make a copy of the Record time and activities sample spreadsheet. The Apps Script project for this solution is attached to the spreadsheet.
Make a copy

Run the script

Sync calendar events

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

2. When prompted, authorize the script. If the OAuth consent screen displays the warning, This app isn't verified, continue by selecting Advanced > Go to {Project Name} (unsafe).

3. Click myTime > Settings again.

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

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

6. Click myTime > Sync calendar events.

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.

Review the code

To review the Apps Script code for this solution, click View source code below:

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.

Next steps

• Custom menus in Google Workspace
• Calendar service reference