Ah, timesheets. The Lex Luthor of every software engineer’s existence. If you’re like me, you’d rather debug a race condition at 3 a.m. than spend even five minutes documenting what you did all day. But alas, as freelancers or full-time employees, we’re often stuck with this tedious chore.
This year, I hit my breaking point. After a whirlwind of projects—some canceled, some pivoted, and others shelved indefinitely—I found myself staring down the barrel of a year-end timesheet deadline. The thought of manually reconstructing my entire year’s work made me want to scream. So, I did what any self-respecting engineer would do: I decided to automate the problem away.
This is the story of how I turned a day of dread into a coding adventure. Buckle up—it’s going to be a lazy, efficient ride.
The Problem: Timesheets Are the Worst
Let’s set the scene:
-
The Task: Document every hour spent on every task for the entire year.
-
The Obstacle: My memory is about as reliable as a flaky integration test.
-
The Deadline: One day. Yes, one day.
I knew I couldn’t do this manually. So, I hatched a plan to pull data from all the tools I use daily—JIRA, Git, Slack, and Outlook—and stitch it together into a coherent timesheet.
The Tools of the Trade
Here’s what I used:
-
JIRA: For tracking tasks and tickets.
-
Git: For commit history (because every good engineer ties commits to tickets, right?).
-
Slack: For team communication (because meetings and messages count too).
-
Outlook: For calendar events (because apparently, meetings are work too).
Step 1: Pulling JIRA Tickets
My first stop was JIRA. I needed to pull all the tickets assigned to me within a specific date range. Thankfully, JIRA has a robust API, and with a little Python magic, I was able to automate this process.
The Script
Here’s the Python script I wrote to fetch JIRA tickets:
<span>import</span> <span>os</span><span>from</span> <span>jira</span> <span>import</span> <span>JIRA</span><span>import</span> <span>pandas</span> <span>as</span> <span>pd</span><span>from</span> <span>datetime</span> <span>import</span> <span>datetime</span><span>import</span> <span>logging</span><span>import</span> <span>sys</span><span>from</span> <span>typing</span> <span>import</span> <span>List</span><span>,</span> <span>Dict</span><span>,</span> <span>Any</span><span>import</span> <span>argparse</span><span># Configure logging </span><span>logging</span><span>.</span><span>basicConfig</span><span>(</span><span>level</span><span>=</span><span>logging</span><span>.</span><span>INFO</span><span>,</span><span>format</span><span>=</span><span>'</span><span>%(asctime)s - %(levelname)s - %(message)s</span><span>'</span><span>)</span><span>logger</span> <span>=</span> <span>logging</span><span>.</span><span>getLogger</span><span>(</span><span>__name__</span><span>)</span><span>class</span> <span>JiraTicketPuller</span><span>:</span><span>def</span> <span>__init__</span><span>(</span><span>self</span><span>,</span> <span>server</span><span>:</span> <span>str</span><span>,</span> <span>email</span><span>:</span> <span>str</span><span>,</span> <span>api_token</span><span>:</span> <span>str</span><span>):</span><span>"""</span><span> Initialize JIRA client with authentication credentials. Args: server: JIRA server URL email: User</span><span>'</span><span>s email for authentication api_token: JIRA API token </span><span>"""</span><span>try</span><span>:</span><span>self</span><span>.</span><span>jira</span> <span>=</span> <span>JIRA</span><span>(</span><span>server</span><span>=</span><span>server</span><span>,</span> <span>basic_auth</span><span>=</span><span>(</span><span>email</span><span>,</span> <span>api_token</span><span>))</span><span>logger</span><span>.</span><span>info</span><span>(</span><span>"</span><span>Successfully connected to JIRA</span><span>"</span><span>)</span><span>except</span> <span>Exception</span> <span>as</span> <span>e</span><span>:</span><span>logger</span><span>.</span><span>error</span><span>(</span><span>f</span><span>"</span><span>Failed to connect to JIRA: </span><span>{</span><span>str</span><span>(</span><span>e</span><span>)</span><span>}</span><span>"</span><span>)</span><span>sys</span><span>.</span><span>exit</span><span>(</span><span>1</span><span>)</span><span>def</span> <span>get_user_tickets</span><span>(</span><span>self</span><span>,</span><span>username</span><span>:</span> <span>str</span><span>,</span><span>start_date</span><span>:</span> <span>str</span><span>,</span><span>end_date</span><span>:</span> <span>str</span><span>)</span> <span>-></span> <span>List</span><span>[</span><span>Dict</span><span>[</span><span>str</span><span>,</span> <span>Any</span><span>]]:</span><span>"""</span><span> Retrieve all tickets assigned to a user within a date range. Args: username: JIRA username to search for start_date: Start date in YYYY-MM-DD format end_date: End date in YYYY-MM-DD format Returns: List of dictionaries containing ticket information </span><span>"""</span><span>try</span><span>:</span><span># Construct JQL query </span> <span>jql_query</span> <span>=</span> <span>(</span><span>f</span><span>'</span><span>assignee = </span><span>{</span><span>username</span><span>}</span><span> AND </span><span>'</span><span>f</span><span>'</span><span>created >= </span><span>{</span><span>start_date</span><span>}</span><span> AND </span><span>'</span><span>f</span><span>'</span><span>created <= </span><span>{</span><span>end_date</span><span>}</span><span> </span><span>'</span><span>'</span><span>ORDER BY created DESC</span><span>'</span><span>)</span><span># Fields to retrieve </span> <span>fields</span> <span>=</span> <span>[</span><span>'</span><span>summary</span><span>'</span><span>,</span><span>'</span><span>status</span><span>'</span><span>,</span><span>'</span><span>priority</span><span>'</span><span>,</span><span>'</span><span>issuetype</span><span>'</span><span>,</span><span>'</span><span>created</span><span>'</span><span>,</span><span>'</span><span>updated</span><span>'</span><span>,</span><span>'</span><span>project</span><span>'</span><span>,</span><span>'</span><span>components</span><span>'</span><span>,</span><span>'</span><span>description</span><span>'</span><span>]</span><span># Get issues using JQL </span> <span>issues</span> <span>=</span> <span>self</span><span>.</span><span>jira</span><span>.</span><span>search_issues</span><span>(</span><span>jql_query</span><span>,</span><span>maxResults</span><span>=</span><span>1000</span><span>,</span> <span># Adjust based on your needs </span> <span>fields</span><span>=</span><span>fields</span><span>)</span><span>tickets_data</span> <span>=</span> <span>[]</span><span>for</span> <span>issue</span> <span>in</span> <span>issues</span><span>:</span><span>ticket</span> <span>=</span> <span>{</span><span>'</span><span>Key</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>key</span><span>,</span><span>'</span><span>Summary</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>summary</span><span>,</span><span>'</span><span>Status</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>status</span><span>),</span><span>'</span><span>Priority</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>priority</span><span>),</span><span>'</span><span>Issue Type</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>issuetype</span><span>),</span><span>'</span><span>Created</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>created</span><span>[:</span><span>10</span><span>],</span> <span># Get date only </span> <span>'</span><span>Updated</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>updated</span><span>[:</span><span>10</span><span>],</span> <span># Get date only </span> <span>'</span><span>Project</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>project</span><span>),</span><span>'</span><span>Components</span><span>'</span><span>:</span> <span>'</span><span>, </span><span>'</span><span>.</span><span>join</span><span>([</span><span>str</span><span>(</span><span>c</span><span>)</span> <span>for</span> <span>c</span> <span>in</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>components</span><span>]),</span><span>'</span><span>Description</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>description</span> <span>or</span> <span>''</span><span>}</span><span>tickets_data</span><span>.</span><span>append</span><span>(</span><span>ticket</span><span>)</span><span>logger</span><span>.</span><span>info</span><span>(</span><span>f</span><span>"</span><span>Retrieved </span><span>{</span><span>len</span><span>(</span><span>tickets_data</span><span>)</span><span>}</span><span> tickets for user </span><span>{</span><span>username</span><span>}</span><span>"</span><span>)</span><span>return</span> <span>tickets_data</span><span>except</span> <span>Exception</span> <span>as</span> <span>e</span><span>:</span><span>logger</span><span>.</span><span>error</span><span>(</span><span>f</span><span>"</span><span>Error retrieving tickets: </span><span>{</span><span>str</span><span>(</span><span>e</span><span>)</span><span>}</span><span>"</span><span>)</span><span>return</span> <span>[]</span><span>def</span> <span>export_to_csv</span><span>(</span><span>self</span><span>,</span> <span>tickets</span><span>:</span> <span>List</span><span>[</span><span>Dict</span><span>[</span><span>str</span><span>,</span> <span>Any</span><span>]],</span> <span>output_file</span><span>:</span> <span>str</span><span>):</span><span>"""</span><span> Export tickets data to CSV file. Args: tickets: List of ticket dictionaries output_file: Path to output CSV file </span><span>"""</span><span>try</span><span>:</span><span>if</span> <span>not</span> <span>tickets</span><span>:</span><span>logger</span><span>.</span><span>warning</span><span>(</span><span>"</span><span>No tickets to export</span><span>"</span><span>)</span><span>return</span><span>df</span> <span>=</span> <span>pd</span><span>.</span><span>DataFrame</span><span>(</span><span>tickets</span><span>)</span><span>df</span><span>.</span><span>to_csv</span><span>(</span><span>output_file</span><span>,</span> <span>index</span><span>=</span><span>False</span><span>,</span> <span>encoding</span><span>=</span><span>'</span><span>utf-8</span><span>'</span><span>)</span><span>logger</span><span>.</span><span>info</span><span>(</span><span>f</span><span>"</span><span>Successfully exported tickets to </span><span>{</span><span>output_file</span><span>}</span><span>"</span><span>)</span><span>except</span> <span>Exception</span> <span>as</span> <span>e</span><span>:</span><span>logger</span><span>.</span><span>error</span><span>(</span><span>f</span><span>"</span><span>Error exporting to CSV: </span><span>{</span><span>str</span><span>(</span><span>e</span><span>)</span><span>}</span><span>"</span><span>)</span><span>def</span> <span>validate_date</span><span>(</span><span>date_str</span><span>:</span> <span>str</span><span>)</span> <span>-></span> <span>bool</span><span>:</span><span>"""</span><span>Validate date string format (YYYY-MM-DD).</span><span>"""</span><span>try</span><span>:</span><span>datetime</span><span>.</span><span>strptime</span><span>(</span><span>date_str</span><span>,</span> <span>'</span><span>%Y-%m-%d</span><span>'</span><span>)</span><span>return</span> <span>True</span><span>except</span> <span>ValueError</span><span>:</span><span>return</span> <span>False</span><span>def</span> <span>main</span><span>():</span><span># Set up argument parser </span> <span>parser</span> <span>=</span> <span>argparse</span><span>.</span><span>ArgumentParser</span><span>(</span><span>description</span><span>=</span><span>'</span><span>Pull JIRA tickets for a user within a date range</span><span>'</span><span>)</span><span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--username</span><span>'</span><span>,</span> <span>required</span><span>=</span><span>True</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>JIRA username</span><span>'</span><span>)</span><span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--start-date</span><span>'</span><span>,</span> <span>required</span><span>=</span><span>True</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>Start date (YYYY-MM-DD)</span><span>'</span><span>)</span><span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--end-date</span><span>'</span><span>,</span> <span>required</span><span>=</span><span>True</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>End date (YYYY-MM-DD)</span><span>'</span><span>)</span><span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--output</span><span>'</span><span>,</span><span>default</span><span>=</span><span>'</span><span>jira_tickets.csv</span><span>'</span><span>,</span><span>help</span><span>=</span><span>'</span><span>Output CSV file path</span><span>'</span><span>)</span><span>args</span> <span>=</span> <span>parser</span><span>.</span><span>parse_args</span><span>()</span><span># Validate dates </span> <span>if</span> <span>not</span> <span>all</span><span>(</span><span>validate_date</span><span>(</span><span>date</span><span>)</span> <span>for</span> <span>date</span> <span>in</span> <span>[</span><span>args</span><span>.</span><span>start_date</span><span>,</span> <span>args</span><span>.</span><span>end_date</span><span>]):</span><span>logger</span><span>.</span><span>error</span><span>(</span><span>"</span><span>Invalid date format. Please use YYYY-MM-DD</span><span>"</span><span>)</span><span>sys</span><span>.</span><span>exit</span><span>(</span><span>1</span><span>)</span><span># Configuration </span> <span>JIRA_SERVER</span> <span>=</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_SERVER</span><span>"</span><span>)</span><span>JIRA_EMAIL</span> <span>=</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_EMAIL</span><span>"</span><span>)</span><span>JIRA_API_TOKEN</span> <span>=</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_API_TOKEN</span><span>"</span><span>)</span><span># Initialize ticket puller </span> <span>puller</span> <span>=</span> <span>JiraTicketPuller</span><span>(</span><span>JIRA_SERVER</span><span>,</span> <span>JIRA_EMAIL</span><span>,</span> <span>JIRA_API_TOKEN</span><span>)</span><span># Get and export tickets </span> <span>tickets</span> <span>=</span> <span>puller</span><span>.</span><span>get_user_tickets</span><span>(</span><span>args</span><span>.</span><span>username</span><span>,</span><span>args</span><span>.</span><span>start_date</span><span>,</span><span>args</span><span>.</span><span>end_date</span><span>)</span><span>puller</span><span>.</span><span>export_to_csv</span><span>(</span><span>tickets</span><span>,</span> <span>args</span><span>.</span><span>output</span><span>)</span><span>if</span> <span>__name__</span> <span>==</span> <span>"</span><span>__main__</span><span>"</span><span>:</span><span># Ensure JIRA API token is set </span> <span>if</span> <span>not</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_API_TOKEN</span><span>"</span><span>):</span><span>logging</span><span>.</span><span>error</span><span>(</span><span>"</span><span>JIRA_API_TOKEN environment variable not set</span><span>"</span><span>)</span><span>return</span><span>main</span><span>()</span><span>import</span> <span>os</span> <span>from</span> <span>jira</span> <span>import</span> <span>JIRA</span> <span>import</span> <span>pandas</span> <span>as</span> <span>pd</span> <span>from</span> <span>datetime</span> <span>import</span> <span>datetime</span> <span>import</span> <span>logging</span> <span>import</span> <span>sys</span> <span>from</span> <span>typing</span> <span>import</span> <span>List</span><span>,</span> <span>Dict</span><span>,</span> <span>Any</span> <span>import</span> <span>argparse</span> <span># Configure logging </span><span>logging</span><span>.</span><span>basicConfig</span><span>(</span> <span>level</span><span>=</span><span>logging</span><span>.</span><span>INFO</span><span>,</span> <span>format</span><span>=</span><span>'</span><span>%(asctime)s - %(levelname)s - %(message)s</span><span>'</span> <span>)</span> <span>logger</span> <span>=</span> <span>logging</span><span>.</span><span>getLogger</span><span>(</span><span>__name__</span><span>)</span> <span>class</span> <span>JiraTicketPuller</span><span>:</span> <span>def</span> <span>__init__</span><span>(</span><span>self</span><span>,</span> <span>server</span><span>:</span> <span>str</span><span>,</span> <span>email</span><span>:</span> <span>str</span><span>,</span> <span>api_token</span><span>:</span> <span>str</span><span>):</span> <span>"""</span><span> Initialize JIRA client with authentication credentials. Args: server: JIRA server URL email: User</span><span>'</span><span>s email for authentication api_token: JIRA API token </span><span>"""</span> <span>try</span><span>:</span> <span>self</span><span>.</span><span>jira</span> <span>=</span> <span>JIRA</span><span>(</span><span>server</span><span>=</span><span>server</span><span>,</span> <span>basic_auth</span><span>=</span><span>(</span><span>email</span><span>,</span> <span>api_token</span><span>))</span> <span>logger</span><span>.</span><span>info</span><span>(</span><span>"</span><span>Successfully connected to JIRA</span><span>"</span><span>)</span> <span>except</span> <span>Exception</span> <span>as</span> <span>e</span><span>:</span> <span>logger</span><span>.</span><span>error</span><span>(</span><span>f</span><span>"</span><span>Failed to connect to JIRA: </span><span>{</span><span>str</span><span>(</span><span>e</span><span>)</span><span>}</span><span>"</span><span>)</span> <span>sys</span><span>.</span><span>exit</span><span>(</span><span>1</span><span>)</span> <span>def</span> <span>get_user_tickets</span><span>(</span> <span>self</span><span>,</span> <span>username</span><span>:</span> <span>str</span><span>,</span> <span>start_date</span><span>:</span> <span>str</span><span>,</span> <span>end_date</span><span>:</span> <span>str</span> <span>)</span> <span>-></span> <span>List</span><span>[</span><span>Dict</span><span>[</span><span>str</span><span>,</span> <span>Any</span><span>]]:</span> <span>"""</span><span> Retrieve all tickets assigned to a user within a date range. Args: username: JIRA username to search for start_date: Start date in YYYY-MM-DD format end_date: End date in YYYY-MM-DD format Returns: List of dictionaries containing ticket information </span><span>"""</span> <span>try</span><span>:</span> <span># Construct JQL query </span> <span>jql_query</span> <span>=</span> <span>(</span> <span>f</span><span>'</span><span>assignee = </span><span>{</span><span>username</span><span>}</span><span> AND </span><span>'</span> <span>f</span><span>'</span><span>created >= </span><span>{</span><span>start_date</span><span>}</span><span> AND </span><span>'</span> <span>f</span><span>'</span><span>created <= </span><span>{</span><span>end_date</span><span>}</span><span> </span><span>'</span> <span>'</span><span>ORDER BY created DESC</span><span>'</span> <span>)</span> <span># Fields to retrieve </span> <span>fields</span> <span>=</span> <span>[</span> <span>'</span><span>summary</span><span>'</span><span>,</span> <span>'</span><span>status</span><span>'</span><span>,</span> <span>'</span><span>priority</span><span>'</span><span>,</span> <span>'</span><span>issuetype</span><span>'</span><span>,</span> <span>'</span><span>created</span><span>'</span><span>,</span> <span>'</span><span>updated</span><span>'</span><span>,</span> <span>'</span><span>project</span><span>'</span><span>,</span> <span>'</span><span>components</span><span>'</span><span>,</span> <span>'</span><span>description</span><span>'</span> <span>]</span> <span># Get issues using JQL </span> <span>issues</span> <span>=</span> <span>self</span><span>.</span><span>jira</span><span>.</span><span>search_issues</span><span>(</span> <span>jql_query</span><span>,</span> <span>maxResults</span><span>=</span><span>1000</span><span>,</span> <span># Adjust based on your needs </span> <span>fields</span><span>=</span><span>fields</span> <span>)</span> <span>tickets_data</span> <span>=</span> <span>[]</span> <span>for</span> <span>issue</span> <span>in</span> <span>issues</span><span>:</span> <span>ticket</span> <span>=</span> <span>{</span> <span>'</span><span>Key</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>key</span><span>,</span> <span>'</span><span>Summary</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>summary</span><span>,</span> <span>'</span><span>Status</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>status</span><span>),</span> <span>'</span><span>Priority</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>priority</span><span>),</span> <span>'</span><span>Issue Type</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>issuetype</span><span>),</span> <span>'</span><span>Created</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>created</span><span>[:</span><span>10</span><span>],</span> <span># Get date only </span> <span>'</span><span>Updated</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>updated</span><span>[:</span><span>10</span><span>],</span> <span># Get date only </span> <span>'</span><span>Project</span><span>'</span><span>:</span> <span>str</span><span>(</span><span>issue</span><span>.</span><span>fields</span><span>.</span><span>project</span><span>),</span> <span>'</span><span>Components</span><span>'</span><span>:</span> <span>'</span><span>, </span><span>'</span><span>.</span><span>join</span><span>([</span><span>str</span><span>(</span><span>c</span><span>)</span> <span>for</span> <span>c</span> <span>in</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>components</span><span>]),</span> <span>'</span><span>Description</span><span>'</span><span>:</span> <span>issue</span><span>.</span><span>fields</span><span>.</span><span>description</span> <span>or</span> <span>''</span> <span>}</span> <span>tickets_data</span><span>.</span><span>append</span><span>(</span><span>ticket</span><span>)</span> <span>logger</span><span>.</span><span>info</span><span>(</span><span>f</span><span>"</span><span>Retrieved </span><span>{</span><span>len</span><span>(</span><span>tickets_data</span><span>)</span><span>}</span><span> tickets for user </span><span>{</span><span>username</span><span>}</span><span>"</span><span>)</span> <span>return</span> <span>tickets_data</span> <span>except</span> <span>Exception</span> <span>as</span> <span>e</span><span>:</span> <span>logger</span><span>.</span><span>error</span><span>(</span><span>f</span><span>"</span><span>Error retrieving tickets: </span><span>{</span><span>str</span><span>(</span><span>e</span><span>)</span><span>}</span><span>"</span><span>)</span> <span>return</span> <span>[]</span> <span>def</span> <span>export_to_csv</span><span>(</span><span>self</span><span>,</span> <span>tickets</span><span>:</span> <span>List</span><span>[</span><span>Dict</span><span>[</span><span>str</span><span>,</span> <span>Any</span><span>]],</span> <span>output_file</span><span>:</span> <span>str</span><span>):</span> <span>"""</span><span> Export tickets data to CSV file. Args: tickets: List of ticket dictionaries output_file: Path to output CSV file </span><span>"""</span> <span>try</span><span>:</span> <span>if</span> <span>not</span> <span>tickets</span><span>:</span> <span>logger</span><span>.</span><span>warning</span><span>(</span><span>"</span><span>No tickets to export</span><span>"</span><span>)</span> <span>return</span> <span>df</span> <span>=</span> <span>pd</span><span>.</span><span>DataFrame</span><span>(</span><span>tickets</span><span>)</span> <span>df</span><span>.</span><span>to_csv</span><span>(</span><span>output_file</span><span>,</span> <span>index</span><span>=</span><span>False</span><span>,</span> <span>encoding</span><span>=</span><span>'</span><span>utf-8</span><span>'</span><span>)</span> <span>logger</span><span>.</span><span>info</span><span>(</span><span>f</span><span>"</span><span>Successfully exported tickets to </span><span>{</span><span>output_file</span><span>}</span><span>"</span><span>)</span> <span>except</span> <span>Exception</span> <span>as</span> <span>e</span><span>:</span> <span>logger</span><span>.</span><span>error</span><span>(</span><span>f</span><span>"</span><span>Error exporting to CSV: </span><span>{</span><span>str</span><span>(</span><span>e</span><span>)</span><span>}</span><span>"</span><span>)</span> <span>def</span> <span>validate_date</span><span>(</span><span>date_str</span><span>:</span> <span>str</span><span>)</span> <span>-></span> <span>bool</span><span>:</span> <span>"""</span><span>Validate date string format (YYYY-MM-DD).</span><span>"""</span> <span>try</span><span>:</span> <span>datetime</span><span>.</span><span>strptime</span><span>(</span><span>date_str</span><span>,</span> <span>'</span><span>%Y-%m-%d</span><span>'</span><span>)</span> <span>return</span> <span>True</span> <span>except</span> <span>ValueError</span><span>:</span> <span>return</span> <span>False</span> <span>def</span> <span>main</span><span>():</span> <span># Set up argument parser </span> <span>parser</span> <span>=</span> <span>argparse</span><span>.</span><span>ArgumentParser</span><span>(</span> <span>description</span><span>=</span><span>'</span><span>Pull JIRA tickets for a user within a date range</span><span>'</span> <span>)</span> <span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--username</span><span>'</span><span>,</span> <span>required</span><span>=</span><span>True</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>JIRA username</span><span>'</span><span>)</span> <span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--start-date</span><span>'</span><span>,</span> <span>required</span><span>=</span><span>True</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>Start date (YYYY-MM-DD)</span><span>'</span><span>)</span> <span>parser</span><span>.</span><span>add_argument</span><span>(</span><span>'</span><span>--end-date</span><span>'</span><span>,</span> <span>required</span><span>=</span><span>True</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>End date (YYYY-MM-DD)</span><span>'</span><span>)</span> <span>parser</span><span>.</span><span>add_argument</span><span>(</span> <span>'</span><span>--output</span><span>'</span><span>,</span> <span>default</span><span>=</span><span>'</span><span>jira_tickets.csv</span><span>'</span><span>,</span> <span>help</span><span>=</span><span>'</span><span>Output CSV file path</span><span>'</span> <span>)</span> <span>args</span> <span>=</span> <span>parser</span><span>.</span><span>parse_args</span><span>()</span> <span># Validate dates </span> <span>if</span> <span>not</span> <span>all</span><span>(</span><span>validate_date</span><span>(</span><span>date</span><span>)</span> <span>for</span> <span>date</span> <span>in</span> <span>[</span><span>args</span><span>.</span><span>start_date</span><span>,</span> <span>args</span><span>.</span><span>end_date</span><span>]):</span> <span>logger</span><span>.</span><span>error</span><span>(</span><span>"</span><span>Invalid date format. Please use YYYY-MM-DD</span><span>"</span><span>)</span> <span>sys</span><span>.</span><span>exit</span><span>(</span><span>1</span><span>)</span> <span># Configuration </span> <span>JIRA_SERVER</span> <span>=</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_SERVER</span><span>"</span><span>)</span> <span>JIRA_EMAIL</span> <span>=</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_EMAIL</span><span>"</span><span>)</span> <span>JIRA_API_TOKEN</span> <span>=</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_API_TOKEN</span><span>"</span><span>)</span> <span># Initialize ticket puller </span> <span>puller</span> <span>=</span> <span>JiraTicketPuller</span><span>(</span><span>JIRA_SERVER</span><span>,</span> <span>JIRA_EMAIL</span><span>,</span> <span>JIRA_API_TOKEN</span><span>)</span> <span># Get and export tickets </span> <span>tickets</span> <span>=</span> <span>puller</span><span>.</span><span>get_user_tickets</span><span>(</span> <span>args</span><span>.</span><span>username</span><span>,</span> <span>args</span><span>.</span><span>start_date</span><span>,</span> <span>args</span><span>.</span><span>end_date</span> <span>)</span> <span>puller</span><span>.</span><span>export_to_csv</span><span>(</span><span>tickets</span><span>,</span> <span>args</span><span>.</span><span>output</span><span>)</span> <span>if</span> <span>__name__</span> <span>==</span> <span>"</span><span>__main__</span><span>"</span><span>:</span> <span># Ensure JIRA API token is set </span> <span>if</span> <span>not</span> <span>os</span><span>.</span><span>getenv</span><span>(</span><span>"</span><span>JIRA_API_TOKEN</span><span>"</span><span>):</span> <span>logging</span><span>.</span><span>error</span><span>(</span><span>"</span><span>JIRA_API_TOKEN environment variable not set</span><span>"</span><span>)</span> <span>return</span> <span>main</span><span>()</span>import os from jira import JIRA import pandas as pd from datetime import datetime import logging import sys from typing import List, Dict, Any import argparse # Configure logging logging.basicConfig( level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s' ) logger = logging.getLogger(__name__) class JiraTicketPuller: def __init__(self, server: str, email: str, api_token: str): """ Initialize JIRA client with authentication credentials. Args: server: JIRA server URL email: User's email for authentication api_token: JIRA API token """ try: self.jira = JIRA(server=server, basic_auth=(email, api_token)) logger.info("Successfully connected to JIRA") except Exception as e: logger.error(f"Failed to connect to JIRA: {str(e)}") sys.exit(1) def get_user_tickets( self, username: str, start_date: str, end_date: str ) -> List[Dict[str, Any]]: """ Retrieve all tickets assigned to a user within a date range. Args: username: JIRA username to search for start_date: Start date in YYYY-MM-DD format end_date: End date in YYYY-MM-DD format Returns: List of dictionaries containing ticket information """ try: # Construct JQL query jql_query = ( f'assignee = {username} AND ' f'created >= {start_date} AND ' f'created <= {end_date} ' 'ORDER BY created DESC' ) # Fields to retrieve fields = [ 'summary', 'status', 'priority', 'issuetype', 'created', 'updated', 'project', 'components', 'description' ] # Get issues using JQL issues = self.jira.search_issues( jql_query, maxResults=1000, # Adjust based on your needs fields=fields ) tickets_data = [] for issue in issues: ticket = { 'Key': issue.key, 'Summary': issue.fields.summary, 'Status': str(issue.fields.status), 'Priority': str(issue.fields.priority), 'Issue Type': str(issue.fields.issuetype), 'Created': issue.fields.created[:10], # Get date only 'Updated': issue.fields.updated[:10], # Get date only 'Project': str(issue.fields.project), 'Components': ', '.join([str(c) for c in issue.fields.components]), 'Description': issue.fields.description or '' } tickets_data.append(ticket) logger.info(f"Retrieved {len(tickets_data)} tickets for user {username}") return tickets_data except Exception as e: logger.error(f"Error retrieving tickets: {str(e)}") return [] def export_to_csv(self, tickets: List[Dict[str, Any]], output_file: str): """ Export tickets data to CSV file. Args: tickets: List of ticket dictionaries output_file: Path to output CSV file """ try: if not tickets: logger.warning("No tickets to export") return df = pd.DataFrame(tickets) df.to_csv(output_file, index=False, encoding='utf-8') logger.info(f"Successfully exported tickets to {output_file}") except Exception as e: logger.error(f"Error exporting to CSV: {str(e)}") def validate_date(date_str: str) -> bool: """Validate date string format (YYYY-MM-DD).""" try: datetime.strptime(date_str, '%Y-%m-%d') return True except ValueError: return False def main(): # Set up argument parser parser = argparse.ArgumentParser( description='Pull JIRA tickets for a user within a date range' ) parser.add_argument('--username', required=True, help='JIRA username') parser.add_argument('--start-date', required=True, help='Start date (YYYY-MM-DD)') parser.add_argument('--end-date', required=True, help='End date (YYYY-MM-DD)') parser.add_argument( '--output', default='jira_tickets.csv', help='Output CSV file path' ) args = parser.parse_args() # Validate dates if not all(validate_date(date) for date in [args.start_date, args.end_date]): logger.error("Invalid date format. Please use YYYY-MM-DD") sys.exit(1) # Configuration JIRA_SERVER = os.getenv("JIRA_SERVER") JIRA_EMAIL = os.getenv("JIRA_EMAIL") JIRA_API_TOKEN = os.getenv("JIRA_API_TOKEN") # Initialize ticket puller puller = JiraTicketPuller(JIRA_SERVER, JIRA_EMAIL, JIRA_API_TOKEN) # Get and export tickets tickets = puller.get_user_tickets( args.username, args.start_date, args.end_date ) puller.export_to_csv(tickets, args.output) if __name__ == "__main__": # Ensure JIRA API token is set if not os.getenv("JIRA_API_TOKEN"): logging.error("JIRA_API_TOKEN environment variable not set") return main()
Enter fullscreen mode Exit fullscreen mode
How It Works
-
Authentication: The script uses your JIRA email and API token to authenticate.
-
JQL Query: It constructs a JQL query to fetch tickets assigned to you within a date range.
-
Data Export: The results are exported to a CSV file for easy analysis.
Step 2: Fetching Git Commits
Next, I turned to Git. Since our team follows a practice of including JIRA ticket IDs in commit messages, I wrote a script to extract commit data and map it to tickets.
The Script
<span>import</span> <span>subprocess</span><span>import</span> <span>csv</span><span>import</span> <span>re</span><span>def</span> <span>get_git_commits</span><span>(</span><span>since_date</span><span>=</span><span>None</span><span>,</span> <span>author</span><span>=</span><span>None</span><span>):</span><span># Get commit data with full commit message </span> <span>cmd</span> <span>=</span> <span>[</span><span>'</span><span>git</span><span>'</span><span>,</span> <span>'</span><span>log</span><span>'</span><span>,</span> <span>'</span><span>--pretty=format:%h|%ad|%s</span><span>'</span><span>,</span> <span>'</span><span>--date=iso</span><span>'</span><span>]</span><span>if</span> <span>since_date</span><span>:</span><span>cmd</span><span>.</span><span>extend</span><span>([</span><span>'</span><span>--since</span><span>'</span><span>,</span> <span>since_date</span><span>])</span><span>if</span> <span>author</span><span>:</span><span>cmd</span><span>.</span><span>extend</span><span>([</span><span>'</span><span>--author</span><span>'</span><span>,</span> <span>author</span><span>])</span><span>result</span> <span>=</span> <span>subprocess</span><span>.</span><span>run</span><span>(</span><span>cmd</span><span>,</span> <span>capture_output</span><span>=</span><span>True</span><span>,</span> <span>text</span><span>=</span><span>True</span><span>)</span><span>commits</span> <span>=</span> <span>result</span><span>.</span><span>stdout</span><span>.</span><span>strip</span><span>().</span><span>split</span><span>(</span><span>'</span><span>\n</span><span>'</span><span>)</span><span># Process commits to extract JIRA IDs </span> <span>processed_commits</span> <span>=</span> <span>[]</span><span>jira_pattern</span> <span>=</span> <span>re</span><span>.</span><span>compile</span><span>(</span><span>r</span><span>'</span><span>JIRA:\s*([A-Z]+-\d+)</span><span>'</span><span>)</span><span>for</span> <span>commit</span> <span>in</span> <span>commits</span><span>:</span><span>if</span> <span>commit</span><span>:</span> <span># Skip empty lines </span> <span>hash_id</span><span>,</span> <span>date</span><span>,</span> <span>message</span> <span>=</span> <span>commit</span><span>.</span><span>split</span><span>(</span><span>'</span><span>|</span><span>'</span><span>,</span> <span>2</span><span>)</span><span>jira_match</span> <span>=</span> <span>jira_pattern</span><span>.</span><span>search</span><span>(</span><span>message</span><span>)</span><span>jira_id</span> <span>=</span> <span>jira_match</span><span>.</span><span>group</span><span>(</span><span>1</span><span>)</span> <span>if</span> <span>jira_match</span> <span>else</span> <span>''</span><span>processed_commits</span><span>.</span><span>append</span><span>({</span><span>'</span><span>date</span><span>'</span><span>:</span> <span>date</span><span>,</span><span>'</span><span>commit</span><span>'</span><span>:</span> <span>hash_id</span><span>,</span><span>'</span><span>jira_id</span><span>'</span><span>:</span> <span>jira_id</span><span>})</span><span># Write to CSV </span> <span>csv_filename</span> <span>=</span> <span>'</span><span>git_commits_jira.csv</span><span>'</span><span>fieldnames</span> <span>=</span> <span>[</span><span>'</span><span>date</span><span>'</span><span>,</span> <span>'</span><span>commit</span><span>'</span><span>,</span> <span>'</span><span>jira_id</span><span>'</span><span>]</span><span>with</span> <span>open</span><span>(</span><span>csv_filename</span><span>,</span> <span>'</span><span>w</span><span>'</span><span>,</span> <span>newline</span><span>=</span><span>''</span><span>)</span> <span>as</span> <span>csvfile</span><span>:</span><span>writer</span> <span>=</span> <span>csv</span><span>.</span><span>DictWriter</span><span>(</span><span>csvfile</span><span>,</span> <span>fieldnames</span><span>=</span><span>fieldnames</span><span>)</span><span>writer</span><span>.</span><span>writeheader</span><span>()</span><span>writer</span><span>.</span><span>writerows</span><span>(</span><span>processed_commits</span><span>)</span><span>return</span> <span>processed_commits</span><span>import</span> <span>subprocess</span> <span>import</span> <span>csv</span> <span>import</span> <span>re</span> <span>def</span> <span>get_git_commits</span><span>(</span><span>since_date</span><span>=</span><span>None</span><span>,</span> <span>author</span><span>=</span><span>None</span><span>):</span> <span># Get commit data with full commit message </span> <span>cmd</span> <span>=</span> <span>[</span><span>'</span><span>git</span><span>'</span><span>,</span> <span>'</span><span>log</span><span>'</span><span>,</span> <span>'</span><span>--pretty=format:%h|%ad|%s</span><span>'</span><span>,</span> <span>'</span><span>--date=iso</span><span>'</span><span>]</span> <span>if</span> <span>since_date</span><span>:</span> <span>cmd</span><span>.</span><span>extend</span><span>([</span><span>'</span><span>--since</span><span>'</span><span>,</span> <span>since_date</span><span>])</span> <span>if</span> <span>author</span><span>:</span> <span>cmd</span><span>.</span><span>extend</span><span>([</span><span>'</span><span>--author</span><span>'</span><span>,</span> <span>author</span><span>])</span> <span>result</span> <span>=</span> <span>subprocess</span><span>.</span><span>run</span><span>(</span><span>cmd</span><span>,</span> <span>capture_output</span><span>=</span><span>True</span><span>,</span> <span>text</span><span>=</span><span>True</span><span>)</span> <span>commits</span> <span>=</span> <span>result</span><span>.</span><span>stdout</span><span>.</span><span>strip</span><span>().</span><span>split</span><span>(</span><span>'</span><span>\n</span><span>'</span><span>)</span> <span># Process commits to extract JIRA IDs </span> <span>processed_commits</span> <span>=</span> <span>[]</span> <span>jira_pattern</span> <span>=</span> <span>re</span><span>.</span><span>compile</span><span>(</span><span>r</span><span>'</span><span>JIRA:\s*([A-Z]+-\d+)</span><span>'</span><span>)</span> <span>for</span> <span>commit</span> <span>in</span> <span>commits</span><span>:</span> <span>if</span> <span>commit</span><span>:</span> <span># Skip empty lines </span> <span>hash_id</span><span>,</span> <span>date</span><span>,</span> <span>message</span> <span>=</span> <span>commit</span><span>.</span><span>split</span><span>(</span><span>'</span><span>|</span><span>'</span><span>,</span> <span>2</span><span>)</span> <span>jira_match</span> <span>=</span> <span>jira_pattern</span><span>.</span><span>search</span><span>(</span><span>message</span><span>)</span> <span>jira_id</span> <span>=</span> <span>jira_match</span><span>.</span><span>group</span><span>(</span><span>1</span><span>)</span> <span>if</span> <span>jira_match</span> <span>else</span> <span>''</span> <span>processed_commits</span><span>.</span><span>append</span><span>({</span> <span>'</span><span>date</span><span>'</span><span>:</span> <span>date</span><span>,</span> <span>'</span><span>commit</span><span>'</span><span>:</span> <span>hash_id</span><span>,</span> <span>'</span><span>jira_id</span><span>'</span><span>:</span> <span>jira_id</span> <span>})</span> <span># Write to CSV </span> <span>csv_filename</span> <span>=</span> <span>'</span><span>git_commits_jira.csv</span><span>'</span> <span>fieldnames</span> <span>=</span> <span>[</span><span>'</span><span>date</span><span>'</span><span>,</span> <span>'</span><span>commit</span><span>'</span><span>,</span> <span>'</span><span>jira_id</span><span>'</span><span>]</span> <span>with</span> <span>open</span><span>(</span><span>csv_filename</span><span>,</span> <span>'</span><span>w</span><span>'</span><span>,</span> <span>newline</span><span>=</span><span>''</span><span>)</span> <span>as</span> <span>csvfile</span><span>:</span> <span>writer</span> <span>=</span> <span>csv</span><span>.</span><span>DictWriter</span><span>(</span><span>csvfile</span><span>,</span> <span>fieldnames</span><span>=</span><span>fieldnames</span><span>)</span> <span>writer</span><span>.</span><span>writeheader</span><span>()</span> <span>writer</span><span>.</span><span>writerows</span><span>(</span><span>processed_commits</span><span>)</span> <span>return</span> <span>processed_commits</span>import subprocess import csv import re def get_git_commits(since_date=None, author=None): # Get commit data with full commit message cmd = ['git', 'log', '--pretty=format:%h|%ad|%s', '--date=iso'] if since_date: cmd.extend(['--since', since_date]) if author: cmd.extend(['--author', author]) result = subprocess.run(cmd, capture_output=True, text=True) commits = result.stdout.strip().split('\n') # Process commits to extract JIRA IDs processed_commits = [] jira_pattern = re.compile(r'JIRA:\s*([A-Z]+-\d+)') for commit in commits: if commit: # Skip empty lines hash_id, date, message = commit.split('|', 2) jira_match = jira_pattern.search(message) jira_id = jira_match.group(1) if jira_match else '' processed_commits.append({ 'date': date, 'commit': hash_id, 'jira_id': jira_id }) # Write to CSV csv_filename = 'git_commits_jira.csv' fieldnames = ['date', 'commit', 'jira_id'] with open(csv_filename, 'w', newline='') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=fieldnames) writer.writeheader() writer.writerows(processed_commits) return processed_commits
Enter fullscreen mode Exit fullscreen mode
How It Works
-
Git Log: The script uses
git logto fetch commit history. -
JIRA ID Extraction: It uses regex to extract JIRA ticket IDs from commit messages.
-
CSV Export: The results are saved to a CSV file for later use.
Step 3: Tackling Slack Messages
Slack was trickier. Messages are context-heavy, and mapping them to specific tasks isn’t straightforward. I briefly considered using AI to parse the data but decided against it due to cost and complexity. Instead, I created a generic ticket to capture communication time and wrote a script to fetch Slack messages.
The Script
<span>import</span> <span>os</span><span>from</span> <span>datetime</span> <span>import</span> <span>datetime</span><span>from</span> <span>slack_sdk</span> <span>import</span> <span>WebClient</span><span>from</span> <span>slack_sdk.errors</span> <span>import</span> <span>SlackApiError</span><span>import</span> <span>pandas</span> <span>as</span> <span>pd</span><span>def</span> <span>get_all_conversations</span><span>(</span><span>client</span><span>):</span><span>"""</span><span> Get all conversations (channels, private channels, and DMs) the bot has access to. Args: client: Slack WebClient instance Returns: list: List of conversation objects </span><span>"""</span><span>conversations</span> <span>=</span> <span>[]</span><span>try</span><span>:</span><span># Get all conversations (channels, private channels, and DMs) </span> <span>result</span> <span>=</span> <span>client</span><span>.</span><span>conversations_list</span><span>(</span><span>types</span><span>=</span><span>"</span><span>public_channel,private_channel,im,mpim</span><span>"</span><span>,</span><span>limit</span><span>=</span><span>1000</span><span>)</span><span>conversations</span><span>.</span><span>extend</span><span>(</span><span>result</span><span>[</span><span>"</span><span>channels</span><span>"</span><span>])</span><span># Handle pagination </span> <span>while</span> <span>result</span><span>.</span><span>get</span><span>(</span><span>"</span><span>response_metadata</span><span>"</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>"</span><span>next_cursor</span><span>"</span><span>):</span><span>cursor</span> <span>=</span> <span>result</span><span>[</span><span>"</span><span>response_metadata</span><span>"</span><span>][</span><span>"</span><span>next_cursor</span><span>"</span><span>]</span><span>result</span> <span>=</span> <span>client</span><span>.</span><span>conversations_list</span><span>(</span><span>types</span><span>=</span><span>"</span><span>public_channel,private_channel,im,mpim</span><span>"</span><span>,</span><span>cursor</span><span>=</span><span>cursor</span><span>,</span><span>limit</span><span>=</span><span>1000</span><span>)</span><span>conversations</span><span>.</span><span>extend</span><span>(</span><span>result</span><span>[</span><span>"</span><span>channels</span><span>"</span><span>])</span><span>except</span> <span>SlackApiError</span> <span>as</span> <span>e</span><span>:</span><span>print</span><span>(</span><span>f</span><span>"</span><span>Error fetching conversations: </span><span>{</span><span>e</span><span>.</span><span>response</span><span>[</span><span>'</span><span>error</span><span>'</span><span>]</span><span>}</span><span>"</span><span>)</span><span>return</span> <span>conversations</span><span>def</span> <span>get_user_info</span><span>(</span><span>client</span><span>,</span> <span>user_id</span><span>):</span><span>"""</span><span> Get user information from their ID. Args: client: Slack WebClient instance user_id: User</span><span>'</span><span>s Slack ID Returns: dict: User information including real name and email </span><span>"""</span><span>try</span><span>:</span><span>result</span> <span>=</span> <span>client</span><span>.</span><span>users_info</span><span>(</span><span>user</span><span>=</span><span>user_id</span><span>)</span><span>user</span> <span>=</span> <span>result</span><span>[</span><span>"</span><span>user</span><span>"</span><span>]</span><span>return</span> <span>{</span><span>"</span><span>real_name</span><span>"</span><span>:</span> <span>user</span><span>.</span><span>get</span><span>(</span><span>"</span><span>real_name</span><span>"</span><span>,</span> <span>"</span><span>Unknown</span><span>"</span><span>),</span><span>"</span><span>email</span><span>"</span><span>:</span> <span>user</span><span>.</span><span>get</span><span>(</span><span>"</span><span>profile</span><span>"</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>"</span><span>email</span><span>"</span><span>,</span> <span>"</span><span>Unknown</span><span>"</span><span>),</span><span>"</span><span>display_name</span><span>"</span><span>:</span> <span>user</span><span>.</span><span>get</span><span>(</span><span>"</span><span>profile</span><span>"</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>"</span><span>display_name</span><span>"</span><span>,</span> <span>"</span><span>Unknown</span><span>"</span><span>)</span><span>}</span><span>except</span> <span>SlackApiError</span><span>:</span><span>return</span> <span>{</span><span>"</span><span>real_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span> <span>"</span><span>email</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span> <span>"</span><span>display_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>}</span><span>def</span> <span>get_messages_from_conversation</span><span>(</span><span>client</span><span>,</span> <span>channel_id</span><span>,</span> <span>channel_name</span><span>,</span> <span>start_date</span><span>,</span> <span>end_date</span><span>):</span><span>"""</span><span> Retrieve messages from a specific conversation within a date range. Args: client: Slack WebClient instance channel_id (str): The ID of the Slack channel channel_name (str): The name of the Slack channel start_date (str): Start date in format </span><span>'</span><span>YYYY-MM-DD</span><span>'</span><span> end_date (str): End date in format </span><span>'</span><span>YYYY-MM-DD</span><span>'</span><span> Returns: list: List of messages within the date range </span><span>"""</span><span>messages</span> <span>=</span> <span>[]</span><span>start_timestamp</span> <span>=</span> <span>int</span><span>(</span><span>datetime</span><span>.</span><span>strptime</span><span>(</span><span>start_date</span><span>,</span> <span>'</span><span>%Y-%m-%d</span><span>'</span><span>).</span><span>timestamp</span><span>())</span><span>end_timestamp</span> <span>=</span> <span>int</span><span>(</span><span>datetime</span><span>.</span><span>strptime</span><span>(</span><span>end_date</span><span>,</span> <span>'</span><span>%Y-%m-%d</span><span>'</span><span>).</span><span>timestamp</span><span>())</span><span>try</span><span>:</span><span># Initialize the cursor for pagination </span> <span>cursor</span> <span>=</span> <span>None</span><span>while</span> <span>True</span><span>:</span><span># Get messages using conversations.history </span> <span>result</span> <span>=</span> <span>client</span><span>.</span><span>conversations_history</span><span>(</span><span>channel</span><span>=</span><span>channel_id</span><span>,</span><span>oldest</span><span>=</span><span>start_timestamp</span><span>,</span><span>latest</span><span>=</span><span>end_timestamp</span><span>,</span><span>limit</span><span>=</span><span>100</span><span>,</span><span>cursor</span><span>=</span><span>cursor</span><span>)</span><span># Process messages </span> <span>for</span> <span>msg</span> <span>in</span> <span>result</span><span>[</span><span>'</span><span>messages</span><span>'</span><span>]:</span><span>user_id</span> <span>=</span> <span>msg</span><span>.</span><span>get</span><span>(</span><span>'</span><span>user</span><span>'</span><span>)</span><span>user_info</span> <span>=</span> <span>get_user_info</span><span>(</span><span>client</span><span>,</span> <span>user_id</span><span>)</span> <span>if</span> <span>user_id</span> <span>else</span> <span>{</span><span>"</span><span>real_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span><span>"</span><span>email</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span><span>"</span><span>display_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>}</span><span>message_data</span> <span>=</span> <span>{</span><span>'</span><span>channel_name</span><span>'</span><span>:</span> <span>channel_name</span><span>,</span><span>'</span><span>channel_id</span><span>'</span><span>:</span> <span>channel_id</span><span>,</span><span>'</span><span>text</span><span>'</span><span>:</span> <span>msg</span><span>.</span><span>get</span><span>(</span><span>'</span><span>text</span><span>'</span><span>,</span> <span>''</span><span>),</span><span>'</span><span>user_id</span><span>'</span><span>:</span> <span>user_id</span><span>,</span><span>'</span><span>user_name</span><span>'</span><span>:</span> <span>user_info</span><span>[</span><span>'</span><span>real_name</span><span>'</span><span>],</span><span>'</span><span>user_email</span><span>'</span><span>:</span> <span>user_info</span><span>[</span><span>'</span><span>email</span><span>'</span><span>],</span><span>'</span><span>timestamp</span><span>'</span><span>:</span> <span>datetime</span><span>.</span><span>fromtimestamp</span><span>(</span><span>float</span><span>(</span><span>msg</span><span>[</span><span>'</span><span>ts</span><span>'</span><span>])).</span><span>strftime</span><span>(</span><span>'</span><span>%Y-%m-%d %H:%M:%S</span><span>'</span><span>),</span><span>'</span><span>thread_ts</span><span>'</span><span>:</span> <span>msg</span><span>.</span><span>get</span><span>(</span><span>'</span><span>thread_ts</span><span>'</span><span>,</span> <span>None</span><span>)</span><span>}</span><span>messages</span><span>.</span><span>append</span><span>(</span><span>message_data</span><span>)</span><span># Check if there are more messages to fetch </span> <span>cursor</span> <span>=</span> <span>result</span><span>.</span><span>get</span><span>(</span><span>'</span><span>response_metadata</span><span>'</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>'</span><span>next_cursor</span><span>'</span><span>)</span><span>if</span> <span>not</span> <span>cursor</span><span>:</span><span>break</span><span>except</span> <span>SlackApiError</span> <span>as</span> <span>e</span><span>:</span><span>print</span><span>(</span><span>f</span><span>"</span><span>Error fetching messages from </span><span>{</span><span>channel_name</span><span>}</span><span>: </span><span>{</span><span>e</span><span>.</span><span>response</span><span>[</span><span>'</span><span>error</span><span>'</span><span>]</span><span>}</span><span>"</span><span>)</span><span>return</span> <span>messages</span><span>def</span> <span>main</span><span>():</span><span># Set your date range </span> <span>start_date</span> <span>=</span> <span>"</span><span>2024-01-01</span><span>"</span><span>end_date</span> <span>=</span> <span>"</span><span>2024-01-31</span><span>"</span><span># Initialize the Slack client </span> <span>slack_token</span> <span>=</span> <span>os</span><span>.</span><span>environ</span><span>.</span><span>get</span><span>(</span><span>'</span><span>SLACK_BOT_TOKEN</span><span>'</span><span>)</span><span>if</span> <span>not</span> <span>slack_token</span><span>:</span><span>raise</span> <span>ValueError</span><span>(</span><span>"</span><span>Please set the SLACK_BOT_TOKEN environment variable</span><span>"</span><span>)</span><span>client</span> <span>=</span> <span>WebClient</span><span>(</span><span>token</span><span>=</span><span>slack_token</span><span>)</span><span># Get all conversations </span> <span>print</span><span>(</span><span>"</span><span>Fetching all conversations...</span><span>"</span><span>)</span><span>conversations</span> <span>=</span> <span>get_all_conversations</span><span>(</span><span>client</span><span>)</span><span># Fetch messages from all conversations </span> <span>all_messages</span> <span>=</span> <span>[]</span><span>total_conversations</span> <span>=</span> <span>len</span><span>(</span><span>conversations</span><span>)</span><span>print</span><span>(</span><span>f</span><span>"</span><span>Found </span><span>{</span><span>total_conversations</span><span>}</span><span> conversations. Fetching messages...</span><span>"</span><span>)</span><span>for</span> <span>idx</span><span>,</span> <span>conv</span> <span>in</span> <span>enumerate</span><span>(</span><span>conversations</span><span>,</span> <span>1</span><span>):</span><span>channel_id</span> <span>=</span> <span>conv</span><span>[</span><span>"</span><span>id</span><span>"</span><span>]</span><span>channel_name</span> <span>=</span> <span>conv</span><span>.</span><span>get</span><span>(</span><span>"</span><span>name</span><span>"</span><span>,</span> <span>"</span><span>DM</span><span>"</span><span>)</span> <span># DMs don't have names </span><span>print</span><span>(</span><span>f</span><span>"</span><span>Processing </span><span>{</span><span>idx</span><span>}</span><span>/</span><span>{</span><span>total_conversations</span><span>}</span><span>: </span><span>{</span><span>channel_name</span><span>}</span><span>"</span><span>)</span><span>messages</span> <span>=</span> <span>get_messages_from_conversation</span><span>(</span><span>client</span><span>,</span><span>channel_id</span><span>,</span><span>channel_name</span><span>,</span><span>start_date</span><span>,</span><span>end_date</span><span>)</span><span>all_messages</span><span>.</span><span>extend</span><span>(</span><span>messages</span><span>)</span><span># Convert to DataFrame for easier handling </span> <span>df</span> <span>=</span> <span>pd</span><span>.</span><span>DataFrame</span><span>(</span><span>all_messages</span><span>)</span><span># Save to CSV </span> <span>output_file</span> <span>=</span> <span>f</span><span>"</span><span>slack_messages_</span><span>{</span><span>start_date</span><span>}</span><span>_to_</span><span>{</span><span>end_date</span><span>}</span><span>.csv</span><span>"</span><span>df</span><span>.</span><span>to_csv</span><span>(</span><span>output_file</span><span>,</span> <span>index</span><span>=</span><span>False</span><span>,</span> <span>encoding</span><span>=</span><span>'</span><span>utf-8</span><span>'</span><span>)</span><span>print</span><span>(</span><span>f</span><span>"</span><span>\n</span><span>Total messages retrieved: </span><span>{</span><span>len</span><span>(</span><span>all_messages</span><span>)</span><span>}</span><span>"</span><span>)</span><span>print</span><span>(</span><span>f</span><span>"</span><span>Messages saved to: </span><span>{</span><span>output_file</span><span>}</span><span>"</span><span>)</span><span># Print summary </span> <span>print</span><span>(</span><span>"</span><span>\n</span><span>Messages per channel:</span><span>"</span><span>)</span><span>channel_summary</span> <span>=</span> <span>df</span><span>.</span><span>groupby</span><span>(</span><span>'</span><span>channel_name</span><span>'</span><span>).</span><span>size</span><span>().</span><span>sort_values</span><span>(</span><span>ascending</span><span>=</span><span>False</span><span>)</span><span>print</span><span>(</span><span>channel_summary</span><span>)</span><span>if</span> <span>__name__</span> <span>==</span> <span>"</span><span>__main__</span><span>"</span><span>:</span><span>main</span><span>()</span><span>import</span> <span>os</span> <span>from</span> <span>datetime</span> <span>import</span> <span>datetime</span> <span>from</span> <span>slack_sdk</span> <span>import</span> <span>WebClient</span> <span>from</span> <span>slack_sdk.errors</span> <span>import</span> <span>SlackApiError</span> <span>import</span> <span>pandas</span> <span>as</span> <span>pd</span> <span>def</span> <span>get_all_conversations</span><span>(</span><span>client</span><span>):</span> <span>"""</span><span> Get all conversations (channels, private channels, and DMs) the bot has access to. Args: client: Slack WebClient instance Returns: list: List of conversation objects </span><span>"""</span> <span>conversations</span> <span>=</span> <span>[]</span> <span>try</span><span>:</span> <span># Get all conversations (channels, private channels, and DMs) </span> <span>result</span> <span>=</span> <span>client</span><span>.</span><span>conversations_list</span><span>(</span> <span>types</span><span>=</span><span>"</span><span>public_channel,private_channel,im,mpim</span><span>"</span><span>,</span> <span>limit</span><span>=</span><span>1000</span> <span>)</span> <span>conversations</span><span>.</span><span>extend</span><span>(</span><span>result</span><span>[</span><span>"</span><span>channels</span><span>"</span><span>])</span> <span># Handle pagination </span> <span>while</span> <span>result</span><span>.</span><span>get</span><span>(</span><span>"</span><span>response_metadata</span><span>"</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>"</span><span>next_cursor</span><span>"</span><span>):</span> <span>cursor</span> <span>=</span> <span>result</span><span>[</span><span>"</span><span>response_metadata</span><span>"</span><span>][</span><span>"</span><span>next_cursor</span><span>"</span><span>]</span> <span>result</span> <span>=</span> <span>client</span><span>.</span><span>conversations_list</span><span>(</span> <span>types</span><span>=</span><span>"</span><span>public_channel,private_channel,im,mpim</span><span>"</span><span>,</span> <span>cursor</span><span>=</span><span>cursor</span><span>,</span> <span>limit</span><span>=</span><span>1000</span> <span>)</span> <span>conversations</span><span>.</span><span>extend</span><span>(</span><span>result</span><span>[</span><span>"</span><span>channels</span><span>"</span><span>])</span> <span>except</span> <span>SlackApiError</span> <span>as</span> <span>e</span><span>:</span> <span>print</span><span>(</span><span>f</span><span>"</span><span>Error fetching conversations: </span><span>{</span><span>e</span><span>.</span><span>response</span><span>[</span><span>'</span><span>error</span><span>'</span><span>]</span><span>}</span><span>"</span><span>)</span> <span>return</span> <span>conversations</span> <span>def</span> <span>get_user_info</span><span>(</span><span>client</span><span>,</span> <span>user_id</span><span>):</span> <span>"""</span><span> Get user information from their ID. Args: client: Slack WebClient instance user_id: User</span><span>'</span><span>s Slack ID Returns: dict: User information including real name and email </span><span>"""</span> <span>try</span><span>:</span> <span>result</span> <span>=</span> <span>client</span><span>.</span><span>users_info</span><span>(</span><span>user</span><span>=</span><span>user_id</span><span>)</span> <span>user</span> <span>=</span> <span>result</span><span>[</span><span>"</span><span>user</span><span>"</span><span>]</span> <span>return</span> <span>{</span> <span>"</span><span>real_name</span><span>"</span><span>:</span> <span>user</span><span>.</span><span>get</span><span>(</span><span>"</span><span>real_name</span><span>"</span><span>,</span> <span>"</span><span>Unknown</span><span>"</span><span>),</span> <span>"</span><span>email</span><span>"</span><span>:</span> <span>user</span><span>.</span><span>get</span><span>(</span><span>"</span><span>profile</span><span>"</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>"</span><span>email</span><span>"</span><span>,</span> <span>"</span><span>Unknown</span><span>"</span><span>),</span> <span>"</span><span>display_name</span><span>"</span><span>:</span> <span>user</span><span>.</span><span>get</span><span>(</span><span>"</span><span>profile</span><span>"</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>"</span><span>display_name</span><span>"</span><span>,</span> <span>"</span><span>Unknown</span><span>"</span><span>)</span> <span>}</span> <span>except</span> <span>SlackApiError</span><span>:</span> <span>return</span> <span>{</span><span>"</span><span>real_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span> <span>"</span><span>email</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span> <span>"</span><span>display_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>}</span> <span>def</span> <span>get_messages_from_conversation</span><span>(</span><span>client</span><span>,</span> <span>channel_id</span><span>,</span> <span>channel_name</span><span>,</span> <span>start_date</span><span>,</span> <span>end_date</span><span>):</span> <span>"""</span><span> Retrieve messages from a specific conversation within a date range. Args: client: Slack WebClient instance channel_id (str): The ID of the Slack channel channel_name (str): The name of the Slack channel start_date (str): Start date in format </span><span>'</span><span>YYYY-MM-DD</span><span>'</span><span> end_date (str): End date in format </span><span>'</span><span>YYYY-MM-DD</span><span>'</span><span> Returns: list: List of messages within the date range </span><span>"""</span> <span>messages</span> <span>=</span> <span>[]</span> <span>start_timestamp</span> <span>=</span> <span>int</span><span>(</span><span>datetime</span><span>.</span><span>strptime</span><span>(</span><span>start_date</span><span>,</span> <span>'</span><span>%Y-%m-%d</span><span>'</span><span>).</span><span>timestamp</span><span>())</span> <span>end_timestamp</span> <span>=</span> <span>int</span><span>(</span><span>datetime</span><span>.</span><span>strptime</span><span>(</span><span>end_date</span><span>,</span> <span>'</span><span>%Y-%m-%d</span><span>'</span><span>).</span><span>timestamp</span><span>())</span> <span>try</span><span>:</span> <span># Initialize the cursor for pagination </span> <span>cursor</span> <span>=</span> <span>None</span> <span>while</span> <span>True</span><span>:</span> <span># Get messages using conversations.history </span> <span>result</span> <span>=</span> <span>client</span><span>.</span><span>conversations_history</span><span>(</span> <span>channel</span><span>=</span><span>channel_id</span><span>,</span> <span>oldest</span><span>=</span><span>start_timestamp</span><span>,</span> <span>latest</span><span>=</span><span>end_timestamp</span><span>,</span> <span>limit</span><span>=</span><span>100</span><span>,</span> <span>cursor</span><span>=</span><span>cursor</span> <span>)</span> <span># Process messages </span> <span>for</span> <span>msg</span> <span>in</span> <span>result</span><span>[</span><span>'</span><span>messages</span><span>'</span><span>]:</span> <span>user_id</span> <span>=</span> <span>msg</span><span>.</span><span>get</span><span>(</span><span>'</span><span>user</span><span>'</span><span>)</span> <span>user_info</span> <span>=</span> <span>get_user_info</span><span>(</span><span>client</span><span>,</span> <span>user_id</span><span>)</span> <span>if</span> <span>user_id</span> <span>else</span> <span>{</span> <span>"</span><span>real_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span> <span>"</span><span>email</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span><span>,</span> <span>"</span><span>display_name</span><span>"</span><span>:</span> <span>"</span><span>Unknown</span><span>"</span> <span>}</span> <span>message_data</span> <span>=</span> <span>{</span> <span>'</span><span>channel_name</span><span>'</span><span>:</span> <span>channel_name</span><span>,</span> <span>'</span><span>channel_id</span><span>'</span><span>:</span> <span>channel_id</span><span>,</span> <span>'</span><span>text</span><span>'</span><span>:</span> <span>msg</span><span>.</span><span>get</span><span>(</span><span>'</span><span>text</span><span>'</span><span>,</span> <span>''</span><span>),</span> <span>'</span><span>user_id</span><span>'</span><span>:</span> <span>user_id</span><span>,</span> <span>'</span><span>user_name</span><span>'</span><span>:</span> <span>user_info</span><span>[</span><span>'</span><span>real_name</span><span>'</span><span>],</span> <span>'</span><span>user_email</span><span>'</span><span>:</span> <span>user_info</span><span>[</span><span>'</span><span>email</span><span>'</span><span>],</span> <span>'</span><span>timestamp</span><span>'</span><span>:</span> <span>datetime</span><span>.</span><span>fromtimestamp</span><span>(</span><span>float</span><span>(</span><span>msg</span><span>[</span><span>'</span><span>ts</span><span>'</span><span>])).</span><span>strftime</span><span>(</span><span>'</span><span>%Y-%m-%d %H:%M:%S</span><span>'</span><span>),</span> <span>'</span><span>thread_ts</span><span>'</span><span>:</span> <span>msg</span><span>.</span><span>get</span><span>(</span><span>'</span><span>thread_ts</span><span>'</span><span>,</span> <span>None</span><span>)</span> <span>}</span> <span>messages</span><span>.</span><span>append</span><span>(</span><span>message_data</span><span>)</span> <span># Check if there are more messages to fetch </span> <span>cursor</span> <span>=</span> <span>result</span><span>.</span><span>get</span><span>(</span><span>'</span><span>response_metadata</span><span>'</span><span>,</span> <span>{}).</span><span>get</span><span>(</span><span>'</span><span>next_cursor</span><span>'</span><span>)</span> <span>if</span> <span>not</span> <span>cursor</span><span>:</span> <span>break</span> <span>except</span> <span>SlackApiError</span> <span>as</span> <span>e</span><span>:</span> <span>print</span><span>(</span><span>f</span><span>"</span><span>Error fetching messages from </span><span>{</span><span>channel_name</span><span>}</span><span>: </span><span>{</span><span>e</span><span>.</span><span>response</span><span>[</span><span>'</span><span>error</span><span>'</span><span>]</span><span>}</span><span>"</span><span>)</span> <span>return</span> <span>messages</span> <span>def</span> <span>main</span><span>():</span> <span># Set your date range </span> <span>start_date</span> <span>=</span> <span>"</span><span>2024-01-01</span><span>"</span> <span>end_date</span> <span>=</span> <span>"</span><span>2024-01-31</span><span>"</span> <span># Initialize the Slack client </span> <span>slack_token</span> <span>=</span> <span>os</span><span>.</span><span>environ</span><span>.</span><span>get</span><span>(</span><span>'</span><span>SLACK_BOT_TOKEN</span><span>'</span><span>)</span> <span>if</span> <span>not</span> <span>slack_token</span><span>:</span> <span>raise</span> <span>ValueError</span><span>(</span><span>"</span><span>Please set the SLACK_BOT_TOKEN environment variable</span><span>"</span><span>)</span> <span>client</span> <span>=</span> <span>WebClient</span><span>(</span><span>token</span><span>=</span><span>slack_token</span><span>)</span> <span># Get all conversations </span> <span>print</span><span>(</span><span>"</span><span>Fetching all conversations...</span><span>"</span><span>)</span> <span>conversations</span> <span>=</span> <span>get_all_conversations</span><span>(</span><span>client</span><span>)</span> <span># Fetch messages from all conversations </span> <span>all_messages</span> <span>=</span> <span>[]</span> <span>total_conversations</span> <span>=</span> <span>len</span><span>(</span><span>conversations</span><span>)</span> <span>print</span><span>(</span><span>f</span><span>"</span><span>Found </span><span>{</span><span>total_conversations</span><span>}</span><span> conversations. Fetching messages...</span><span>"</span><span>)</span> <span>for</span> <span>idx</span><span>,</span> <span>conv</span> <span>in</span> <span>enumerate</span><span>(</span><span>conversations</span><span>,</span> <span>1</span><span>):</span> <span>channel_id</span> <span>=</span> <span>conv</span><span>[</span><span>"</span><span>id</span><span>"</span><span>]</span> <span>channel_name</span> <span>=</span> <span>conv</span><span>.</span><span>get</span><span>(</span><span>"</span><span>name</span><span>"</span><span>,</span> <span>"</span><span>DM</span><span>"</span><span>)</span> <span># DMs don't have names </span> <span>print</span><span>(</span><span>f</span><span>"</span><span>Processing </span><span>{</span><span>idx</span><span>}</span><span>/</span><span>{</span><span>total_conversations</span><span>}</span><span>: </span><span>{</span><span>channel_name</span><span>}</span><span>"</span><span>)</span> <span>messages</span> <span>=</span> <span>get_messages_from_conversation</span><span>(</span> <span>client</span><span>,</span> <span>channel_id</span><span>,</span> <span>channel_name</span><span>,</span> <span>start_date</span><span>,</span> <span>end_date</span> <span>)</span> <span>all_messages</span><span>.</span><span>extend</span><span>(</span><span>messages</span><span>)</span> <span># Convert to DataFrame for easier handling </span> <span>df</span> <span>=</span> <span>pd</span><span>.</span><span>DataFrame</span><span>(</span><span>all_messages</span><span>)</span> <span># Save to CSV </span> <span>output_file</span> <span>=</span> <span>f</span><span>"</span><span>slack_messages_</span><span>{</span><span>start_date</span><span>}</span><span>_to_</span><span>{</span><span>end_date</span><span>}</span><span>.csv</span><span>"</span> <span>df</span><span>.</span><span>to_csv</span><span>(</span><span>output_file</span><span>,</span> <span>index</span><span>=</span><span>False</span><span>,</span> <span>encoding</span><span>=</span><span>'</span><span>utf-8</span><span>'</span><span>)</span> <span>print</span><span>(</span><span>f</span><span>"</span><span>\n</span><span>Total messages retrieved: </span><span>{</span><span>len</span><span>(</span><span>all_messages</span><span>)</span><span>}</span><span>"</span><span>)</span> <span>print</span><span>(</span><span>f</span><span>"</span><span>Messages saved to: </span><span>{</span><span>output_file</span><span>}</span><span>"</span><span>)</span> <span># Print summary </span> <span>print</span><span>(</span><span>"</span><span>\n</span><span>Messages per channel:</span><span>"</span><span>)</span> <span>channel_summary</span> <span>=</span> <span>df</span><span>.</span><span>groupby</span><span>(</span><span>'</span><span>channel_name</span><span>'</span><span>).</span><span>size</span><span>().</span><span>sort_values</span><span>(</span><span>ascending</span><span>=</span><span>False</span><span>)</span> <span>print</span><span>(</span><span>channel_summary</span><span>)</span> <span>if</span> <span>__name__</span> <span>==</span> <span>"</span><span>__main__</span><span>"</span><span>:</span> <span>main</span><span>()</span>import os from datetime import datetime from slack_sdk import WebClient from slack_sdk.errors import SlackApiError import pandas as pd def get_all_conversations(client): """ Get all conversations (channels, private channels, and DMs) the bot has access to. Args: client: Slack WebClient instance Returns: list: List of conversation objects """ conversations = [] try: # Get all conversations (channels, private channels, and DMs) result = client.conversations_list( types="public_channel,private_channel,im,mpim", limit=1000 ) conversations.extend(result["channels"]) # Handle pagination while result.get("response_metadata", {}).get("next_cursor"): cursor = result["response_metadata"]["next_cursor"] result = client.conversations_list( types="public_channel,private_channel,im,mpim", cursor=cursor, limit=1000 ) conversations.extend(result["channels"]) except SlackApiError as e: print(f"Error fetching conversations: {e.response['error']}") return conversations def get_user_info(client, user_id): """ Get user information from their ID. Args: client: Slack WebClient instance user_id: User's Slack ID Returns: dict: User information including real name and email """ try: result = client.users_info(user=user_id) user = result["user"] return { "real_name": user.get("real_name", "Unknown"), "email": user.get("profile", {}).get("email", "Unknown"), "display_name": user.get("profile", {}).get("display_name", "Unknown") } except SlackApiError: return {"real_name": "Unknown", "email": "Unknown", "display_name": "Unknown"} def get_messages_from_conversation(client, channel_id, channel_name, start_date, end_date): """ Retrieve messages from a specific conversation within a date range. Args: client: Slack WebClient instance channel_id (str): The ID of the Slack channel channel_name (str): The name of the Slack channel start_date (str): Start date in format 'YYYY-MM-DD' end_date (str): End date in format 'YYYY-MM-DD' Returns: list: List of messages within the date range """ messages = [] start_timestamp = int(datetime.strptime(start_date, '%Y-%m-%d').timestamp()) end_timestamp = int(datetime.strptime(end_date, '%Y-%m-%d').timestamp()) try: # Initialize the cursor for pagination cursor = None while True: # Get messages using conversations.history result = client.conversations_history( channel=channel_id, oldest=start_timestamp, latest=end_timestamp, limit=100, cursor=cursor ) # Process messages for msg in result['messages']: user_id = msg.get('user') user_info = get_user_info(client, user_id) if user_id else { "real_name": "Unknown", "email": "Unknown", "display_name": "Unknown" } message_data = { 'channel_name': channel_name, 'channel_id': channel_id, 'text': msg.get('text', ''), 'user_id': user_id, 'user_name': user_info['real_name'], 'user_email': user_info['email'], 'timestamp': datetime.fromtimestamp(float(msg['ts'])).strftime('%Y-%m-%d %H:%M:%S'), 'thread_ts': msg.get('thread_ts', None) } messages.append(message_data) # Check if there are more messages to fetch cursor = result.get('response_metadata', {}).get('next_cursor') if not cursor: break except SlackApiError as e: print(f"Error fetching messages from {channel_name}: {e.response['error']}") return messages def main(): # Set your date range start_date = "2024-01-01" end_date = "2024-01-31" # Initialize the Slack client slack_token = os.environ.get('SLACK_BOT_TOKEN') if not slack_token: raise ValueError("Please set the SLACK_BOT_TOKEN environment variable") client = WebClient(token=slack_token) # Get all conversations print("Fetching all conversations...") conversations = get_all_conversations(client) # Fetch messages from all conversations all_messages = [] total_conversations = len(conversations) print(f"Found {total_conversations} conversations. Fetching messages...") for idx, conv in enumerate(conversations, 1): channel_id = conv["id"] channel_name = conv.get("name", "DM") # DMs don't have names print(f"Processing {idx}/{total_conversations}: {channel_name}") messages = get_messages_from_conversation( client, channel_id, channel_name, start_date, end_date ) all_messages.extend(messages) # Convert to DataFrame for easier handling df = pd.DataFrame(all_messages) # Save to CSV output_file = f"slack_messages_{start_date}_to_{end_date}.csv" df.to_csv(output_file, index=False, encoding='utf-8') print(f"\nTotal messages retrieved: {len(all_messages)}") print(f"Messages saved to: {output_file}") # Print summary print("\nMessages per channel:") channel_summary = df.groupby('channel_name').size().sort_values(ascending=False) print(channel_summary) if __name__ == "__main__": main()
Enter fullscreen mode Exit fullscreen mode
How It Works
-
Conversation List: The script fetches all channels and DMs accessible to the bot.
-
Message Retrieval: It retrieves messages within a specified date range.
-
CSV Export: The messages are saved to a CSV file for further analysis.
Step 4: Capturing Outlook Meetings
Finally, I needed to account for meetings. Using the exchangelib Python library, I wrote a script to pull calendar events and export them to a CSV.
The Script
<span>from</span> <span>exchangelib</span> <span>import</span> <span>Credentials</span><span>,</span> <span>Account</span><span>import</span> <span>pandas</span> <span>as</span> <span>pd</span><span>from</span> <span>datetime</span> <span>import</span> <span>datetime</span><span>,</span> <span>timedelta</span><span>def</span> <span>get_outlook_meetings</span><span>():</span><span># Replace with your email and password </span> <span>email</span> <span>=</span> <span>os</span><span>.</span><span>environ</span><span>.</span><span>get</span><span>(</span><span>"</span><span>JIRA_EMAIL</span><span>"</span><span>)</span><span>password</span> <span>=</span> <span>os</span><span>.</span><span>environ</span><span>.</span><span>get</span><span>(</span><span>'</span><span>OUTLOOK_PASSWORD</span><span>'</span><span>)</span><span># Connect to Exchange </span> <span>credentials</span> <span>=</span> <span>Credentials</span><span>(</span><span>email</span><span>,</span> <span>password</span><span>)</span><span>account</span> <span>=</span> <span>Account</span><span>(</span><span>email</span><span>,</span> <span>credentials</span><span>=</span><span>credentials</span><span>,</span> <span>autodiscover</span><span>=</span><span>True</span><span>)</span><span># Get calendar items for the last 30 days (adjust as needed) </span> <span>start_date</span> <span>=</span> <span>datetime</span><span>.</span><span>now</span><span>()</span> <span>-</span> <span>timedelta</span><span>(</span><span>days</span><span>=</span><span>30</span><span>)</span><span>end_date</span> <span>=</span> <span>datetime</span><span>.</span><span>now</span><span>()</span> <span>+</span> <span>timedelta</span><span>(</span><span>days</span><span>=</span><span>30</span><span>)</span><span># Get calendar items </span> <span>calendar_items</span> <span>=</span> <span>account</span><span>.</span><span>calendar</span><span>.</span><span>view</span><span>(</span><span>start</span><span>=</span><span>start_date</span><span>,</span><span>end</span><span>=</span><span>end_date</span><span>)</span><span># Prepare data structure </span> <span>meetings</span> <span>=</span> <span>[]</span><span># Process each appointment </span> <span>for</span> <span>item</span> <span>in</span> <span>calendar_items</span><span>:</span><span># Calculate duration in minutes </span> <span>duration</span> <span>=</span> <span>int</span><span>((</span><span>item</span><span>.</span><span>end</span> <span>-</span> <span>item</span><span>.</span><span>start</span><span>).</span><span>total_seconds</span><span>()</span> <span>/</span> <span>60</span><span>)</span><span>meetings</span><span>.</span><span>append</span><span>({</span><span>'</span><span>date</span><span>'</span><span>:</span> <span>item</span><span>.</span><span>start</span><span>.</span><span>strftime</span><span>(</span><span>'</span><span>%Y-%m-%d</span><span>'</span><span>),</span><span>'</span><span>time</span><span>'</span><span>:</span> <span>item</span><span>.</span><span>start</span><span>.</span><span>strftime</span><span>(</span><span>'</span><span>%H:%M</span><span>'</span><span>),</span><span>'</span><span>duration</span><span>'</span><span>:</span> <span>duration</span><span>,</span><span>'</span><span>title</span><span>'</span><span>:</span> <span>item</span><span>.</span><span>subject</span><span>})</span><span># Create DataFrame and save to CSV </span> <span>df</span> <span>=</span> <span>pd</span><span>.</span><span>DataFrame</span><span>(</span><span>meetings</span><span>)</span><span>df</span><span>.</span><span>to_csv</span><span>(</span><span>'</span><span>outlook_meetings.csv</span><span>'</span><span>,</span> <span>index</span><span>=</span><span>False</span><span>)</span><span>return</span> <span>df</span><span># Run the function </span><span>if</span> <span>__name__</span> <span>==</span> <span>"</span><span>__main__</span><span>"</span><span>:</span><span>meetings_df</span> <span>=</span> <span>get_outlook_meetings</span><span>()</span><span>print</span><span>(</span><span>f</span><span>"</span><span>Exported </span><span>{</span><span>len</span><span>(</span><span>meetings_df</span><span>)</span><span>}</span><span> meetings to outlook_meetings.csv</span><span>"</span><span>)</span><span>from</span> <span>exchangelib</span> <span>import</span> <span>Credentials</span><span>,</span> <span>Account</span> <span>import</span> <span>pandas</span> <span>as</span> <span>pd</span> <span>from</span> <span>datetime</span> <span>import</span> <span>datetime</span><span>,</span> <span>timedelta</span> <span>def</span> <span>get_outlook_meetings</span><span>():</span> <span># Replace with your email and password </span> <span>email</span> <span>=</span> <span>os</span><span>.</span><span>environ</span><span>.</span><span>get</span><span>(</span><span>"</span><span>JIRA_EMAIL</span><span>"</span><span>)</span> <span>password</span> <span>=</span> <span>os</span><span>.</span><span>environ</span><span>.</span><span>get</span><span>(</span><span>'</span><span>OUTLOOK_PASSWORD</span><span>'</span><span>)</span> <span># Connect to Exchange </span> <span>credentials</span> <span>=</span> <span>Credentials</span><span>(</span><span>email</span><span>,</span> <span>password</span><span>)</span> <span>account</span> <span>=</span> <span>Account</span><span>(</span><span>email</span><span>,</span> <span>credentials</span><span>=</span><span>credentials</span><span>,</span> <span>autodiscover</span><span>=</span><span>True</span><span>)</span> <span># Get calendar items for the last 30 days (adjust as needed) </span> <span>start_date</span> <span>=</span> <span>datetime</span><span>.</span><span>now</span><span>()</span> <span>-</span> <span>timedelta</span><span>(</span><span>days</span><span>=</span><span>30</span><span>)</span> <span>end_date</span> <span>=</span> <span>datetime</span><span>.</span><span>now</span><span>()</span> <span>+</span> <span>timedelta</span><span>(</span><span>days</span><span>=</span><span>30</span><span>)</span> <span># Get calendar items </span> <span>calendar_items</span> <span>=</span> <span>account</span><span>.</span><span>calendar</span><span>.</span><span>view</span><span>(</span> <span>start</span><span>=</span><span>start_date</span><span>,</span> <span>end</span><span>=</span><span>end_date</span> <span>)</span> <span># Prepare data structure </span> <span>meetings</span> <span>=</span> <span>[]</span> <span># Process each appointment </span> <span>for</span> <span>item</span> <span>in</span> <span>calendar_items</span><span>:</span> <span># Calculate duration in minutes </span> <span>duration</span> <span>=</span> <span>int</span><span>((</span><span>item</span><span>.</span><span>end</span> <span>-</span> <span>item</span><span>.</span><span>start</span><span>).</span><span>total_seconds</span><span>()</span> <span>/</span> <span>60</span><span>)</span> <span>meetings</span><span>.</span><span>append</span><span>({</span> <span>'</span><span>date</span><span>'</span><span>:</span> <span>item</span><span>.</span><span>start</span><span>.</span><span>strftime</span><span>(</span><span>'</span><span>%Y-%m-%d</span><span>'</span><span>),</span> <span>'</span><span>time</span><span>'</span><span>:</span> <span>item</span><span>.</span><span>start</span><span>.</span><span>strftime</span><span>(</span><span>'</span><span>%H:%M</span><span>'</span><span>),</span> <span>'</span><span>duration</span><span>'</span><span>:</span> <span>duration</span><span>,</span> <span>'</span><span>title</span><span>'</span><span>:</span> <span>item</span><span>.</span><span>subject</span> <span>})</span> <span># Create DataFrame and save to CSV </span> <span>df</span> <span>=</span> <span>pd</span><span>.</span><span>DataFrame</span><span>(</span><span>meetings</span><span>)</span> <span>df</span><span>.</span><span>to_csv</span><span>(</span><span>'</span><span>outlook_meetings.csv</span><span>'</span><span>,</span> <span>index</span><span>=</span><span>False</span><span>)</span> <span>return</span> <span>df</span> <span># Run the function </span><span>if</span> <span>__name__</span> <span>==</span> <span>"</span><span>__main__</span><span>"</span><span>:</span> <span>meetings_df</span> <span>=</span> <span>get_outlook_meetings</span><span>()</span> <span>print</span><span>(</span><span>f</span><span>"</span><span>Exported </span><span>{</span><span>len</span><span>(</span><span>meetings_df</span><span>)</span><span>}</span><span> meetings to outlook_meetings.csv</span><span>"</span><span>)</span>from exchangelib import Credentials, Account import pandas as pd from datetime import datetime, timedelta def get_outlook_meetings(): # Replace with your email and password email = os.environ.get("JIRA_EMAIL") password = os.environ.get('OUTLOOK_PASSWORD') # Connect to Exchange credentials = Credentials(email, password) account = Account(email, credentials=credentials, autodiscover=True) # Get calendar items for the last 30 days (adjust as needed) start_date = datetime.now() - timedelta(days=30) end_date = datetime.now() + timedelta(days=30) # Get calendar items calendar_items = account.calendar.view( start=start_date, end=end_date ) # Prepare data structure meetings = [] # Process each appointment for item in calendar_items: # Calculate duration in minutes duration = int((item.end - item.start).total_seconds() / 60) meetings.append({ 'date': item.start.strftime('%Y-%m-%d'), 'time': item.start.strftime('%H:%M'), 'duration': duration, 'title': item.subject }) # Create DataFrame and save to CSV df = pd.DataFrame(meetings) df.to_csv('outlook_meetings.csv', index=False) return df # Run the function if __name__ == "__main__": meetings_df = get_outlook_meetings() print(f"Exported {len(meetings_df)} meetings to outlook_meetings.csv")
Enter fullscreen mode Exit fullscreen mode
How It Works
-
Authentication: The script uses your Outlook email and password to authenticate.
-
Calendar Query: It fetches calendar events within a specified date range.
-
CSV Export: The events are saved to a CSV file.
What’s Next?
At this point, I had four CSV files:
-
JIRA Tickets: All the tasks I worked on.
-
Git Commits: All the code I wrote.
-
Slack Messages: All the communication I participated in.
-
Outlook Meetings: All the meetings I attended.
In Part 2, I’ll show you how I stitched these datasets together to create a comprehensive timesheet. Spoiler alert: It involves more Python, some data wrangling, and a little bit of magic.
Stay tuned, and remember: Laziness is the mother of invention.
What’s your least favorite chore as a software engineer? Have you automated it yet? Share your stories in the comments!
原文链接：The Lazy Engineer’s Guide to Automating Timesheets: Part 1
© 版权声明
THE END
暂无评论内容