Clockify_Create_time_entries_database_on_workspace.ipynb

Kernel: Python 3

Clockify - Create time entries database on a workspace

Give Feedback | Bug report

Tags: #clockify #timeentry #database #workspace #user #create

Author: Florent Ravenel

Last update: 2023-07-26 (Created: 2023-07-26)

Description: This notebook creates a time entries database on a specific timeframe, adding client, project and task name. It is usefull for organizations to track time entries and optimize their workflow.

References:

• Clockify API Documentation

Input

Import libraries

import requests
import naas
import pandas as pd
from datetime import datetime

Setup variables

Get your Clockify API key and set the variables below:

• api_key: Clockify API Key

• workspace_id: ID of the workspace

• user_id: ID of the user to get time entries from

• start_date: Start date of the timeframe to create the time entries database

• end_date: End date of the timeframe to create the time entries database

api_key = naas.secret.get("CLOCKIFY_API_KEY") or "YOUR_API_KEY"
workspace_id = "626f9e3b36c2670314c0386e" #"<WORKSPACE_ID>"
start_date = "2023-01-01"
end_date = datetime.now().astimezone().isoformat()#.strftime("%Y-%m-%d")

Model

Function: Flatten the nested dict

# Flatten the nested dict
def flatten_dict(d, parent_key='', sep='_'):
    """
    Flattens a nested dictionary into a single level dictionary.

    Args:
        d (dict): A nested dictionary.
        parent_key (str): Optional string to prefix the keys with.
        sep (str): Optional separator to use between parent_key and child_key.

    Returns:
        dict: A flattened dictionary.
    """
    items = []
    for k, v in d.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_dict(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return dict(items)

Function: Get referentials from workspace

def get_data(api_key, workspace_id, endpoint):
    # Init
    page = 1
    df = pd.DataFrame()
    
    while True:
        # Requests
        url = f"https://api.clockify.me/api/v1/workspaces/{workspace_id}/{endpoint}"
        headers = {
            "X-Api-Key": api_key
        }
        params = {
            "page": page,
            "page-size": 100
        }
        res = requests.get(url, headers=headers, params=params)
        data = res.json()
        if len(data) > 0:
            for d in data:
                res = flatten_dict(d)
                tmp_df = pd.DataFrame([res])
                df = pd.concat([df, tmp_df])
        else:
            break
        page += 1
    return df.reset_index(drop=True)

Get all users

df_users = get_data(api_key, workspace_id, "users")
df_users = df_users.rename(columns={"id": "userId", "name": "userName"})
df_users = df_users[["userId", "userName", "email"]]
print("Users fetched:", len(df_users))
df_users.head(1)

Get time entries

def get_time_entries(
    api_key,
    workspace_id,
    user_id,
    start_date,
    end_date
):
    # Init
    start_date = datetime.strptime(start_date, "%Y-%m-%d").astimezone().isoformat() # Format date
    page = 1
    df = pd.DataFrame()
    
    # Get raw data
    while True:
        url = f"https://api.clockify.me/api/v1/workspaces/{workspace_id}/user/{user_id}/time-entries"
        headers = {"X-Api-Key": api_key}
        params = {
            "start": start_date,
            "end": end_date,
            "page": page,
            "page-size": 100
        }
        res = requests.get(url, headers=headers, params=params)
        data = res.json()
        if len(data) > 0:
            for d in data:
                res = flatten_dict(d)
                tmp_df = pd.DataFrame([res])
                df = pd.concat([df, tmp_df]).reset_index(drop=True)
        else:
            break
        page += 1
    return df.reset_index(drop=True)

# Init
database = pd.DataFrame()

# Loop
for row in df_users.itertuples():
    user_id = row.userId
    user_name = row.userName
    user_email = row.email
    
    # Get entries
    df_time_entries = get_time_entries(api_key, workspace_id, user_id, start_date, end_date)

    # Concat
    database = pd.concat([database, df_time_entries]).reset_index(drop=True)
    
print("Time entries fetched:", len(database))
database.head(3)

Get all projects

df_projects = get_data(api_key, workspace_id, "projects")
df_projects = df_projects.rename(columns={"id": "projectId", "name": "projectName"})
df_projects = df_projects[["projectId", "projectName", "clientId"]]
print("Projects fetched:", len(df_projects))
df_projects.head(1)

Get all clients

df_clients = get_data(api_key, workspace_id, "clients")
df_clients = df_clients.rename(columns={"id": "clientId", "name": "clientName"})
df_clients = df_clients[["clientId", "clientName"]]
print("Clients fetched:", len(df_clients))
df_clients.head(1)

Output

Create final database

Enrich data with referentials from workspace

final_db = database.copy()

# Final DB
final_db = pd.merge(final_db, df_users, how="left", on="userId")
final_db = pd.merge(final_db, df_projects, how="left", on="projectId")
final_db = pd.merge(final_db, df_clients, how="left", on="clientId")

# Select column
to_select = [
    "id",
    "description",
    "isLocked",
    "timeInterval_start",
    "timeInterval_end",
    "timeInterval_duration",
    "userId",
    "userName",
    "email",
    "projectId",
    "projectName",
    "clientId",
    "clientName",
]
final_db = final_db[to_select]
print("Time entries fetched:", len(final_db))
final_db.head(1)