Creating Jira Issue from Excel vba

Andy Grace June 13, 2013
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

4 answers

1 accepted

2 votes
Answer accepted
Andy Grace July 2, 2013

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 18, 2013

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

4 votes
Fabiano Nascimento Leite January 26, 2014
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 September 25, 2014

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 September 25, 2014

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 September 25, 2014

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 September 26, 2014

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

Pranav Patel June 25, 2015

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 July 8, 2015

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 December 15, 2017

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 December 18, 2017

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 December 18, 2017

Hi Viktor,

fixed!

jenni August 6, 2018

@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 August 6, 2018

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"

Like Kev Trasler likes this
jenni August 6, 2018

@Joe Buttigieg thank you :)

jenni August 6, 2018

@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 August 6, 2018

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 & """ } ] } } "

Like Richard Summers likes this
jenni August 6, 2018

@Joe Buttigieg 

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

Joe Buttigieg August 6, 2018

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 August 6, 2018

thank you @Joe Buttigieg

jenni August 7, 2018

@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 August 7, 2018

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 August 7, 2018

@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 August 7, 2018

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

jenni August 8, 2018

@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 August 8, 2018

No not that

jenni August 20, 2018

@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 August 20, 2018

No, you would normally do that via a transition. 

0 votes
Fabiano Nascimento Leite September 25, 2014

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

0 votes
Teck-En
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
June 13, 2013

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:

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. :)

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events