This notebook scans a user's Trello boards to create a summary of recent and upcoming work. It pulls out lists with specific names and places their boards in a summary document. I cut-and-paste the content of these documents into my weekly snippets and to and from requierments documents.
If you place an estimate of the time needed to complete a task in brackets preceeding the title of your cards, it will also calculate how much work you have in all lists named Doing, Writing, Dev, QC, UAT, or Rollout (more on these lists below). It compares this sum against the available hours in your work week. Consequently, the assumption is that you're running this once a week.
Additionally, it creates a time tracking document with the titles of cards from all of your items in the above listed lists. This document has timers for each task and works as a punch clock, noting when you are working and what you are working on. A timer is only provided for cards where you are a member. Other cards show up in a small orange font.
Note: your time estimates are not included in your summary document. Rather percentages are used to give a sense of priorities. I feel that the content of your time tracking document should be for personal use only. This is about giving you tools to improve your efficency, not enableing some Orwellian tracking system.
Set up a Trello borad for each of your projects, and create boards with one of the following list sets:
OR:
Define bite-sized tasks/features for your projects and add these as cards to the appropriate lists, prefacing each task with an estimate in hours of how long you think it will take (e.g., '[1] task A' would indicate that you estimate task A will take 1 hour).
Optional: Set up IFTTT recipes to add recurring tasks to your boards. For example, every week late Sunday night, IFTTT adds cards to my Queuing board for my weekly meetings. This way, when I move one week's cards, along with their notes to Done, I have a new set to move into Doing.
Once a week, review and edit your boards and edit them as needed. Then run the code found under Run Me to produce a summary and time-traking document.
If you've already set things up, skip to Run Me below.
You'll need to install the py-trello libary. So run the following on the command line:
pip install py-trello
Login to your Trello account and vist this page to get your app credentials (i.e., your key and your secret). When you run the cell below, you will be prompted to provide these. Now run the following code:
In [ ]:
from trello import TrelloClient
import os
try:
inputFunc = raw_input
except NameError:
inputFunc = input
os.environ['TRELLO_API_KEY'] = inputFunc('What is your TRELLO_API_KEY? ')
os.environ['TRELLO_API_SECRET'] = inputFunc('What is your TRELLO_API_SECRET? ')
credetials = open("keys.txt", "w")
credetials.write("%s,%s" %(os.environ['TRELLO_API_KEY'],os.environ['TRELLO_API_SECRET']))
credetials.close()
os.environ['TRELLO_EXPIRATION'] = 'never'
print ("\nYou keys have benn saved.\n")
# h/t @sarumont et al. See https://github.com/sarumont/py-trello/blob/master/trello/util.py
from __future__ import with_statement, print_function, absolute_import
from requests_oauthlib import OAuth1Session
def create_oauth_token(expiration=None, scope=None, key=None, secret=None, name=None, output=True):
"""
Script to obtain an OAuth token from Trello.
Must have TRELLO_API_KEY and TRELLO_API_SECRET set in your environment
To set the token's expiration, set TRELLO_EXPIRATION as a string in your
environment settings (eg. 'never'), otherwise it will default to 30 days.
More info on token scope here:
https://trello.com/docs/gettingstarted/#getting-a-token-from-a-user
"""
request_token_url = 'https://trello.com/1/OAuthGetRequestToken'
authorize_url = 'https://trello.com/1/OAuthAuthorizeToken'
access_token_url = 'https://trello.com/1/OAuthGetAccessToken'
expiration = expiration or os.environ.get('TRELLO_EXPIRATION', "30days")
scope = scope or os.environ.get('TRELLO_SCOPE', 'read,write')
trello_key = key or os.environ['TRELLO_API_KEY']
trello_secret = secret or os.environ['TRELLO_API_SECRET']
name = name or os.environ.get('TRELLO_NAME', 'py-trello')
# Step 1: Get a request token. This is a temporary token that is used for
# having the user authorize an access token and to sign the request to obtain
# said access token.
session = OAuth1Session(client_key=trello_key, client_secret=trello_secret)
response = session.fetch_request_token(request_token_url)
resource_owner_key, resource_owner_secret = response.get('oauth_token'), response.get('oauth_token_secret')
#if output:
# print("Request Token:")
# print(" - oauth_token = %s" % resource_owner_key)
# print(" - oauth_token_secret = %s" % resource_owner_secret)
# print("")
# Step 2: Redirect to the provider. Since this is a CLI script we do not
# redirect. In a web application you would redirect the user to the URL
# below.
print("After logging into Trello, go to the following link in your browser and click \"Allow\". ")
print("There you will be given a verification code.\n")
print("{authorize_url}?oauth_token={oauth_token}&scope={scope}&expiration={expiration}&name={name}".format(
authorize_url=authorize_url,
oauth_token=resource_owner_key,
expiration=expiration,
scope=scope,
name=name
))
# After the user has granted access to you, the consumer, the provider will
# redirect you to whatever URL you have told them to redirect to. You can
# usually define this in the oauth_callback argument as well.
# Python 3 compatibility (raw_input was renamed to input)
try:
inputFunc = raw_input
except NameError:
inputFunc = input
oauth_verifier = inputFunc('\nWhat is the verification code? ')
# Step 3: Once the consumer has redirected the user back to the oauth_callback
# URL you can request the access token the user has approved. You use the
# request token to sign this request. After this is done you throw away the
# request token and use the access token returned. You should store this
# access token somewhere safe, like a database, for future use.
session = OAuth1Session(client_key=trello_key, client_secret=trello_secret,
resource_owner_key=resource_owner_key, resource_owner_secret=resource_owner_secret,
verifier=oauth_verifier)
access_token = session.fetch_access_token(access_token_url)
if output:
#print("Access Token:")
#print(" - oauth_token = %s" % access_token['oauth_token'])
#print(" - oauth_token_secret = %s" % access_token['oauth_token_secret'])
#print("")
print("\nYou are all set up.")
credetials = open("credentials.txt", "w")
credetials.write("%s,%s" %(access_token['oauth_token'],access_token['oauth_token_secret']))
credetials.close()
return access_token
if __name__ == '__main__':
create_oauth_token()
# vim: tabstop=8 expandtab shiftwidth=4 softtabstop=4
In [ ]:
from trello import TrelloClient
import os, os.path
import re
import json
import numpy as np
import pandas as pd
from datetime import datetime, timezone
from IPython.core.display import display, HTML
# Load credentials
keys = open('keys.txt','r').read()
keys = keys.split(",")
credetials = open('credentials.txt','r').read()
credetials = credetials.split(",")
# create client
client = TrelloClient(
#api_key='your-key', api_secret='your-secret', token='your-oauth-token-key', token_secret='your-oauth-token-secret'
api_key=keys[0], api_secret=keys[1], token=credetials[0], token_secret=credetials[1]
)
if not os.path.exists('./summaries'):
os.makedirs('./summaries')
In [ ]:
gsheet = "https://docs.google.com/spreadsheets/d/1oy9i_T0TF-66RL-PnJ5eO6_aryydxUpwBklSgUfE3CQ/edit#gid=0" #url for your timesheet
costperswitch = 0.08
efficency = 0.80
nofBuiltIns = 1
priorities = pd.DataFrame(np.nan, index=[0], columns=['Percentage of Week','Project'])
f2vararray = ""
f2divlist = ""
days = 5 # days in which you plan to finish all active list items
workday = 7 # hours in a work day
#lastMeeting = datetime.strptime('2016-07-25 10:00', '%Y-%m-%d %H:%M')
#nextMeeting = datetime.strptime('2016-08-01 10:00', '%Y-%m-%d %H:%M')
#days = np.busday_count(lastMeeting, nextMeeting, holidays=['2016-07-04', '2016-12-25'])
esthoursinweek = days*workday*efficency
hoursinweek = esthoursinweek
hoursinbacklog = 0
j = 0
import glob
no_old = len(glob.glob(os.path.join("./summaries/", '*')))
this_sum = no_old + 1
f=open('./summaries/summary.%s.html'%this_sum, 'w+')
print ("<HTML><HEAD><STYLE>pre {\n\tfont-family: Calibri, Candara, Segoe, Optima, Arial, sans-serif;\n\tfont-size: 11pt;\n}</STYLE></HEAD>\n<BODY><pre>", file=f)
print ("Text <span style='background:#ddffdd;'>highlighted in green</span> represents additions, text <strike style='background:#ffdddd;'>highlighted in red</strike> represents deletions, and text <span style='background:#ffffdd;'>highlighted in yellow</span> represents changes.", file=f)
boards = client.list_boards()
#print (boards)
member = client.get_member('me')
urid = member.id
#print (member.id)
for b in boards:
#print (dir(b))
# Exclude Boards as you wish. For example, I have personal and work board, and never should the two meet.
if "Personal" not in b.name and False == b.closed:
lists = b.all_lists()
i = 0
hoursinlist = 0
hoursinbacklog = 0
for c in lists:
#print (dir(c))
if "Queuing" not in c.name:
cards = c.list_cards()
# If list contains "Doing", add board name to time tracking doc.
if len(cards) > 0 and "Doing" in c.name:
f2divlist = f2divlist+"""
<h2>"""+b.name+"""</h2>\n"""
# First time through? Add Board as heading to summary doc.
if i == 0:
print ("\n===============================", file=f)
print (" %s"%b.name, file=f)
print ("===============================", file=f)
# If there are cards, add them to summary doc.
i = i+1
if len(cards) > 0:
# Loop through cards
#
# set up variables and loop through only once
#
list = ""
for d in cards:
#if urid in d.member_ids or len(d.member_ids) == 0:
#if urid in d.member_ids:
list = c.name
if list:
print ("\n%s"%list, file=f)
for d in cards:
#print (dir(d))
#print (d.member_ids)
task = re.sub(r"(http(|s):[^\s]*)", r"<a href='\1'>\1</a>", d.name)
hours = re.match( r'^\[(\d+\.?\d*)\]', task)
if urid in d.member_ids and "Queuing" not in c.name and "Backlog" not in c.name and "On Deck" not in c.name and "Done" not in c.name:
#print ("%s - %s"%(urid,d.member_ids))
#task = re.sub(r"(http(|s):[^\s]*)", r"<a href='\1'>\1</a>", d.name)
#if "Doing" in c.name:
if hours:
if float(hours.group(1)) > 0:
hoursinweek = hoursinweek - float(hours.group(1)) - costperswitch
hoursinlist = hoursinlist + float(hours.group(1)) + costperswitch
#print (d.name)
#print ("hours: %s"%hours.group(1))
#print (hoursinweek)
if j == 0:
f2vararray = " var x = [new clsStopwatch()"
k=0
while k<nofBuiltIns:
f2vararray = f2vararray+",new clsStopwatch()"
k=k+1
j=nofBuiltIns
else:
f2vararray = f2vararray+",new clsStopwatch()"
f2divlist = f2divlist+" <div class=task id='h"+str(j)+"' style='display:none;'><font size=-1><a href='javascript:shoh(\"d"+str(j)+"\");shoh(\"h"+str(j)+"\")' style=\"color:gray; text-decoration:none;\">"+d.name+"</a></font></div>"
f2divlist = f2divlist+" <div class=task id='d"+str(j)+"'>\n <div class=time><a href='javascript:shoh(\"d"+str(j)+"\");shoh(\"h"+str(j)+"\")' style=\"color:black; text-decoration:none;\">"+d.name+"</a></div>"
f2divlist = f2divlist+"""
<div class=buttons>
(<span id='"""+str(j)+"""'></span>) <input type=hidden id='h"""+str(j)+"""'/>
<input type="button" value="start" onclick="start("""+str(j)+""");">
<input type="button" value="stop" onclick="stop("""+str(j)+""");">
<input type="button" value="reset" onclick="reset("""+str(j)+""")">
</div>
</div>\n"""
j = j + 1
elif "Doing" in c.name:
f2divlist = f2divlist+" <div class=task>\n <div class=time><font size=-1 style=\"color:orange; text-decoration:none;\">"+d.name+"</font></div></div>\n"
elif "Queuing" in c.name or "On Deck" in c.name or "Backlog" in c.name:
if hours:
if float(hours.group(1)) > 0:
hoursinbacklog = hoursinbacklog + float(hours.group(1)) + costperswitch
task = re.sub(r"(^(\[\d+\.?\d*\])|(\[\d+\.?\d*\]\s*)$)", r"", task)
if urid in d.member_ids:
sep = ""
else:
sep = "[WAITING] "
print ("+ %s%s"%(sep,task.strip()), file=f)
if d.desc:
print (" %s"%d.desc.strip(), file=f)
comments = d.get_comments()
for e in comments:
comment = re.sub(r"(http(|s):[^\s]*)", r"<a href='\1'>\1</a>", e['data']['text'])
comment = re.sub(r"\n\-\s{1}", "\n", comment)
comment = re.sub(r"^\-\s{1}", "\n", comment)
comment = re.sub(r"\n", "\n ---- ", comment)
comment = re.sub(r"\n ---- \n", "", comment)
print (" -- %s"%comment, file=f)
#print ("", file=f)
per_of_wk = (hoursinlist/esthoursinweek)*100
print ("\nYou are scheduled to spend %.2f%% of the work week on this. "%(per_of_wk), file=f)
priorities.loc[len(priorities)] = [per_of_wk,b.name]
if per_of_wk > 0 and hoursinbacklog > 0:
wks_at_current = hoursinbacklog/(esthoursinweek*(per_of_wk/100))
print ("At this rate, it would take %.2f weeks to work through your backlog. "%wks_at_current, file=f)
if hoursinbacklog > 0:
wks_at_100 = hoursinbacklog/(esthoursinweek)
wks_at_75 = hoursinbacklog/(esthoursinweek*0.75)
wks_at_50 = hoursinbacklog/(esthoursinweek*0.5)
wks_at_25 = hoursinbacklog/(esthoursinweek*0.25)
wks_at_10 = hoursinbacklog/(esthoursinweek*0.1)
print ("If you spent 100%% of your time, it would take %.2f weeks to work through your backlog. \nAlternatively, if you devote 75%% of your week, it would take %.2f weeks; 50%%, %.2f; 25%%, %.2f; and 10%%, %.2f respectively. "%(wks_at_100,wks_at_75,wks_at_50,wks_at_25,wks_at_10), file=f)
print ("</pre></BODY></HTML>", file=f)
f.close()
f2vararray = f2vararray+"];\n"
f2=open('./clock.html', 'w+')
print(open('./html/f2_1.html', 'r').read(), file=f2)
print (f2vararray, file=f2)
print (open('./html/f2_2.html', 'r').read(), file=f2)
print (f2divlist, file=f2)
print(" <hr> <input type=input id=oneday value=\""+str(workday)+"\" maxlength=\"4\"><input type=button onClick=\"workday=document.getElementById('oneday').value;timeondeck=totalhours_g;update();\" value=\"New Day\"> \n", file=f2)
print(" <a href=\"%s\">timesheet</a></div>\n</body>\n</html>"%gsheet, file=f2)
f2.close()
capacity = ((esthoursinweek-hoursinweek)/esthoursinweek)*100
print("\nHours available in the upcoming week minus hours estimated for your active tasks: %.2f"%hoursinweek)
print("To complete your scheduled tasks, you must work at %.2f%% capacity."%capacity)
priorities = priorities.sort_values(by=['Percentage of Week','Project'],ascending=[0,1])
priorities = priorities.dropna()
priorities['Percentage of Week'] = priorities['Percentage of Week'].round(2)
priorities[:]
In [ ]:
#h/t http://stackoverflow.com/questions/774316/python-difflib-highlighting-differences-inline
import difflib
def show_diff(seqm):
"""Unify operations between two compared strings seqm is a difflib.SequenceMatcher instance whose a & b are strings"""
output= []
for opcode, a0, a1, b0, b1 in seqm.get_opcodes():
if opcode == 'equal':
output.append(seqm.a[a0:a1])
elif opcode == 'insert':
output.append("<span style=\"background:#ddFFdd\">" + seqm.b[b0:b1] + "</span>")
elif opcode == 'delete':
output.append("<strike style=\"background:#FFdddd\">" + seqm.a[a0:a1] + "</strike>")
elif opcode == 'replace':
output.append("<span style=\"background:#FFFFdd\">" + seqm.b[b0:b1] + "</span>")
# raise NotImplementedError, "what to do with 'replace' opcode?"
#else:
# raise RuntimeError, "unexpected opcode"
return ''.join(output)
if this_sum > 1:
fromfile = "./summaries/summary.%s.html"%no_old
tofile = "./summaries/summary.%s.html"%this_sum
fromlines = open(fromfile, 'r').read()
tolines = open(tofile, 'r').read()
sm= difflib.SequenceMatcher(None, fromlines, tolines)
credetials = open("summary.html", "w")
credetials.write(show_diff(sm))
credetials.close()
else:
tofile = "./summaries/summary.%s.html"%this_sum
tolines = open(tofile, 'r').read()
credetials = open("summary.html", "w")
credetials.write(tolines)
credetials.close()
display(HTML("<a href='http://localhost:8888/files/Documents/Python%%20Scripts/trello-sum/summaries/summary.%s.html' target='_new'>Current Board</a>"%this_sum))
In [ ]: