Record time and activities in Calendar and Sheets

Create calendar events for your activities, and synchronise to your timesheet in sheets for reporting.

Last updated: September, 2019

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.

Technology highlights

• The SpreadsheetApp service is used to write the events to the sheet.
• Custom Menus are used to trigger the synchronization.
• The CalendarApp service is used to write the events to the sheet.

Try it

1. Create your secondary calendar: Create a new calendar.
2. Copy this sheet.
3. Open the sheet and wait until the myTime menu option appears. (This wil take 10 to 20 seconds.)
4. Select the Settings option from the myTime menu. An authorization dialog will appear
5. Authorize the script.
6. Run the MyTime -> Settings option again, create the configuration and save it.
7. Add some events to the secondary calender and hit MyTime -> Sync Calendar events calendar events.

Next steps

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.

Feedback

Were you able to get the solution up and running?

It worked!

Great! Check out the "Next steps" section above to learn more.

There was a problem

Bummer, let us know what went wrong. If you have found a bug in the code, report the issue on GitHub or submit a pull request.

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.