Build a script in Excel to make an REST API call to HOPEX

[oguimard]

⭐️⭐️⭐️ Have a look at a VB script that make an HTTP request to HOPEX to feed an excel file with the list of application coming from HOPEX. The video show the few methods and call to make.

Option Explicit

Public Const urlGraphQL As String = "https://s000002nug.labsmega.com/hopexgraphQL/api/ITPM"
Public Const urlUAS As String = "https://s000002nug.labsmega.com/UAS/connect/token"
Public Const grant_type As String = "password"
Public Const scope As String = "hopex openid read write"
Public Const username As String = "xxxxxxxxxxxxxxx"
Public Const password As String = "xxxxxxxxxxxxxxxxx"
Public Const client_id As String = "HopexAPI"
Public Const client_secret As String = "secret"
Public Const environmentId As String = "vkHlStRiTDo5"
Public Const repositoryId As String = "vqXt9MVvPjoL"
Public Const ProfileId As String = "I9iCqGJg)u00"

Public Sub Main()
Dim bearer As String
bearer = getBearerToken()

Dim query As String
Dim graphQLResult As Dictionary

query = "{""query"":""{application {id name }}""}"

Set graphQLResult = getGraphQLResponse(bearer, urlGraphQL, query)

writeInExcelSheet graphQLResult

End Sub

Public Sub writeInExcelSheet(graphQLjson As Dictionary)

Dim thisWorkbook As Workbook
Dim thisWorksheet As Worksheet

Set thisWorkbook = application.ActiveWorkbook
Set thisWorksheet = thisWorkbook.Worksheets("Sheet1")

Dim data As Dictionary
Set data = graphQLjson.Item("data")

Dim i, j, count As Integer
Dim applicationCollection As Collection

Set applicationCollection = data.Item("application")

count = applicationCollection.count

thisWorksheet.Cells(1, 1) = "Absolute Identifier"
thisWorksheet.Cells(1, 2) = "Short Name"

j = 2
For i = 1 To count
Dim oApplication As Dictionary
Set oApplication = applicationCollection.Item(i)

Dim name As String
Dim id As String

name = oApplication.Item("name")
id = oApplication.Item("id")

thisWorksheet.Cells(j, 1) = id
thisWorksheet.Cells(j, 2) = name

j = j + 1
Next i

End Sub

Public Function getGraphQLResponse(bearer As String, urlGraphQL As String, graphQLQuery As String) As Dictionary
Dim Url As String

Dim Client As New WebClient
Dim Request As New WebRequest
Dim Response As New WebResponse

EnableLogging = True

Client.BaseUrl = urlGraphQL

' We make a POST Request
Request.Method = WebMethod.HttpPost
Request.RequestFormat = WebFormat.Json
Request.Format = WebFormat.Json

Dim HopexContext As String

HopexContext = "{""EnvironmentId"":""" & environmentId & """,""RepositoryId"":""" & repositoryId & """,""ProfileId"":""" & ProfileId & """}"

Request.AddHeader "Authorization", "Bearer " & bearer & ""
Request.AddHeader "x-hopex-context", HopexContext

Request.Body = graphQLQuery

Set Response = Client.Execute(Request)

If (Response.StatusCode = Ok) Then
Dim responseContent As String
responseContent = Response.Content

Dim responseJson As Dictionary
Set responseJson = JsonConverter.ParseJson(responseContent)

Set getGraphQLResponse = responseJson
Else
MsgBox ("GraphQL =" & Response.StatusCode & " : " & Response.Content)

End If

Set Client = Nothing
Set Request = Nothing
Set Response = Nothing
End Function

Public Function getBearerToken() As String
Dim Url As String

Dim Client As New WebClient
Dim Request As New WebRequest
Dim Response As New WebResponse

EnableLogging = True

Client.BaseUrl = urlUAS

' We make a POST Request
Request.Method = WebMethod.HttpPost
Request.RequestFormat = WebFormat.FormUrlEncoded
Request.Format = WebFormat.FormUrlEncoded


' we setup the body content
Dim Body As New Dictionary
Body.Add "grant_type", grant_type
Body.Add "scope", scope
Body.Add "username", username
Body.Add "password", password
Body.Add "client_id", client_id
Body.Add "client_secret", client_secret
Body.Add "environmentId", environmentId
Set Request.Body = Body

Set Response = Client.Execute(Request)

If (Response.StatusCode = Ok) Then
Dim responseContent As String
responseContent = Response.Content

Dim responseJson As Object
Set responseJson = JsonConverter.ParseJson(responseContent)

Dim access_token As String
access_token = responseJson("access_token")

getBearerToken = access_token
Else
MsgBox ("Bearer =" & Response.StatusCode & " : " & Response.Content)

End If

Set Client = Nothing
Set Request = Nothing
Set Response = Nothing

End Function