Categories
Using Google Workspace

Track your time in Google Calendar and Google Sheets

Want to track the amount of time you’ve spent on a particular project, client or activity? Learn how Google Calendar, Google Sheets and Zapier may be the answer in this easy to read, practical article.

We often get questions in the Google Calendar Help Forum around how to track time spent on a particular project, client and/or activity. This is particularly important for freelancers or small business owners who charge by the hour or need to track the amount of time they’ve spent on a particular project.

There’s currently no easy way to do this in Google Calendar but then yesterday I read an article from Zapier that showed a nice workflow that I believe could be helpful.

What or Who is Zapier?

First, if you’re not familiar with Zapier it is online software that allows you to create automated workflows without writing any code. These can range from very simple (think sending a tweet and then having that tweet saved automatically in a Google Sheet); to quite complicated (think adding details from an email to a Google Sheet that then creates a Google Calendar event and perhaps also creates a task)

Time Tracking with Google Calendar, Google Sheets and Zapier

In this article we’re going to look at a Zap that creates rows in a Google Sheet every time a Google Calendar event is created – and in fact you can choose whether to export specific events based on particular search terms too!

You can then use formulas within Google Sheets to calculate the amount of time you’ve spent on a particular project.

Step One: Setting up the Google Sheet

It’s important to do this even before you create a Zapier account, as it will form the basis of the Zap.

  • In a new browser tab type sheets.new – this will create a new Google Sheet
  • Add the following columns:
    • Event Begins
    • Event Ends
    • Duration
    • Description
  • Name the workbook something like “Tracking Project Work”

Step Two: Creating an account with Zapier

You’ll need to create an account with Zapier before you can do anything. You can log in using your Google account – providing your Google Workspace admin has not turned off that functionality! Otherwise you can use Facebook or even good old-fashioned email and passphrase.

You’ll also need to connect your Google Calendar and Google Sheets accounts. Be aware that this will allow Zapier to for example “See, edit, share, and permanently delete all the calendars you can access using Google Calendar”. If you’re in an industry with strong privacy requirements then make sure you’re comfortable with Zapier’s privacy policy etc before continuing.

When you create an account with Zapier you’ll be placed on a 14 day free trial of their premium features. At the end of that trial, if you don’t update your billing details you’ll be downgraded to the free version of Zapier which should be fine for the majority of small business owners.

The biggest limitation with the free version is that it only allows for 5 ‘Zaps’ (a Zap is one workflow) and 100 tasks (a task is counted every time a Zap successfully moves data or takes action for you) per month.

This particular Zap will use 1 task every time it creates a Google Sheet row from Google Calendar, so if you need to track more than 100 events you may need to upgrade to a paid plan.

Step 3: Choosing your Zap

Once you’ve created the spreadsheet and created a Zapier account, you can make a decision about which Zap you’d like to use.

I’ve created two different Zaps that you can choose from. Simply click on either link and then follow the steps to create your Google Calendar and Google Sheets accounts:

Some Notes

  • Select the Google calendar you wish to use by clicking in the radio button next to the Calendar name. I like the fact that you can select secondary calendars and I tend to use those quite heavily but if you put all your information into the main calendar then select that one.
  • After you connect Google Calendar, Zapier will try to find an event that has that search term, in the Calendar that you connected and will fail! This is completely normal and exactly what we want to see.
    • Switch over to your Google Calendar and create an event (make sure you include the search terms if you’ve selected that particular option. It doesn’t have to be the exact same term as per Zapier – tracking in the event description for example will work even if you’ve put [tracking] in Zapier.)
    • Switch back to Zapier and click on Try Again

Once you’re finished, click on Run Test.

Step four: Turn it on / Use the data

Switch over to your Google Sheet and you’ll see that the test event you crated has now been added to your Google Sheet.

If you’re happy with the way the Zap works then click Turn on Zap.

Yay! Now you’ve got a way to export Google Calendar events that pertain to a specific client, project or activity to Google Sheets but you’re not finished yet – you still need to use that data.

There’s lots of ways to do this – you may wish to sum up the amount of time you’ve spent on the project (the duration column) or maybe you want to count the amount of events you’ve logged against the project (counting the number of rows in the sheet) or maybe you’d like to know what month you worked on the project (Start and end dates).

The sky’s the limit!

Make the most of your Google Workspace investment

Small business owners wear many hats including that of Google Workspace administrator but I’m betting that’s not your most favourite role! However it is mine – in fact I love helping small business owners get the most out of their Google Workspace investment.

And one way I do that, is by sending you all the tips, news and resources you need to run Google Workspace in your small business, so you can spend less time on ‘all that stuff’ and more time running the business you love.

Published every Sunday night to set you up for the week ahead. Designed and written by a small business owner for small business owners. 

One reply on “Track your time in Google Calendar and Google Sheets”

Leave a Reply

Your email address will not be published.