Why I wanted to Access Trello data

My work transitioned to an agile format and we’ve used Trello to track and monitor our sprints. Our scrum master was tracking our hoursby looking at our Trello sprint board and manually updating a spreadshhet. Since we are constantly updating our sprint board, I thought I would look at ways to automate that process for him.

As expected, someone had already built an R package for accessing data from Trello via the Trello API. Shout out to Jakub Chromec for creating the trelloR package. I haven’t used the trelloR packakge, so my intent was to share my experience using it for the first time. I should note that the package had a pretty big disclaier:

Disclaimer: trelloR is not affiliated, associated, authorized, endorsed by or in any way officially connected to Trello, Inc. (www.trello.com).

Since my work Trello account is password protected, I created a public board that uses the same conventions. From reading the package information,this process should be the same for accessing private Trello board excpet you need to include a Trello token.

For now, here is the public board I created to mimic our work sprint board.

My goal and our current use

My goal w#s to automate tracking the total hours not started, in progress, and completed once a day. Some basic conventions we use on our Trello board are as follows:

• We create a card for each task we have
• Each task is assigned to one or multiple people by adding a memebr to the card
• Each task is assined to a story by adding a label to the card
• Each task has an estimated hours attached to it by adding a numeric value within braces [Number goes here] as the last part of the card.

Basic setup

First I did some basic environment set up by using the here and checkpoint before loading the required packages. (Note: I ran into some issues with here and checkpoint so I commented those out for blogdown)

# library(here)
# library(checkpoint)
# 
# checkpoint("2019-07-01", R.version = "3.5.2",
#            checkpointLocation = here::here())


library(trelloR)
library(tidyverse)
library(lubridate)

Connect to Trello board & get data

Next I connected to the Trello board and captured the neccesary data. This was a little bit of trial and error, but the package has some very simple functions to get the data.

I wanted to track the hours by each list (i.e. “Not Started”, “In Progress”, “Completed”), so I ended up getting that information via the get_board_lists function and getting the individual card data with the get_board_cards function.

# ![Sample Trello board I created](img/trello.PNG)
trello.board <- trelloR::get_id_board("https://trello.com/b/ZA6mkMo2/totally-real-sprint-backlog")
## Sending request...
## Request URL:
## https://api.trello.com/1/board/ZA6mkMo2?fields=name&limit=1000
## Converted into character vector of length 1 with name "Totally Real Sprint Backlog "

#Get Lists 
current.lists <- get_board_lists(trello.board) 
## Sending request...
## Request URL:
## https://api.trello.com/1/board/5d1b2ff197b7098b43ea0cba/lists?limit=1000

#Get cards from the board
cards <- get_board_cards(trello.board)
## Sending request...
## Request URL:
## https://api.trello.com/1/board/5d1b2ff197b7098b43ea0cba/cards?limit=1000

Clean and prep data

After checking the results it appears all the data I need was captured. I quickly selected the fields I needed and did some minor cleaning.

current.lists <- current.lists %>% 
  select(id, status = name)

cards <- cards %>%
  select(idList, task = name, labels, dateLastActivity) %>%
  mutate(dateLastActivity = ymd_hms(dateLastActivity)) %>% 
  unnest(.) %>%
  select(-idBoard, -id, -color) %>% 
  rename(story = name)

trello.df <- current.lists %>% 
  left_join(cards, by = c("id" = "idList"))
  

One thing I needed to do was capture the individual hours. As mentioned, our hour estimates are tacked on to the end of our tasks within brackets. I used a quick helper function to get the hours into a new column.

get_hours <- function(string) { 
  str_sub(string, 
     str_locate_all(string, '\\[')[[1]][,1]+1, 
     str_locate_all(string, '\\]')[[1]][,1]-1)
}


trello.df <- trello.df %>% 
  filter(status != "Sprint # Backlog") %>% # filtering out info for a future update
  rowwise() %>% 
  mutate(hours = get_hours(task))

Format data for export

trello.df %>% 
  group_by(status) %>% 
  summarise(hours = sum(as.numeric(hours)))
## # A tibble: 5 x 2
##   status                hours
##   <chr>                 <dbl>
## 1 Impeded                  NA
## 2 Tasks - Done              4
## 3 Tasks - In Progress      23
## 4 Tasks - Not Started       5
## 5 Test - Transfer Tasks    NA

At this point I had enough initial data to provide my scrum master and hopefuly save him some time. There is definately room for improvement, and if it looks like this helpful I’ll update this into a more functional process and add any other info he might need