Solved: Syncing Clockify Time Logs to Quickbooks for Invoicing

#devops #programming #tutorial #cloud

🚀 Executive Summary

TL;DR: The article details an automated Python script solution to synchronize Clockify time logs with Quickbooks for invoicing, eliminating manual data entry and reducing errors. This custom integration fetches billable time entries from Clockify and creates corresponding TimeActivity objects in Quickbooks Online, significantly streamlining the billing process.

🎯 Key Takeaways

The automated sync leverages Python 3 with requests for Clockify API, python-dotenv for environment variables, and python-quickbooks for simplified Quickbooks OAuth2 authentication and API interactions.
Quickbooks Online OAuth2 requires a one-time manual authorization to obtain a refresh token, which is then used by the python-quickbooks library to automatically acquire new access tokens, valid for up to 101 days.
Critical implementation considerations for a robust sync include handling Clockify API pagination to retrieve all entries, enforcing strict naming conventions for accurate mapping between Clockify projects/users and Quickbooks customers/employees, and implementing idempotency checks to prevent duplicate TimeActivity creation in Quickbooks.

Syncing Clockify Time Logs to Quickbooks for Invoicing

Hey everyone, Darian here. Let’s talk about a workflow that genuinely gave me back a couple of hours every single month. I used to be the one manually exporting Clockify reports, checking them against project codes, and then painstakingly creating draft invoices in Quickbooks. It was tedious and, frankly, way too easy to make a costly mistake. After one too many billing cycles spent squinting at spreadsheets, I built this automated sync. It was a game-changer, and today I’m going to walk you through how to set it up.

Prerequisites

Before we dive in, make sure you have the following ready. This isn’t a beginner’s guide, so I’ll assume you have a handle on these basics:

Python 3 installed on your system.
Admin access to your Clockify Workspace to get an API key.
Developer access to your Quickbooks Online account to create an app.
A solid understanding of REST APIs and how they function.
A way to run a scheduled script (like cron on a Linux server).

The Guide: From Time Log to Draft Invoice

Alright, let’s get into the weeds. I’m focusing on the logic and the code here. I’ll skip the standard project setup steps like creating a directory or a virtual environment, since you likely have your own preferred workflow for that. Let’s jump straight to the good stuff.

Step 1: Get Your Credentials

First, we need to gather our keys. Store these securely and never commit them to version control.

Clockify API Key: In Clockify, go to your Profile Settings > API tab, and generate a new key.
Quickbooks App Credentials:
- Go to the Quickbooks Developer Portal and create a new app.
- Under the “Keys & OAuth” section for your app, you’ll find your Client ID and Client Secret.
- You also need to add a Redirect URI. For local testing, something like http://localhost:8080/callback is fine. We’ll only use this once to get our initial tokens.

I recommend using a config.env file to manage these secrets. It keeps them out of your code and makes configuration much easier. The file would look something like this:

CLOCKIFY_API_KEY="your_clockify_key_here"
CLOCKIFY_WORKSPACE_ID="your_workspace_id_here"
QUICKBOOKS_CLIENT_ID="your_qb_client_id_here"
QUICKBOOKS_CLIENT_SECRET="your_qb_client_secret_here"
QUICKBOOKS_REALM_ID="your_company_id_here"
# We'll get these two in the next step
QUICKBOOKS_ACCESS_TOKEN=""
QUICKBOOKS_REFRESH_TOKEN=""

Step 2: Environment and Dependencies

For this script, we’ll need a few Python libraries to do the heavy lifting. Specifically, we’ll use requests for the Clockify API, python-dotenv to load our config.env file, and python-quickbooks, which is a lifesaver for handling the complex Quickbooks OAuth2 authentication. You can install these with pip in your project environment.

Step 3: Authenticating with Quickbooks (One-Time Setup)

This is the most manual part of the process. We need to run a small script one time to authorize our application and get the long-lived refresh token. The python-quickbooks library simplifies this greatly.

Pro Tip: Refresh tokens for Quickbooks Online are valid for 101 days. A well-written script will automatically use the refresh token to get a new access token (and a new refresh token) with each run, so you should only have to do this manual step once.

Here’s a helper script to guide you through the initial authorization:

# initial_auth.py
import os
from dotenv import load_dotenv
from quickbooks.auth import AuthClient

load_dotenv('config.env')

def get_auth_url():
    auth_client = AuthClient(
        client_id=os.getenv("QUICKBOOKS_CLIENT_ID"),
        client_secret=os.getenv("QUICKBOOKS_CLIENT_SECRET"),
        redirect_uri="http://localhost:8080/callback", # Must match your app settings
        environment='sandbox',  # Use 'production' for your live account
    )
    url = auth_client.get_authorization_url([ "com.intuit.quickbooks.accounting" ])
    print(f"Please go to this URL and authorize the app:\n{url}")

def get_tokens():
    auth_client = AuthClient(
        client_id=os.getenv("QUICKBOOKS_CLIENT_ID"),
        client_secret=os.getenv("QUICKBOOKS_CLIENT_SECRET"),
        redirect_uri="http://localhost:8080/callback",
        environment='sandbox',
    )
    auth_code = input("Enter the auth code from the callback URL: ")
    realm_id = input("Enter the realmId from the callback URL: ")

    auth_client.get_bearer_token(auth_code, realm_id=realm_id)

    print("\n--- SAVE THESE IN YOUR config.env FILE ---")
    print(f"QUICKBOOKS_REALM_ID=\"{auth_client.realm_id}\"")
    print(f"QUICKBOOKS_ACCESS_TOKEN=\"{auth_client.access_token}\"")
    print(f"QUICKBOOKS_REFRESH_TOKEN=\"{auth_client.refresh_token}\"")

if __name__ == "__main__":
    print("Step 1: Generate Authorization URL")
    get_auth_url()
    print("\nStep 2: After authorizing, get tokens from the callback URL")
    get_tokens()

Run this script. First, it will print a URL. Paste that into your browser, log in to Quickbooks, and authorize the app. You’ll be redirected to your localhost address with a URL containing the code and realmId. Copy those values and paste them back into the script terminal when prompted. It will then print the tokens you need to save in your config.env file.

Step 4: The Main Sync Script Logic

Now we can build the core script that will run automatically. Here’s the structure I use:

Load environment variables.
Connect to Quickbooks using the saved refresh token.
Fetch billable time entries from Clockify for the last week.
Map Clockify projects/users to Quickbooks customers/employees.
Create TimeActivity objects in Quickbooks for each entry.

Here’s a simplified version of the main script:

# sync_script.py
import os
import requests
from datetime import datetime, timedelta
from dotenv import load_dotenv
from quickbooks import QuickBooks
from quickbooks.objects.timeactivity import TimeActivity
from quickbooks.objects.customer import Customer
from quickbooks.objects.employee import Employee

# --- 1. SETUP ---
load_dotenv('config.env')

def get_qb_client():
    return QuickBooks(
        auth_client=None, # The library handles this if we provide tokens
        refresh_token=os.getenv("QUICKBOOKS_REFRESH_TOKEN"),
        client_id=os.getenv("QUICKBOOKS_CLIENT_ID"),
        client_secret=os.getenv("QUICKBOOKS_CLIENT_SECRET"),
        realm_id=os.getenv("QUICKBOOKS_REALM_ID"),
        environment='sandbox',
    )

# --- 2. FETCH FROM CLOCKIFY ---
def get_clockify_entries():
    API_KEY = os.getenv("CLOCKIFY_API_KEY")
    WORKSPACE_ID = os.getenv("CLOCKIFY_WORKSPACE_ID")

    # Get time entries from the last 7 days
    start_date = (datetime.utcnow() - timedelta(days=7)).isoformat() + "Z"

    headers = {'X-Api-Key': API_KEY}
    url = f"https://api.clockify.me/api/v1/workspaces/{WORKSPACE_ID}/time-entries"
    params = {'start': start_date, 'billable': 'true'}

    response = requests.get(url, headers=headers, params=params)
    response.raise_for_status() # Raise an exception for bad status codes
    return response.json()

# --- 3. SYNC TO QUICKBOOKS ---
def sync_to_quickbooks(qb_client, entries):
    # In a real setup, you'd cache these lookups
    customers = Customer.all(qb=qb_client)
    employees = Employee.all(qb=qb_client)

    customer_map = {c.DisplayName: c.Id for c in customers}
    employee_map = {e.DisplayName: e.Id for e in employees}

    for entry in entries:
        # Basic mapping: Clockify Project Name -> QB Customer Name
        project_name = entry.get('project', {}).get('name')
        user_name = entry.get('user', {}).get('name')

        if not project_name or not user_name:
            print(f"Skipping entry with missing project or user: {entry['id']}")
            continue

        if project_name not in customer_map or user_name not in employee_map:
            print(f"Skipping entry for '{project_name}' - no matching QB customer/employee.")
            continue

        # Convert duration from ISO 8601 format (e.g., PT1H30M)
        duration = entry['timeInterval']['duration']
        hours = 0
        minutes = 0
        if 'H' in duration:
            hours = int(duration.split('H')[0].replace('PT', ''))
        if 'M' in duration:
            minutes_part = duration.split('H')[-1] if 'H' in duration else duration.replace('PT', '')
            minutes = int(minutes_part.split('M')[0])

        time_activity = TimeActivity()
        time_activity.NameOf = "Employee" # Or "Vendor"
        time_activity.EmployeeRef = employee_map[user_name]
        time_activity.CustomerRef = customer_map[project_name]
        time_activity.Description = entry['description']
        time_activity.TxnDate = entry['timeInterval']['start'].split('T')[0]
        time_activity.Hours = hours
        time_activity.Minutes = minutes
        time_activity.BillableStatus = "Billable"

        # You'd add logic here to prevent duplicates
        print(f"Creating time activity for {project_name}...")
        time_activity.save(qb=qb_client)

if __name__ == "__main__":
    print("Starting sync...")
    qb_client = get_qb_client()
    clockify_entries = get_clockify_entries()
    if clockify_entries:
        sync_to_quickbooks(qb_client, clockify_entries)
    print("Sync complete.")

Step 5: Automation with Cron

Once you’ve tested the script and it’s working reliably, you can automate it. On a Linux server, a cron job is perfect for this. I usually set mine to run early Monday morning to sync the previous week’s logs.

Pro Tip: Direct the output of your cron job to a log file. If something goes wrong, you’ll have a record of the error messages without having to check system mail. Example: >> /path/to/your/logs/sync.log 2>&1

Here’s what the cron job definition would look like. You would typically add this by running a command to edit your cron file.

0 2 * * 1 python3 script.py

This command tells the system to run python3 script.py at 2:00 AM every Monday.

Common Pitfalls (Where I Usually Mess Up)

Forgetting Pagination: The Clockify API returns a maximum of 50 entries per request. My first version of this script missed hours of billable time because I wasn’t checking for more pages. You need to implement a loop to make requests until you’ve received all the data for your time period.
Mismatched Names: This is the big one. “Project-A” in Clockify and “Project A” in Quickbooks are not the same. The script will fail to find a match. I now enforce a strict naming convention and use a pre-sync check to flag any discrepancies before the main script runs.
Idempotency: Running the script twice by accident could create duplicate time entries in Quickbooks, which is a nightmare to clean up. A simple fix is to store the ID of each synced Clockify entry in a local database or a file, and check against it before creating a new TimeActivity.

Conclusion

And that’s the core of it. This script forms a solid foundation for a reliable sync between your team’s time tracking and your accounting system. It eliminates manual data entry, reduces errors, and speeds up your entire invoicing process. From here, you can customize it to handle different billing rates, map Clockify tags to Quickbooks service items, or add more robust error handling and notifications. I hope this helps you reclaim some of your time. Let me know if you have any questions.