Record time and activities in Calendar and Sheets
Create calendar events for your activities, and synchronize to your timesheet in sheets for reporting.
Last updated: December, 2020
Contributed by Jasper Duizendstra, follow me at @Duizendstra
Keeping track of the time spent on project for customers can be a challenge. Not all time writing apps are user friendly and sometimes you need to record your time in multiple systems. You can end up spending a lot of time writing time on a daily basis in a stubborn system and synchronize that time to other systems.
To solve this problem we can use the main time related application we have open every day, Google Calendar. In an additional calendar we can quickly add an event for the appropriate timeslot. Then add a short description and forget about it. At the end of the week you synchronize the calendar with a Google sheet.
You can categorize this information by customer, project and task based on the description and the data is structured for further processing. Use the dashboard or write a simple query to report or copy in other time sheets.
SpreadsheetAppservice is used to write the events to the sheet.
- Custom Menus are used to trigger the synchronization.
CalendarAppservice is used to write the events to the sheet.
- Create your secondary calendar: Create a new calendar.
- Copy this sheet.
- Open the sheet and wait until the myTime menu option appears. (This wil take 10 to 20 seconds.)
- Select the Settings option from the myTime menu. An authorization dialog will appear
- Authorize the script.
- Run the MyTime -> Settings option again, create the configuration and save it.
- Add some events to the secondary calender and hit MyTime -> Sync Calendar events calendar events.
This solution is based on this article
To get started with Google Apps Script, try out the codelab which guides you through the creation of your first script.
You can also view the full source code of this solution on GitHub to learn more about how it was built.
Were you able to get the solution up and running?
If you have an idea for another solution you'd like to see featured in our gallery submit a request on the GitHub issue tracker.