Excel Almighty and Redmine: how to generate tasks right in Excel

In far from IT project organizations, Excel is often used as a processing tool for God knows what data.

I was faced with the task of setting up, in a very short time, in Excel monitoring of a very specific project with passing assignments to the project departments. Regarding the monitoring of the TOR, it was more or less defined and all the functionality was implemented by means of Excel VBA itself.

With the issuance of tasks it was not so clear. Having creaked the chair, I decided to try sending tasks to Redmine directly from Excel, getting back the hyperlink and the task number.
')
The article was written by a design engineer for the same beginners in programming, automating the routine work with improvised means. I would welcome any comments!

Why is this at all?

It so happened that Excel in the design industry is de facto the same standard when exchanging all sorts of lists of documentation and specifications between collaborating organizations as AutoCAD for drawings.

I used to think that this was only in our organization, and advanced offices use smart tools and databases that eliminate duplication and data integrity problems. But in fact, it turned out that even large companies on multibillion-dollar projects use Excel in the “back and forth” mode when sharing data and technical documentation with counterparties.

In such projects, hundreds of sets of technical documentation may be in development at the same time. For example, we are regularly sent documents with an attached Excel table for ~ 4500 lines, where each line determines the current status of a specific document. When a certain status is achieved, each document should be launched to work: translated, agreed, adapted to GOST, etc.

It is convenient to launch such tasks of the same type in Redmine directly from Excel, forming them in accordance with the data from the table.

In addition to all the above, I already had some experience of writing crutches on vba, and the approximate solution to the problem was spinning in my head.

I will not dwell on the installation and configuration of Redmine itself. Those who have not yet come across this project management system can try demo , or download and run a stack from bitnami . Documentation on working with the Bitnami Redmine virtual machine can be found here .

The source of information on the API - Redmine API .

A simple example of creating a task

So, we go into the vba editor in Excel, create a new module and enter the following into it:

'  Redmine,      , '      url,     '       Const REDMINE_URL = "http://redmine_url" '      'Const REDMINE_URL = "http://user:password@redmine_url '    ,     PostIssue ' ( ,   ,        '   ,    ) Public issue_url, issue_id As String Sub Redmine_Create_Issue() Dim ReqStatus As Boolean Dim PROJECT_ID, TRACKER_ID, ASSIGNED_TO_ID, CATEGORY_ID As Integer Dim Subject, Body, DUE_DATE, REDMINE_API_KEY As String ' ID     Redmine '   ,      Redmine PROJECT_ID = 32 TRACKER_ID = 1 ASSIGNED_TO_ID = 20 'ID ,      '         Subject = " " Body = " " '     DUE_DATE = Format(ActiveSheet.Cells(ActiveCell.Row, 12), "yyyy-mm-dd") 'REDMINE_API_KEY = "e11234567891234567891234567891234567bce0" '   API key '        ReqStatus = PostIssue(PROJECT_ID, TRACKER_ID, ASSIGNED_TO_ID, Subject, Body, DUE_DATE, _ REDMINE_API_KEY, CATEGORY_ID) ' ,    If ReqStatus <> False Then MsgBox "Redmine: Ok,  " '      2      ActiveSheet.Cells(ActiveCell.Row, 2) = issue_id ActiveSheet.Hyperlinks.Add Range("B" & ActiveCell.Row), issue_url, "", _ " " & issue_url '      11   ) ActiveSheet.Cells(ActiveCell.Row, 11) = Date Else MsgBox "Redmine: ,   " End If End Sub '       xml Function PostIssue(ByVal PROJECT_ID As Integer, ByVal TRACKER_ID As Integer, _ ByVal ASSIGNED_TO_ID As Integer, ByVal Subject As String, _ ByVal Body As String, ByVal DUE_DATE As String, _ ByVal REDMINE_API_KEY As String, ByVal CATEGORY_ID As String) Dim xhr Dim RequestURL As String Dim RequestBody As String RequestURL = REDMINE_URL & "/issues.xml?format=xml" '   API key 'RequestURL = REDMINE_URL & "/issues.xml?format=xml&key=" & REDMINE_API_KEY Set xhr = CreateObject("Microsoft.XMLHTTP") xhr.Open "GET", RequestURL, False xhr.SetRequestHeader "Content-Type", "text/xml" RequestBody = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" RequestBody = RequestBody & "<issue>" RequestBody = RequestBody & "<project_id>" & PROJECT_ID & "</project_id>" RequestBody = RequestBody & "<tracker_id>" & TRACKER_ID & "</tracker_id>" RequestBody = RequestBody & "<assigned_to_id>" & ASSIGNED_TO_ID & "</assigned_to_id>" RequestBody = RequestBody & "<subject>" & Subject & "</subject>" RequestBody = RequestBody & "<description>" & Body & "</description>" RequestBody = RequestBody & "<due_date>" & DUE_DATE & "</due_date>" RequestBody = RequestBody & "</issue>" ' ,    xhr.Send (RequestBody) If xhr.Status = 201 Then PostIssue = True Else PostIssue = False End If '     issue_url = xhr.getResponseHeader("location") issue_id = Right(issue_url, Len(issue_url) - InStrRev(issue_url, "/")) End Function 

Note: for authorization in Redmine, you can use the login and password, or the API key, which can be viewed in the profile. In the example above, the login-password is used, the lines with the key are commented out.

Everything is good, but what about the parameters that we can learn only from the Redmine database? I mean the ID for the project, the tracker, and to whom the task is assigned.

In my case, all these parameters are related to the data from the table (project, its status, the responsible department), so I acted simply, albeit incorrectly from the point of view of universality. I looked at the ID in the Redmine database via phpMyAdmin and created on a separate sheet a settings tab, where I registered these IDs for the parameters I needed from the main table. As a result, the user only needs to select the desired row in the table and click the submit task button, no additional dialog boxes.

I have slightly simplified the actual logic for clarity, but the meaning is the same - we take the value from the current line and compare the table with the ID.


As you can see, in my case, enter the ID quite a bit: a project with several subprojects, four trackers, and a little more than a dozen responsible department heads for whom the task should initially be assigned.

Thus, instead of:

 PROJECT_ID = 32 TRACKER_ID = 1 ASSIGNED_TO_ID = 20 

I wrote something like this:

 PROJECT_ID = 0 TRACKER_ID = 0 ASSIGNED_TO_ID = 0 Set ID_WS = Application.ThisWorkbook.Sheets("ID") last_row = ID_WS.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To last_row If ActiveSheet.Cells(ActiveCell.Row, 3) = ID_WS.Cells(i, 2) Then PROJECT_ID = ID_WS.Cells(i, 3) End If If ActiveSheet.Cells(ActiveCell.Row, 4) = ID_WS.Cells(i, 5) Then TRACKER_ID = ID_WS.Cells(i, 6) End If If ActiveSheet.Cells(ActiveCell.Row, 10) = ID_WS.Cells(i, 8) Then ASSIGNED_TO_ID = ID_WS.Cells(i, 9) End If If PROJECT_ID <> 0 And TRACKER_ID <> 0 And ASSIGNED_TO_ID <> 0 Then Exit For Next 

Parsing xml

A more correct and complex approach is to read the necessary data directly from Redmine. Then the API will help us again.

We will need the following functions:

XMLtoArray - function xml parsing starting from the specified node. It requires the connection of an additional Microsoft XML library, so if anyone knows how to make it easier, please tell me.


SWAP - the function of permuting the columns in a two-dimensional array (taken from here ).


Now you can make requests in Redmine:

List of projects:

 RequestURL = REDMINE_URL & "/projects.xml?include=trackers" Arr_childNodes_projects = Array("id", "name", "trackers@id", "trackers@name", _ "identifier", "description", "parent@id", "parent@name", _ "status", "is_public", "created_on", "updated_on") Arr_projects = XMLtoArray(RequestURL, "project", Arr_childNodes_projects) Arr_projects_SWAP = SWAP(Arr_projects, 1) 

At the output we get two arrays - an array with all the data obtained, and a trimmed SWAP array with only the columns we need, in this case, with the names of the projects that can be output to the form for the user.

Similarly, we get:





This data is quite enough for the implementation of the task creation functional, for example, using this form:



This form was made under Word (in fact it does not change, since the vba code is the same in Excel) for another project, with a parallel generation of a print and signature task for our bureaucrats. But this, as they say, is another story.