Creating Jira Issue from Excel vba

[Andy Grace]

Hi I see a couple of threads in here where the SOAP/RPC api is used but as that is deprecated I'd like to use the REST API - I won't be able to use CURL from within vba - has anyone had any luck creating issues in this way? Any good examples? Cheers Andy

[Andy Grace · Accepted solution]

Thanks I've ended up solving by calling a curl command in a shell script from VBA - on windows I write the output of create to a file then read back, and on a Mac I can get all the returned data from the shell.

[Sam Hall]

Sounds entertaining, you should totally blog the details on that.

[Fabiano Nascimento Leite]

Following:

Sub criarIssue()

    'Necessário usar referencias: Microsoft XML, v6.0 e Microsoft WinHTTP Services, version 5.1
    Dim JiraService As New MSXML2.XMLHTTP60
    Dim sErg As Variant
    Dim sRestAntwort As Variant
    Dim sSummary As Variant
    Dim sDescription As Variant
    Dim sProject As Variant
    Dim sIssueType As Variant
    Dim sData As Variant
    Dim sPath As Variant
    Dim sUsername As Variant
    Dim sPassword As Variant
    Dim sAux As Variant
    Dim sStatus As Variant
    Dim sEncbase64Auth As Variant
    
    sUsername = Plan1.Range("B1").Value
    sPassword = Plan1.Range("B2").Value
    
    sSummary = Plan1.Range("G2").Value
    sDescription = Plan1.Range("G3").Value
    sIssueType = Plan1.Range("G4").Value
    sProject = Plan1.Range("G5").Value

    sData = " { ""fields"" : { ""project"" : { ""key"" : """ & sProject & """ }, ""summary"" : """ & _
            sSummary & """, ""description"" : """ & sDescription & """, ""issuetype"" : { ""name"" : """ & sIssueType _
                & """ } } } "

    sEncbase64Auth = FuncaoComum.EncodeBase64(sUsername & ":" & sPassword)

    Plan1.Range("G6").Value = sData & " | Auth Basic: " & sEncbase64Auth

    'Cria uma Issue
    With JiraService
        .Open "POST", "http://<jira_url>/rest/api/2/issue/", False
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Authorization", "Basic " & sEncbase64Auth
        .Send (sData)
        sRestAntwort = .ResponseText
        sStatus = .Status & " | " & .StatusText
    End With

    Set JiraService = Nothing

    ' Tratamento JSON
    sAux = Replace(sRestAntwort, "{", "")
    sAux = Replace(sAux, "}", "")
    sAux = Split(sAux, ",")
    sAux = sAux(1)
    sAux = Split(sAux, ":")
    sAux = sAux(1)
    sAux = Replace(sAux, """", "")

    Plan1.Range("C8").Value = sStatus
    Plan1.Range("F18").Value = sRestAntwort & " | " & sAux
    Plan1.Range("O2").Value = Plan1.Range("O2").Value + 1
    MsgBox "Fim"

End Sub

' Module FuncaoComum

Public Function EncodeBase64(text As String) As String
    Dim arrData() As Byte
    arrData = StrConv(text, vbFromUnicode)
 
    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement
 
    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")
 
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text
 
    Set objNode = Nothing
    Set objXML = Nothing
End Function

I thank the P.Giessler I modified his example:

https://answers.atlassian.com/questions/27182/excel-vba-jira-rest-api

[Joe Buttigieg]

Hi Fabiano, many thanks for the work that you and P.Giessler have put in here. I dont have a thorough technical background of APIs, etc but I have been able to use your examples so far to create issues, read issues, get a list of components and create a component. My next test was to edit an existing issue. I have created a new function as follows: Public Function EditIssue(sKey As Variant) As String Dim sData As Variant Dim sAux As Variant sData = " { ""fields"" : { ""summary"" : ""test test test"" } } " With JiraService .Open "PUT", sURL & "/rest/api/2/issue/" & sKey, False .setRequestHeader "Content-Type", "application/json" .setRequestHeader "Accept", "application/json" .setRequestHeader "Set-Cookie", sCookie .send sData sRestAntwort = .responseText sStatus = .status & " | " & .statusText End With So it is working - it updates the summary of the existing issue. The only problem is that I am getting a run-time error -2147467260 (80004004) after the send command. Can you see whether I am doing something wrong?

[Fabiano Nascimento Leite]

Hi Joe, I'm not remembering, but I had a similar problem and solved using: On Error Resume Next. I'll check this detail and notice you.

[Fabiano Nascimento Leite]

Joe, analyzed the VBA code that I created, I have not investigated the reason for the error, though, I realized that there is no problem sending data to Jira. So I'm using "On Error Resume Next" and had no problems so far. Hope this helps you.

[Joe Buttigieg]

Hi Fabiano, many thanks for your response. I have implemented similar into my code.

[Pranav Patel]

Hi Fabiano, Your code seems fine but in VBA, is there a way to generate the JSON string instead of manually creating it like you are doing? -Thanks

[Fabiano Nascimento Leite]

Hello Pranav Patel, Unfortunately I did not find native libraries for this, I see that there are some codes that others have created but I did tests. I'm still thinking about creating my own but I need to better understand the criteria for transforming objects to JSON. :D. I found this link which can help you https://github.com/VBA-tools/VBA-JSON I hope this helps.

[Viktor Procházka]

YES  finaly this stupid API is working, but no idea why it was failed on Cookie session. With this simple BASIC is all working well.

Thank you.

[Viktor Procházka]

Please fix this row:

.Send sData

to 

.Send (sData)

 Under Windows 7 Office Excel 2013 without "()" is not working. It's function. Rest of your code is FINE.

[Fabiano Nascimento Leite]

Hi Viktor,

fixed!

[jenni]

@Joe Buttigieg @Fabiano Nascimento Leite

Hi Joe/Fabiano,

how did you get the code working? do you have to be an admin in JIRA? I got a "403 forbidden" error in mine.

[Joe Buttigieg]

Jenni,

The APIswork with the same authority as the UI. That is, you should be using a user that has authority to create an issue in the particular project you are testing this for.

Just looking back through the code posted earlier, I know we had to make a change to it to handle some extra security added by Atlassian. In the case of creating a new JIRA issue, an additional request header is required as follows:

.SetRequestHeader "X-Atlassian-Token", "nocheck"

[jenni]

@Joe Buttigieg thank you :)

[jenni]

@Joe Buttigieg

Now i'm getting this error: {"errorMessages":[],"errors":{"project":"project is required"}} | project

it seems that there are no variables for key in this code:

sData = " { ""fields"" : { ""project"" : { ""key"" : """ 

My apologies if this is to basic but i'm new to JIRA and i haven't successfully connected it to VBA yet.

[Joe Buttigieg]

So looks like you are connecting OK. Now your issue is just sending some valid data that will successfully create an issue. Here is am example of the data I send that works successfully for us with how our JIRA is configured. So all the words starting with "s" are the variables that are passed into the JIRACreateIssue function in VBA.

sData = " { ""fields"" : { ""project"" : { ""key"" : """ & sProject & """ }, ""assignee"" : { ""name"" : """ & sAssignee & """ }, ""reporter"" : { ""name"" : """ & sReporter & """ }, ""summary"" : """ & sSummary & """, ""description"" : """ & sDescription & """, ""issuetype"" : { ""name"" : """ & sIssueType & """ } , ""components"" : [ { ""name"" : """ & sComponent & """ } ] } } "

[jenni]

@Joe Buttigieg 

I copied the same format and i'm having the same issue. :(

[Joe Buttigieg]

Sorry, then the problem is likely to be what you are trying to create nothing to do with the code. What always helps me is to go to the UI and create a new issue that way. See what fields you needed to enter. Make sure they are included in your sData. Make sure you are passing in correct variables. 

[jenni]

thank you @Joe Buttigieg

[jenni]

@Joe Buttigieg

I resolved the previous error. Now I have this.

{"errorMessages":[],"errors":{"summary":"Field 'summary' cannot be set. It is not on the appropriate screen, or unknown.","description":"Field 'description' cannot be set. It is not on the appropriate screen, or unknown.","components":"Field 'components' cannot be set. It is not on the appropriate screen, or unknown.","assignee":"Field 'assignee' cannot be set. It is not on the appropriate screen, or unknown.","labels":"Field 'labels' cannot be set. It is not on the appropriate screen, or unknown."}} | summary

I tried the following instructions from @Shah Zaman but I don't seem to have the following options. I'm using JIRA v7.2.1

You may find the 'Create Issue' as;

• Go to your JIRA project main page.
• Click the "Project administration" , on the bottom of left sidebar.
• Select the "Screens"  from left sidebar.
• Now click the "Default Screen" . .
• Here is your "Create Issue" screen.
• Add any field that is missing.
• ......and your code should succeed.

Just wondering if you encountered the same errors.

Thank you!

[Joe Buttigieg]

If all those fields are relevant for when you create a jira issue,  then you might want to double check the double quotes " you are using.  Possibly you have too many or not enough.  

[jenni]

@Joe Buttigieg 

Got rid of the errors and now I'm getting this one:

"Anonymous users do not have permission to create issues in this project. Please try logging in first."

I used EncodeBase64 for the authentication. I'm not sure if this is supported by V7 though.

[Joe Buttigieg]

e use the Cloud, so not sure. If you were getting those other errors, it would suggest you were authorised at some stage. 

[jenni]

@Joe Buttigieg

Thanks for responding so quickly. 

From the code above, sRestAntwort = .ResponseText is not returning any values for mine. Just wondering if you have encountered a similar issue.

[Joe Buttigieg]

No not that

[jenni]

@Joe Buttigieg

Where you able to update the resolution and status fields using your code above?

--> With JiraService .Open "PUT", sURL & "/rest/api/2/issue/" & sKey, False

[Joe Buttigieg]

No, you would normally do that via a transition. 

[Fabiano Nascimento Leite]

Hi,

I created a topic of how to attach a spreadsheet JIRA.

It should work for any file (not tested).

Link: https://answers.atlassian.com/questions/9377423

[Teck-En]

we don't have materials for that but there are answers post and tutorials from other smart users out there which you can use to get started:

• https://answers.atlassian.com/questions/123670/how-to-create-an-issue-with-rest-api-using-c
• http://maffelu.net/jira-basic-cjira-connection-using-rest/

If you have trouble, you can post your qustion over here and some other experienced users will be possible to shed some light for you. :)