Engineering Inc. has set up a Kanban board in Trello for tracking an important business process. This has been a huge improvement over there existing email workflow and now they are looking to make the process even better with analytics, slow project tracking, and easy project creation.
An Excel Workbook has already been set up with analytics functionality and new project form, we just need to integrate Trello. The following functions need to be completed:
Public Function LoadProjects() As Collection
' TODO
End Function
Public Sub MarkSlowProjects(Projects As Collection)
' TODO
End Sub
Public Sub CreateProject(ByRef Project)
' TODO
End Sub
Oftentimes, the most difficult part of working with APIs is getting everything set up to connect to them. Each one has its own set of required keys, tokens, and other items that need to be generated/retrieved in order to access the system.
From Trello’s Getting Started documentation:
Following the directions in the Getting Started documentation, an Application Key and User Token are created. A specific user (VBA-Web Bot) will be used to access Trello so a user token only needs to generated once, but your app may need to generate tokens for other users.
For this example, the following example values will be used:
key...
token...
With the Application Id and User Token ready, let’s run a quick test to see if we can retrieve our Kanban board.
Add a temporary GetBoard
method to the Trello
module. The BoardId
value can be found from the board URL when viewing the board (e.g. for https://trello.com/b/iBpxWmUu/engineering-inc
the BoardId
is iBpxWmUu
).
WebClient
that will handle requests and responses and is responsible for shared functionality like authentication, proxy configuration, and security. For the Trello API, all requests start with “https://api.trello.com/1/” so this will be shared between all requests with BaseUrl
.WebRequest
is used to create detailed requests (including formatting, querystrings, headers, cookies, and much more). VBA-Web aims to make every part of the request configurable, so there are helpers to avoid building strings for URLs or Body values by hand and other tedious and potentially error-prone methods of creating requests.WebResponse
wraps http and cURL repsonses and includes parsed Data
based on WebRequest.ResponseFormat
.' Trello.bas
Private Const ApplicationKey As String = "key..."
Private Const UserToken As String = "token..."
Private Const BoardId As String = "iBpxWmUu"
Sub GetBoard()
Dim Client As New WebClient
Client.BaseUrl = "https://api.trello.com/1/"
Dim Request As New WebRequest
' Anti-pattern: Building URL by hand
Request.Resource = "boards/" & BoardId & "?key=" & ApplicationKey & "&token=" & UserToken
' Preferred
Request.Resource = "boards/{board_id}"
Request.AddUrlSegment "board_id", BoardId
Request.AddQuerystringParam "key", ApplicationKey
Request.AddQuerystringParam "token", UserToken
' Defaults:
' Request.Format = WebFormat.Json
' Request.Method = WebMethod.HttpGet
Dim Response As WebResponse
Set Response = Client.Execute(Request)
Debug.Print Response.StatusCode & ": " & Response.Content
End Sub
Hopefully, the above test went smoothly, but if there were issues, how do you debug what happened?
Enable logging with WebHelpers.EnableLogging = True
and open the Immediate Window (View > Immediate Window
or ctrl+g
) to view the raw request that was sent and response recieved.
Sub GetBoard()
WebHelpers.EnableLogging = True
' ...
End Sub
' --> Request - #:##:## AM
' GET https://api.trello.com/1/boards/iBpxWmUu?key=key...&token=token...
' ...
'
' <-- Response - #:##:## AM
' 200 OK
' ...
'
' {"id":"5431d8cf70be14fc345c8e35","name":"Engineering Inc.",...}
With the GetBoard
test successful, we’re ready to start work on the LoadProjects
method. First, let’s check out what we need for the KanbanProject
class.
...
Examining Trello’s API docs, we can get this information with…
' Trello.bas
Private Const ApplicationKey As String = "key..."
Private Const UserToken As String = "token..."
Private Const BoardId As String = "iBpxWmUu"
Function LoadProjects() As Collection
Dim Client As New WebClient
Client.BaseUrl = "https://api.trello.com/1/"
Dim Request As New WebRequest
Request.Resource = "boards/{board_id}"
Request.AddUrlSegment "board_id", BoardId
Request.AddQuerystringParam "key", ApplicationKey
Request.AddQuerystringParam "token", UserToken
Dim Response As WebResponse
Set Response = Client.Execute(Request)
Dim Projects As New Collection
Dim Project As KanbanProject
Dim Card As Dictionary
For Each Card In Response.Data("cards")
Set Project = New KanbanProject
' TODO...
Projects.Add Project
Next Card
Set LoadProjects = Projects
End Collection