How I use Toggl and Google Sheets for Automated Invoicing

First things first, Toggl is a great tool. If you're doing any kind of freelance work, I can't recommend it enough. And the Premium versions would pretty much do everything it is that I do with Google Sheets. So why create an integration at all? 90% of the time, it's just me, and the team reporting features are something that I don't need (and hence don't want to pay for).

I've been using Toggl as my time tracker for many years, but up until a year ago was doing my invoicing manually. It was a horrible process, calculating time, creating PDF invoices, attaching to emails. So when I accidently switched two invoices between clients (and considering that one invoice stipulated a time donation while the other did not.. cringe), I finally got to putting something together for myself.

The code isn't difficult at all to understand, and it's been a useful tool that has changed invoicing from a few hours work, to a few button clicks.

Enter Google Sheets

I used Google Sheets to create a workbook that contains 3 sheets: Client Information, an Invoice Template, and Report Data.
The end goal being to create an invoice as a new sheet (copied from the template), alter the invoice with client specific infomration (like addresses and rates) from Client Information, and then fill the invoice with the Report Data (which came from Toggl).


Client Information


The Template

Something pretty that is going to end up in the client's inbox, but get gets filled from the Client Information sheet.

Enter Toggl

The only thing I have to be sure of is that whatever name I use for the client here, I make sure to match in 'Client Information' sheet in the Google Sheets workbook.


Toggl UI

Google Scripts to put it together

The 'Report Data' sheet in my Google Workbook needs to be filled with the data for the time period that I'm invoicing on, and for that I use Google Scripts to access the Toggl API.


Getting Toggl Data

A bit of code to grab the data and drop it into Report Data sheet. You'll obviously need your own API key and workspace Id. Look under your Profile in Toggl for your API Key.


I first get the dates that I want to create invoices for, and use that to filter the query to the Toggl API.


Prepping URLs is ugly, so in gets contained in it's own function.


Data is returned as a JSON object, and then parsed into a 2d array.




The 2d array is then pushed into the 'Report Data' sheet where I can look it over quickly to make sure the entries look right.


Creating Invoices

My invoices are named by a timestamp YYYYMMDD-XX, where XX is the number of invoices that I'm printing for the month. I get the YYYYMMMDD part from an input box.

Create an object where I'll add all ReportData based on the client name. If there are 5 clients this month, then there will be 5 custom objects (workingclientObject) in the 'clientObjects' array.


Now go into the Client Information sheet and get that data. We're matching the 'clientObjects' array and the Client Information sheet data on the client name.


For every workingClientObject in 'clientObjects' array, create a new sheet and add it to the workbook. Copy it from the Invoice Template sheet, and then fill it.


Prep the Emails

After I've looked at the invoices, it's time to prep the emails.

The invoices are sheets in the workbook. I want to create PDFs for every sheet except my main three sheets.

After I create the PDF, I want to copy that PDF into my Invoice folder for that client. That folder id is in the Client Information sheet.


We get the PDF of the sheet by calling out to this workbook via UrlFetch with specific options about how I want the sheet to 'print'.



Drop the PDF into the invoice folder.

Create an email draft and attach the PDF.


And at this point, I'm done. I've clicked three buttons, answered some questions about dates and the invoice name, and my Email Drafts folder has emails waiting for me to send. I'll give the emails a look, and any notes that I need to, and send them off.

Source Code

While I'm not prepping the Google Sheets workbook, I'm happy to provide the code that you'll need to use Google Sheets and Toggl in the same way that I do. You'll need to make alterations to the script to add your API keys and Workspace Ids from Toggl, and also account for the fact that your Invoice Tempalte will look different than mine, putting data into different cells.


Google Scripts Code File


If you're looking for an implemenation of this for your organization, feel free to reach and let me know.

Corey Broderick
coreybroderick@gmail.com

Phone

+1 (647) 367-1601

+502 5985-5412

Address

Port Hope, Ontario, Canada
Gutemala City, Guatemala

Email

coreybroderick@gmail.com