A Python script that generates a comprehensive Excel/Google Sheets Kanban-style task tracking (To Do, In Progress, Blocked, Done) compatible task tracking system with automated dashboards.

TeamTrack.py

import pandas as pd
import os
from datetime import datetime, timedelta
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference, LineChart
from openpyxl.worksheet.datavalidation import DataValidation


def create_task_prefix(name):
    if not name or len(name) < 2:
        return "TK"

    name_parts = name.split()
    if len(name_parts) == 1:
        return f"{name[0]}{name[-1]}".upper()
    else:
        return f"{name_parts[0][0]}{name_parts[-1][0]}".upper()


def create_team_tracking_sheet():
    print("===== Team Activity Tracking System =====")

    team_name = input("Enter team name: ")
    if not team_name:
        team_name = "My Team"

    print("\nEnter team members (one per line, enter 'done' when finished):")
    team_members = []
    while True:
        name = input("> ")
        if name.lower() == 'done':
            break
        team_members.append(name)

    if not team_members:
        print("No team members added. Exiting.")
        return

    week_start_options = {
        "1": "Monday",
        "2": "Tuesday",
        "3": "Wednesday",
        "4": "Thursday",
        "5": "Friday",
        "6": "Saturday",
        "7": "Sunday"
    }

    print("\nSelect week start day:")
    for key, day in week_start_options.items():
        print(f"{key}. {day}")

    week_start_choice = input("> ")
    if week_start_choice not in week_start_options:
        print("Invalid choice. Defaulting to Monday.")
        week_start_day = 0  # Monday
    else:
        week_start_day = int(week_start_choice) - 1

    wb = openpyxl.Workbook()
    default_sheet = wb.active
    wb.remove(default_sheet)

    member_prefixes = {}
    for member in team_members:
        prefix = create_task_prefix(member)
        counter = 1
        while prefix in member_prefixes.values():
            prefix = f"{prefix[0]}{counter}{prefix[-1]}"
            counter += 1
        member_prefixes[member] = prefix

    dashboard = wb.create_sheet("Dashboard")
    setup_dashboard(dashboard, team_members, member_prefixes, team_name)

    for member in team_members:
        member_sheet = wb.create_sheet(member)
        setup_member_sheet(member_sheet, member, member_prefixes[member])

    weekly_dashboard = wb.create_sheet("Weekly Activity")
    setup_weekly_dashboard(weekly_dashboard, team_members, team_name, week_start_day)

    monthly_dashboard = wb.create_sheet("Monthly Activity")
    setup_monthly_dashboard(monthly_dashboard, team_members, team_name)

    filename = f"{team_name.replace(' ', '_')}_tracker_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx"
    wb.save(filename)
    print(f"\nFile created successfully: {filename}")
    print(f"You can import this file into Google Sheets.")


def setup_dashboard(sheet, team_members, member_prefixes, team_name):
    sheet.sheet_properties.tabColor = "1072BA"

    title_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
    critical_fill = PatternFill(start_color="FFD9D9", end_color="FFD9D9", fill_type="solid")

    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                         top=Side(style='thin'), bottom=Side(style='thin'))

    sheet['A1'] = f"{team_name.upper()} - ACTIVITY DASHBOARD"
    sheet['A1'].font = Font(bold=True, size=16, color="FFFFFF")
    sheet['A1'].fill = title_fill
    sheet.merge_cells('A1:G1')
    sheet['A1'].alignment = Alignment(horizontal='center')

    current_date = datetime.now().strftime("%Y-%m-%d")
    sheet['A2'] = f"Last updated: {current_date}"
    sheet.merge_cells('A2:G2')
    sheet['A2'].alignment = Alignment(horizontal='center')

    headers = ["Team Member", "To Do", "In Progress", "Blocked", "Done", "Critical", "Total"]
    for col, header in enumerate(headers, 1):
        cell = sheet.cell(row=3, column=col, value=header)
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center')
        sheet.column_dimensions[get_column_letter(col)].width = 15

    for row, member in enumerate(team_members, 4):
        sheet.cell(row=row, column=1, value=member).alignment = Alignment(horizontal='left')

        sheet.cell(row=row, column=2, value=f"=COUNTIF('{member}'!C:C, \"To Do\")")
        sheet.cell(row=row, column=3, value=f"=COUNTIF('{member}'!C:C, \"In Progress\")")
        sheet.cell(row=row, column=4, value=f"=COUNTIF('{member}'!C:C, \"Blocked\")")
        sheet.cell(row=row, column=5, value=f"=COUNTIF('{member}'!C:C, \"Done\")")
        sheet.cell(row=row, column=6, value=f"=COUNTIF('{member}'!D:D, \"Critical\")")
        sheet.cell(row=row, column=7, value=f"=SUM(B{row}:E{row})")

        for col in range(1, 8):
            sheet.cell(row=row, column=col).border = thin_border
            sheet.cell(row=row, column=col).alignment = Alignment(horizontal='center')
            if col == 6:
                sheet.cell(row=row, column=col).fill = critical_fill

    total_row = len(team_members) + 4
    sheet.cell(row=total_row, column=1, value="TOTALS")
    sheet.cell(row=total_row, column=1).font = Font(bold=True)
    sheet.cell(row=total_row, column=1).alignment = Alignment(horizontal='left')

    for col in range(2, 8):
        col_letter = get_column_letter(col)
        cell = sheet.cell(row=total_row, column=col, value=f"=SUM({col_letter}4:{col_letter}{total_row - 1})")
        cell.font = Font(bold=True)
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center')

    chart = BarChart()
    chart.title = "Task Distribution by Team Member"
    chart.x_axis.title = "Team Members"
    chart.y_axis.title = "Number of Tasks"

    data = Reference(sheet, min_col=2, min_row=3, max_col=5, max_row=total_row - 1)
    cats = Reference(sheet, min_col=1, min_row=4, max_row=total_row - 1)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    sheet.add_chart(chart, "A" + str(total_row + 2))


def setup_member_sheet(sheet, member_name, member_prefix):
    sheet.sheet_properties.tabColor = "92D050"

    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                         top=Side(style='thin'), bottom=Side(style='thin'))

    title_fill = PatternFill(start_color="70AD47", end_color="70AD47", fill_type="solid")
    header_fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid")
    todo_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
    progress_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
    blocked_fill = PatternFill(start_color="FFD9D9", end_color="FFD9D9", fill_type="solid")
    done_fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid")
    mandatory_fill = PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid")

    sheet['A1'] = f"{member_name}'s Task Board"
    sheet['A1'].font = Font(bold=True, size=14, color="FFFFFF")
    sheet['A1'].fill = title_fill
    sheet.merge_cells('A1:G1')
    sheet['A1'].alignment = Alignment(horizontal='center')

    sheet['G2'] = "* Required fields"
    sheet['G2'].font = Font(italic=True)
    sheet['G2'].alignment = Alignment(horizontal='right')

    headers = ["Task ID", "Task Description*", "Status*", "Priority", "Due Date*", "Created Date", "Notes"]
    for col, header in enumerate(headers, 1):
        cell = sheet.cell(row=3, column=col, value=header)
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center')

        if col == 2 or col == 3 or col == 5:
            cell.fill = mandatory_fill

        if col == 2:
            sheet.column_dimensions[get_column_letter(col)].width = 40
        elif col == 7:
            sheet.column_dimensions[get_column_letter(col)].width = 25
        else:
            sheet.column_dimensions[get_column_letter(col)].width = 15

    status_validation = DataValidation(
        type="list",
        formula1='"To Do,In Progress,Blocked,Done"',
        allow_blank=False
    )

    priority_validation = DataValidation(
        type="list",
        formula1='"Critical,High,Normal,Low"',
        allow_blank=True
    )

    sheet.add_data_validation(status_validation)
    sheet.add_data_validation(priority_validation)

    for row in range(4, 54):
        sheet.cell(row=row, column=1, value=f"=IF(B{row}<>\"\",\"{member_prefix}-\"&ROW()-3,\"\")")

        created_date_cell = sheet.cell(row=row, column=6, value=f"=IF(B{row}<>\"\",TODAY(),\"\")")
        created_date_cell.number_format = "yyyy-mm-dd"

        due_date_cell = sheet.cell(row=row, column=5)
        due_date_cell.number_format = "yyyy-mm-dd"

        for col in range(1, 8):
            sheet.cell(row=row, column=col).border = Side(style='thin')
            if col == 2 or col == 7:
                sheet.cell(row=row, column=col).alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
            else:
                sheet.cell(row=row, column=col).alignment = Alignment(horizontal='center')

        status_validation.add(f'C{row}')
        priority_validation.add(f'D{row}')

        conditional = f'INDIRECT("C"&ROW())'
        sheet.conditional_formatting.add(f'C{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'{conditional}="To Do"'],
                                             stopIfTrue=True,
                                             fill=todo_fill))
        sheet.conditional_formatting.add(f'C{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'{conditional}="In Progress"'],
                                             stopIfTrue=True,
                                             fill=progress_fill))
        sheet.conditional_formatting.add(f'C{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'{conditional}="Blocked"'],
                                             stopIfTrue=True,
                                             fill=blocked_fill))
        sheet.conditional_formatting.add(f'C{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'{conditional}="Done"'],
                                             stopIfTrue=True,
                                             fill=done_fill))

        sheet.conditional_formatting.add(f'B{row}:B{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'AND(C{row}<>"",B{row}="")'],
                                             stopIfTrue=True,
                                             fill=mandatory_fill))

        sheet.conditional_formatting.add(f'C{row}:C{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'AND(B{row}<>"",C{row}="")'],
                                             stopIfTrue=True,
                                             fill=mandatory_fill))

        sheet.conditional_formatting.add(f'E{row}:E{row}',
                                         openpyxl.formatting.rule.FormulaRule(
                                             formula=[f'AND(B{row}<>"",E{row}="")'],
                                             stopIfTrue=True,
                                             fill=mandatory_fill))


def setup_weekly_dashboard(sheet, team_members, team_name, week_start_day):
    sheet.sheet_properties.tabColor = "4F81BD"

    title_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
    header_fill = PatternFill(start_color="DCE6F1", end_color="DCE6F1", fill_type="solid")

    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                         top=Side(style='thin'), bottom=Side(style='thin'))

    sheet['A1'] = f"{team_name.upper()} - WEEKLY ACTIVITY"
    sheet['A1'].font = Font(bold=True, size=16, color="FFFFFF")
    sheet['A1'].fill = title_fill
    sheet.merge_cells('A1:H1')
    sheet['A1'].alignment = Alignment(horizontal='center')

    today = datetime.now()
    days_since_start = (today.weekday() - week_start_day) % 7
    start_of_week = today - timedelta(days=days_since_start)

    weekday_names = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
    weekdays = []
    for i in range(7):
        weekday_idx = (week_start_day + i) % 7
        weekdays.append(weekday_names[weekday_idx])

    sheet['A2'] = f"Week of {start_of_week.strftime('%Y-%m-%d')}"
    sheet['A2'].font = Font(bold=True)
    sheet.merge_cells('A2:H2')
    sheet['A2'].alignment = Alignment(horizontal='center')

    sheet.cell(row=3, column=1, value="Team Member").font = Font(bold=True)
    sheet.cell(row=3, column=1).fill = header_fill
    sheet.cell(row=3, column=1).border = thin_border

    for col, day in enumerate(weekdays, 2):
        date = start_of_week + timedelta(days=col - 2)
        header = f"{day}\n{date.strftime('%m/%d')}"
        cell = sheet.cell(row=3, column=col, value=header)
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
        sheet.column_dimensions[get_column_letter(col)].width = 12

    sheet.column_dimensions['A'].width = 20

    sheet.cell(row=3, column=9, value="Total").font = Font(bold=True)
    sheet.cell(row=3, column=9).fill = header_fill
    sheet.cell(row=3, column=9).border = thin_border
    sheet.column_dimensions['I'].width = 12

    for row, member in enumerate(team_members, 4):
        sheet.cell(row=row, column=1, value=member).alignment = Alignment(horizontal='left')
        sheet.cell(row=row, column=1).border = thin_border

        for col in range(2, 10):
            if col < 9:
                date = start_of_week + timedelta(days=col - 2)
                date_str = date.strftime("%Y-%m-%d")
                formula = f"=COUNTIFS('{member}'!F:F,\"{date_str}\",'{member}'!C:C,\"Done\")"
                sheet.cell(row=row, column=col, value=formula)
            else:
                sheet.cell(row=row, column=col, value=f"=SUM(B{row}:H{row})")

            sheet.cell(row=row, column=col).border = thin_border
            sheet.cell(row=row, column=col).alignment = Alignment(horizontal='center')

    total_row = len(team_members) + 4
    sheet.cell(row=total_row, column=1, value="DAILY TOTALS").font = Font(bold=True)
    sheet.cell(row=total_row, column=1).border = thin_border

    for col in range(2, 10):
        col_letter = get_column_letter(col)
        cell = sheet.cell(row=total_row, column=col, value=f"=SUM({col_letter}4:{col_letter}{total_row - 1})")
        cell.font = Font(bold=True)
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center')

    chart_row = total_row + 2
    sheet.cell(row=chart_row, column=1, value="Weekly Completion Trend").font = Font(bold=True, size=12)
    sheet.merge_cells(f'A{chart_row}:I{chart_row}')
    sheet.cell(row=chart_row, column=1).alignment = Alignment(horizontal='center')

    chart = LineChart()
    chart.title = "Tasks Completed by Day"
    chart.x_axis.title = "Day of Week"
    chart.y_axis.title = "Tasks Completed"

    data = Reference(sheet, min_col=2, min_row=3, max_col=8, max_row=total_row)
    cats = Reference(sheet, min_col=2, min_row=3, max_col=8, max_row=3)

    chart.add_data(data, titles_from_data=False, from_rows=True)
    chart.set_categories(cats)

    sheet.add_chart(chart, f"A{chart_row + 2}")


def setup_monthly_dashboard(sheet, team_members, team_name):
    sheet.sheet_properties.tabColor = "9BBB59"

    title_fill = PatternFill(start_color="9BBB59", end_color="9BBB59", fill_type="solid")
    header_fill = PatternFill(start_color="EBF1DE", end_color="EBF1DE", fill_type="solid")

    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                         top=Side(style='thin'), bottom=Side(style='thin'))

    sheet['A1'] = f"{team_name.upper()} - MONTHLY ACTIVITY"
    sheet['A1'].font = Font(bold=True, size=16, color="FFFFFF")
    sheet['A1'].fill = title_fill
    sheet.merge_cells('A1:F1')
    sheet['A1'].alignment = Alignment(horizontal='center')

    today = datetime.now()
    current_month = today.strftime("%B %Y")

    sheet['A2'] = f"Month: {current_month}"
    sheet['A2'].font = Font(bold=True)
    sheet.merge_cells('A2:F2')
    sheet['A2'].alignment = Alignment(horizontal='center')

    headers = ["Team Member", "Tasks Created", "Tasks Completed", "In Progress", "Success Rate",
               "Average Completion Time"]
    for col, header in enumerate(headers, 1):
        cell = sheet.cell(row=4, column=col, value=header)
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
        sheet.column_dimensions[get_column_letter(col)].width = 18

    sheet.column_dimensions['A'].width = 20

    for row, member in enumerate(team_members, 5):
        sheet.cell(row=row, column=1, value=member).alignment = Alignment(horizontal='left')
        sheet.cell(row=row, column=1).border = thin_border

        first_day = today.replace(day=1).strftime("%Y-%m-%d")
        last_day = (today.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)
        last_day = last_day.strftime("%Y-%m-%d")

        sheet.cell(row=row, column=2,
                   value=f"=COUNTIFS('{member}'!F:F,\">={first_day}\",'{member}'!F:F,\"<={last_day}\")")
        sheet.cell(row=row, column=3,
                   value=f"=COUNTIFS('{member}'!C:C,\"Done\",'{member}'!F:F,\">={first_day}\",'{member}'!F:F,\"<={last_day}\")")
        sheet.cell(row=row, column=4,
                   value=f"=COUNTIFS('{member}'!C:C,\"In Progress\",'{member}'!F:F,\">={first_day}\",'{member}'!F:F,\"<={last_day}\")")
        sheet.cell(row=row, column=5, value=f"=IF(B{row}>0,C{row}/B{row},0)")
        sheet.cell(row=row, column=6, value="N/A")

        for col in range(1, 7):
            sheet.cell(row=row, column=col).border = thin_border
            if col == 5:
                sheet.cell(row=row, column=col).number_format = "0.0%"
            sheet.cell(row=row, column=col).alignment = Alignment(horizontal='center')

    total_row = len(team_members) + 5
    sheet.cell(row=total_row, column=1, value="TOTALS").font = Font(bold=True)
    sheet.cell(row=total_row, column=1).border = thin_border

    for col in range(2, 5):
        col_letter = get_column_letter(col)
        cell = sheet.cell(row=total_row, column=col, value=f"=SUM({col_letter}5:{col_letter}{total_row - 1})")
        cell.font = Font(bold=True)
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center')

    sheet.cell(row=total_row, column=5, value=f"=IF(B{total_row}>0,C{total_row}/B{total_row},0)")
    sheet.cell(row=total_row, column=5).number_format = "0.0%"
    sheet.cell(row=total_row, column=5).font = Font(bold=True)
    sheet.cell(row=total_row, column=5).border = thin_border
    sheet.cell(row=total_row, column=5).alignment = Alignment(horizontal='center')

    sheet.cell(row=total_row, column=6, value="N/A")
    sheet.cell(row=total_row, column=6).font = Font(bold=True)
    sheet.cell(row=total_row, column=6).border = thin_border
    sheet.cell(row=total_row, column=6).alignment = Alignment(horizontal='center')

    chart_row = total_row + 2
    sheet.cell(row=chart_row, column=1, value="Monthly Task Summary").font = Font(bold=True, size=12)
    sheet.merge_cells(f'A{chart_row}:F{chart_row}')
    sheet.cell(row=chart_row, column=1).alignment = Alignment(horizontal='center')

    chart = BarChart()
    chart.title = f"Tasks by Team Member - {current_month}"
    chart.x_axis.title = "Team Members"
    chart.y_axis.title = "Number of Tasks"

    data = Reference(sheet, min_col=2, min_row=4, max_col=4, max_row=total_row - 1)
    cats = Reference(sheet, min_col=1, min_row=5, max_row=total_row - 1)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    sheet.add_chart(chart, f"A{chart_row + 2}")


if __name__ == "__main__":
    create_team_tracking_sheet()

TeamTrack
A Python script that generates a comprehensive Excel/Google Sheets compatible task tracking system with automated dashboards.

This tool creates a professional task tracking spreadsheet with individual team member boards and consolidated dashboards. It features:

• Kanban-style task tracking (To Do, In Progress, Blocked, Done)
• Color-coded status visualization
• Automatic task ID generation using member name patterns
• Weekly and monthly activity dashboards with charts
• Priority tracking with Critical task highlighting
• Mandatory field indicators
• Customizable week start day

Usage

• Run the script
• Enter team name when prompted
• Enter team member names (one per line, type 'done' when finished)
• Select week start day (1-7)
• Import the generated Excel file into Google Sheets
• Add tasks to individual member sheets - dashboards update automatically