Update JIRA endpoints from Excel Macro using REST API calls

 To update JIRA issue from Excel Macro follow these steps.

1. Create a webhook automation

2. Create a MAcro in Excel to update

'macro to upload data back into Jira

Sub updateJIRAFunc(Key As String)

On Error GoTo eh

  Dim objHTTP As Object

  'Json = "{    ""key"": ""SCP-1409"",    ""duedate"" : ""2018-03-03""}"

    Dim Json As String

    Json = "{"

    Json = Json & """key"": """ & Key & """"

    Json = Json & "}"

    'MsgBox (Json)

    Dim result As String

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

    Dim Url As String

        Url = "https://automation.atlassian.com/pro/hooks/3b5d3807"

        If Left(Key, 3) = "SCP" Then

        Url = "https://automation.atlassian.com/pro/hooks/b786e191d456544dc2239ba7b3b"

        End If

    objHTTP.Open "POST", Url, False

   objHTTP.setRequestHeader "Content-type", "application/json"

   'objHTTP.setRequestHeader "apikey", ""

   objHTTP.send (Json)

   result = objHTTP.responseText

   'Some simple debugging

   ' Set the worksheet

   Range("I3").Value = result

   Set objHTTP = Nothing

   Exit Sub

eh:

    Range("I3").Value = Err.Description

End Sub

Step3. Run that in a for loop on click of a button

Sub Button1_Click()

 

 'updateJIRAFunc ("OM-24545")

 

 Dim ws As Worksheet

   Dim lastRow As Long

   Dim i As Long

   ' Set the worksheet

   Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to the sheet's name

   ' Find the last row with data in the first column

   lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

   

   'Application.ActiveWorkbook.Worksheets("exportDataToJira").Range("F3:F7") = Format(Date, "yyyy-mm-dd")

   'Application.ActiveWorkbook.Worksheets("exportDataToJira").Range("F3:F7").NumberFormat = "yyyy-mm-dd"

   'Range("F3:F7").NumberFormat = "yyyy\-mm\-dd"

   ' Loop through the rows

   For i = 4 To lastRow ' Assuming data starts from row 2

   ' Get inputs from columns

       Dim jiraKey As String

      

       jiraKey = ws.Cells(i, 1).Value ' Jira Key is in column A

      

        'newDueDate = Replace(ws.Cells(i, 6).Value, "/", "-")

        Call updateJIRAFunc(jiraKey, i)

        ws.Cells(i, 4).Value = "1"

       

    Next i

End Sub

Written By

-Vinod Kotiya