Automate Your Toggl Reporting With Zapier and Google Sheets

Do you work on retainer for your clients?

If you do, you probably run into the problem where you send your clients weekly updates on how many hours are left on their retainer or your clients always have to ask to see a report.

I knew there had to be a better way.

Of course, I could pay for some fancy system that I didn’t feel like I needed. I ended up creating it myself.

In the end, whenever I tracked time in Toggl, it automatically gets put on a master Google Spreadsheet. From this spreadsheet, it automatically gets distributed to my retainer clients. (Seriously, I don’t have to touch any of the spreadsheets. Everything automatically gets updated.)

What You’ll Need:

  • Toggl Account (the free version works just fine)
  • Zapier (the free version works just fine for this as well)
  • Google Spreadsheets (this is free)

I’ve broken down the process into 5 steps:

Step 1: Set Up Your Master Spreadsheet

Step 2: Set Up a Zap

Step 3: Create Tabs for Each Client

Step 4: Create a Google Sheet for Your Client

Payments Tab

Time Tab

Summary Tab

Step 5: Share With Your Clients

 

Step 1: Set Up Your Master Spreadsheet


Create a Google Sheet to be your master spreadsheet. This is for internal purposes and will hold the information from all of your clients.

I called mine “Toggl Time” (I know, I’m super creative) and have the following columns: Client, Date, Time, Description

Rename the sheets to whatever you’d like. I named mine “total”. This will be important when setting up your zap.

Step 2: Set Up a Zap


In your Zapier account, create a zap where:

  • The trigger is Toggl new time entry
  • The action is a new row in your Google Sheet

 

Step 3: Create Tabs for Each Client


Note: You could skip step 3 and create a combo formula for your time tab below but breaking it out was easier for me to follow.

On your master sheet (the one I called Toggl Time), create a separate tab for each of your clients. The name of each of these tabs should be unique. This could be the client name or number.

In cell A1, write a query formula to grab all of the rows with that particular client’s information.

I named the range on the total tab to AllTogglTime to make the query formula a little easier.

Step 4: Create a Google Sheet for Your Client


Create a Google Sheet for your client. Rename the sheet to something that makes sense. For example “Your Business Name – Your Client’s Name”

Create 3 tabs: Summary, Time, and Payments.

Payments Tab


We’ll start with the payments tab since that’s the easiest.

Go to your payments tab and manually enter the amount of hours they purchased (this part is still manual for me). Here’s what mine looks like:

Time Tab


Next is the Time tab.

In cell A1, create an import range formula to pull the data from your client’s tab on your Master Sheet.

Summary Tab


Make sure this tab is first because this is the tab that you want to format, look professional, and you want your clients to immediately see this when they open your spreadsheet.

I made all of the columns width 150.

Put the logo in the top left corner and your contact information next to it.

Write a little note to your client, if you wish.

Give them links to the tabs to provide more detail to them (to make it as easy as possible for them).

Create your summaries. I have formulas for the past 7 and 30 days. These formulas are sumifs formula using “today” as the parameter.

I then put a simple sum formula that takes the total payments (from the payments tab) and subtracts the total time spent (from the time tab).

I then removed the gridlines to make it look a little nicer by going to view > gridlines.

TIP: After this step, copy the spreadsheet and change formulas for each client so you don’t have to redo any of the formatting!

Step 5: Share With Your Clients


Share this spreadsheet with your clients so they can take a look whenever they want!

Do you need a more detailed step by step guide? For just $7, you can see the EXACT steps I’ve used, along with videos to guide you through each step here. BONUS: In this tutorial, you’ll also see how I created a summary for my master spreadsheet to give me an overview and also show me when a client’s hours are getting low!

If you’d like to skip the DIY and would like me to set this up for you (including the master sheet’s summary), reach out to me here and I’d be happy to help!

nicole_hatcherAmanda Leigh, with her business savvy tools and upbeat attitude, helps entrepreneurs develop systems that work for, not against them. By setting up project management systems and email management protocols curated specifically for each business, Amanda’s dedication to helping others thrive means making sure nothing is forgotten.

If you could use an extra set of hands in your business, Schedule a FREE Discovery Call here. flogo_rgb_hex-brc-site-250

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s