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.
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
Step 1: Create a new calendar
If you plan to use an existing calendar, you can skip this step.
- Go to calendar.google.com.
- Next to Other calendars, click Add other calendars > Create new calendar.
- Name your calendar and click Create calendar.
- Add some events to the calendar.
Step 2: Set up the spreadsheet
Click the button below to make a copy of the Record time and activities sample spreadsheet.
Make a copy
Click myTime > Settings. You might need to refresh the page for this custom menu to appear.
When prompted, authorize the script.
Click myTime > Settings again.
From the list of available calendars, select the calendar you created and any other calendars you want to sync.
Configure the rest of the settings and click Save.
Click myTime > Sync calendar events.
Step 3: Set up the dashboard
- Go to the Categories sheet.
- Add customers, projects, and tasks.
- Go to the Hours sheet.
- For each synced event, select the customer, project, and task.
- 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
- The next section provides weekly totals and corresponds to the date
- The last three sections provide overall totals by task, project, and customer.
- The first section provides daily totals. To update the list of dates for the daily totals, change the date in cell
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.
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.