Update RMsis from Excel Using RMsis APIs

The following example demonstrates the use of RMSis API's which is used to create a report in MS-Excel after dynamically fetching the data from RMsis using macros.

Pre-Requisites:

RMsis version v1.8.9.2 or higher.
MS-Excel with developer module to write macro.

The code of the excel macro is mentioned below, it contains following steps:

Login into JIRA user a valid username and password.
Fetch data from RMsis using API's.
Create requirements in RMsis by selecting a cell in excel and clicking on create requirement macro.

Excel Macro

'Function to fetch details using RMsis graph APIs
Function GetRequestfromRMsis(rmsisApiString) As String
Dim stringURL As String
Dim auth
'Base 64 encoded username & password used to authenticate the user
'Update Required
auth = "(YWRtaW46YWRtaW4=)"
Set restRequest = CreateObject("Msxml2.ServerXMLHTTP.6.0")
'Change URL as per the JIRA Base URL of your installation
'Here Base URL is http://10.0.1.10:8080
'Update Required
stringURL = "http://10.0.1.10:8080/rest/service/latest/rmsis/graphql?query="
restRequest.Open "GET", stringURL & rmsisApiString, False
restRequest.SetRequestHeader "Content-Type", "application/json"
restRequest.SetRequestHeader "Authorization", "Basic " & auth
restRequest.Send
If restRequest.status = "401" Then
    MsgBox "Not authorized"
    Else
    'MsgBox restRequest.ResponseText
    GetRequestfromRMsis = restRequest.ResponseText
End If
End Function
'Macro to create requirement using data from selected cell
Sub CreateRequirement()
    Dim rmsisProjectId As Long
    Dim rmsisRequirementId As Long
    Dim requirementSummary As String
    requirementSummary = """"
    rmsisProjectId = 766
    ActiveCell.Select
    MsgBox (requirementSummary)
    If Selection.value <> "" Then
        requirementSummary = requirementSummary & Selection.value & """"
        MsgBox (requirementSummary)
        rmsisApiString = "mutation {createUnplannedRequirement(projectId: prjId, summary: reqSummary) {id}}"
        rmsisApiString = Replace(rmsisApiString, "prjId", rmsisProjectId)
        rmsisApiString = Replace(rmsisApiString, "reqSummary", requirementSummary)
        MsgBox (rmsisApiString)
        ResponseText = GetRequestfromRMsis(rmsisApiString)
        'MsgBox (ResponseText)
        Set JSON = ParseJson(ResponseText)
        rmsisRequirementId = JSON("data")("createUnplannedRequirement")("id")
        'If result.value <> "" Then
            'MsgBox ("Successfully Created")
        'Else
            'MsgBox ("Error while Creating Requirement")
        'End If
    Else
            MsgBox ("Please select a non empty cell.")
    End If
End Sub