Clockify_Send_activity_brief_to_Slack.ipynb

Kernel: Python 3

Clockify - Send activity brief to Slack

Give Feedback | Bug report

Tags: #clockify #slack #activity #brief #daily #weekly #automation

Author: Florent Ravenel

Last update: 2023-11-16 (Created: 2023-11-16)

Description: This notebook automates the process of sending a daily activity brief from Clockify to Slack. If today is Monday, the script will send the activity report from last week to Slack. For Tuesday to Friday, it will send a brief of the previous day's activity.

References:

• Clockify API Documentation

• Slack App

Input

Import libraries

import requests
import naas
import pandas as pd
from datetime import datetime, date, timedelta, time
from naas_drivers import slack

Setup variables

Pre-requisite

• Create Slack App

• In "Auth & Permissions" section, add user token Scopes: chat:write, groups:write, files:write

• Get your Clockify API key Mandatory

• api_key: Clockify API key

• workspace_id: ID of the workspace

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

• slack_token: User Slack token

• slack_channel: the channel where you wish to send the message

Optional

• cron: This variable represents the CRON syntax used to run the scheduler. More information here: https://crontab.guru/#0_12,18_*_*_1-5

# Mandatory
api_key = naas.secret.get("CLOCKIFY_API_KEY") or "YOUR_API_KEY"
workspace_id = "626f9e3b36c2670314c0386e" #"<WORKSPACE_ID>"
user_id = "626fa819f87fd71e0e1f392c"
slack_token = naas.secret.get("SLACK_USER_TOKEN")
slack_channel = "core-team-chat"

# Optional
cron = "0 8 * * 1-5"

Model

Get start date and end date

today = date.today() 

# Get the weekday as an integer (Monday is 0 and Sunday is 6)
weekday = today.weekday()
print("Weekday:", weekday)

if weekday == 0:
    days_to_subtract = (weekday) % 7 + 7
    first_day_of_last_week = today - timedelta(days=days_to_subtract)
    start_date = first_day_of_last_week.strftime("%Y-%m-%d")
    end_date = datetime.combine((first_day_of_last_week + timedelta(days=6)), datetime.max.time()).astimezone().isoformat()
elif weekday in [1, 2, 3, 4]:
    start_date = (today - timedelta(days=1)).strftime("%Y-%m-%d")
    end_date = datetime.combine((datetime.now() - timedelta(days=1)), datetime.max.time()).astimezone().isoformat()
print("Start date:", start_date)
print("End date:", end_date)

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 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)

# Get entries
database = get_time_entries(api_key, workspace_id, user_id, start_date, end_date)
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)

Create database

Enrich data with referentials from workspace

def create_database(
    df_init,
    df_projects,
    df_clients,
    filters=None
):
    # Init
    df = df_init.copy()
    
    # Final DB
    df = pd.merge(df, df_projects, how="left", on="projectId")
    df = pd.merge(df, df_clients, how="left", on="clientId")
    df["timeduration_Hours"] = round((pd.to_datetime(df["timeInterval_end"]) - pd.to_datetime(df["timeInterval_start"])).dt.total_seconds() / 3600, 2)

    # Select column
    to_group = [
        "clientName",
        "projectName",
        "description",
    ]
    to_agg = {
        "timeduration_Hours": "sum" 
    }
    df = df.groupby(to_group, as_index=False).agg(to_agg)
    return df
    
df_slack = create_database(
    database,
    df_projects,
    df_clients,
)
print(df_slack.timeduration_Hours.sum())
df_slack

Create Slack blocks

blocks = [
    {
        "type": "section",
        "text": {
            "type": "mrkdwn",
            "text": f"*Here is my brief of yesterday activity: {start_date}*",
        }
    }
]

clients = df_slack.clientName.unique()
for client in clients:
    # Filter on clients
    tmp_df = df_slack[df_slack["clientName"] == client].reset_index(drop=True)
    total = round(tmp_df.timeduration_Hours.sum())
    
    # Get clients
    blocks.append({"type": "section", "text": {"type": "mrkdwn", "text": f"*{client} : {total} hours*"}})
    
    # Get project details
    bullet_points = []
    for row in tmp_df.itertuples():
        project_name = row.projectName
        description = row.description
        hours = row.timeduration_Hours
        bullet_points.append(f"• {project_name}: {description} ({hours} hours)")
        
    for b in bullet_points:
        blocks.append({"type": "section", "text": {"type": "mrkdwn", "text": b}})
        
blocks

Output

Send message to Slack

slack.connect(slack_token).send(slack_channel, text="", blocks=blocks)

Add scheduler

naas.scheduler.add(cron=cron)

# naas.scheduler.delete()