CODEWITHSUNDEEP
jsonexcelvbacurlweb-scraping
Newbie_2006
Newbie_2006

Reputation: 72

Using VBA in Excel to retrieve a Json from an API (problem with the API password)

I'm having difficulties with an API (Alsi, this is the documentation: https://alsi.gie.eu/GIE_API_documentation_v003.pdf) that provides natural gas data. It allows me to put a URL into my internet browser so I can see the Json-formatted data I need. However, if I wanted to download that Json, I should use a "x-key" they also gave me with a cURL command, like this one that's given in the documentation as an example:

"https://agsi.gie.eu/api/data/eu?from=2017-06-01&till=2017-06-14" --header "x-key: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

Since we want to automatize some processes, the idea would be to have a macro-enabled Excel doc. that would retrieve this information easily so it could be used in Excel. Thanks to other open questions here in StackOverflow I have the following VBA code that seems to interact with the API, however it returns "access denied", and my best guess is that this happens because the password (or "x-key") isn't been inputted correctly. This is all I have (aside of JsonConverter and Base64Encode in other modules):

Sub test()

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sUrl = "https://alsi.gie.eu/api/data/eu?from=2018-11-15&till=2018-12-04"
 sAuth = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"


 sRequest = sUrl
 httpObject.Open "GET", sRequest, True
 httpObject.setRequestHeader "x-key", Base64Encode(sAuth)
 httpObject.send
 sGetResult = httpObject.responseText

 MsgBox sGetResult

 Dim oJSON As Object
 Set oJSON = JsonConverter.ParseJson(sGetResult)

 For Each sItem In oJSON
  dItemDate = oJSON(sItem)("date")
  sItemString = oJSON(sItem)("string")
  vItemValue = oJSON(sItem)("value")

  MsgBox "Item: " & sItem & " Date: " & dItemDate & " String: " & sItemString & " Value: " & vItemValue
 Next

End Sub

I tried also appliying Base64Encode to the password variable (sAuth) as you can see in case that was the problem, also without it, also without a password/key at all... but I always get the same "access denied" message, which is the reason why I think the password is the issue. At this point I really don't know how to make any progress, this is the closest I got to obtaining data from this API in Excel via VBA (I tried with many more chunks of code here and in other websites/forums before) and I feel I'm very close, just not there yet.

Thanks a lot in advance.

Upvotes: 1

Views: 880

Answers (1)

QHarr
QHarr

Reputation: 84465

You don't need to encode the key. Not sure why that attempt didn't work for you without though make sure you pass False in the .Open line.

I note that the response is a collection of dictionaries. Those dictionaries do not have the keys you specify in your script. 

Option Explicit
Public Sub GetJSON()
    Dim json As Object   
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://agsi.gie.eu/api/data/eu?from=2017-06-01&till=2017-06-14", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .setRequestHeader "x-key", "APIkey"
        .send
        Set json = JsonConverter.ParseJson(.responseText)
    End With
End Sub

JSON sample:

Upvotes: 1

Related Questions