CODEWITHSUNDEEP
vbarestapims-access
FreeMan
FreeMan

Reputation: 5687

REST API access via VBA returns "Invalid ID/Key" error

I am attempting to access the Appointments-Plus.com API via VBA code and am consistently being told I'm giving it an invalid site-id/key. I'm using Access from the Office365 version.

This is the relevant documentation for this API call.

When I use Postman to test out the API, I am able to successfully connect and I get data back. The URL Postman puts together is this:

https://ws.appointment-plus.com/Locations/GetLocations?Authorization:Basic=<site-ID>:<Key>&response_type=xml

My VBA code is this:

Public Sub RESTtestBigURL()

  Dim responseType As String
  responseType = "response_type=json"

  Dim restRequest As WinHttp.WinHttpRequest
  Set restRequest = New WinHttp.WinHttpRequest

  Dim restResult As String

  With restRequest
    .Open "POST", "https://ws.appointment-plus.com/Locations/GetLocations?Authorization:Basic=<site-ID>:<Key>&response_type=xml", False
    .Send
    .WaitForResponse
    Debug.Print ".ResponseText: " & .ResponseText
    Debug.Print ".Status: " & .Status
    Debug.Print ".StatusText: " & .StatusText
    Debug.Print ".ResponseBody: " & .ResponseBody
  End With

End Sub

I know that the first question is "are you sure you've got the <site-ID> and <key> correct???" Yes - I've copy/pasted the entire URL from Postman into my VBA code, and I've had another couple of pairs of eyeballs review it to confirm that they're still the same.

When I run that code, I get:

.ResponseText: <?xml version="1.0" encoding="utf-8" ?>
<APResponse>
  <resource>customers</resource>
  <action>getcustomers</action>
  <request></request>
  <result>fail</result>
  <count>0</count>
  <errors>
    <error><![CDATA[Web Services authentication failed: invalid Site ID or API Key]]></error>
  </errors>
</APResponse>

I've tried several other methods of accessing the API, all of which are giving me the same "Invalid ID/Key" error:

Public Sub SecondRESTtestMSXML()

  Dim restRequest As MSXML2.XMLHTTP60
  Set restRequest = New MSXML2.XMLHTTP60

  With restRequest

    .Open "GET", URL & REQUEST_GET_LOCATIONS, True
    .SetRequestHeader "Authorization", "Basic" & SITE_ID & ":" & API_KEY
    .SetRequestHeader "response_type", "xml"
    .SetRequestHeader "Accept-Encoding", "application/xml"
    .Send "{""response_type"":""JSON""&""location"":""582""}"
    While .ReadyState <> 4
      DoEvents
    Wend
    Debug.Print ".ResponseText: " & .ResponseText
    Debug.Print ".Status: " & .Status
    Debug.Print ".StatusText: " & .StatusText
    Debug.Print ".ResponseBody: " & .ResponseBody

  End With

End Sub

There is a suggestion that this is a duplicate of another question that was resolved by Base64-encoding. However, this method, while it wasn't explicit, shows that I have attempted that, too. I've added the Base64Encode function code that is called from here.

Public Sub RESTtest()

  Dim restRequest As WinHttp.WinHttpRequest
  Set restRequest = New WinHttp.WinHttpRequest

  Dim restResult As String

  With restRequest
    .Open "POST", URL & REQUEST_GET_LOCATIONS, True
    .SetRequestHeader "Authorization", "Basic " & SITE_ID & ":" & Base64Encode(API_KEY)
' Note call to Base64Encode() on this line ---------------- ----- ^^^^^^^^^^^^
    .Option(WinHttpRequestOption_EnableRedirects) = False
    .Send "{""response_type"":""JSON""}"
    .WaitForResponse
    Debug.Print ".ResponseText: " & .ResponseText
    Debug.Print ".Status: " & .Status
    Debug.Print ".StatusText: " & .StatusText
    Debug.Print ".ResponseBody: " & .ResponseBody
  End With

End Sub

Public Function Base64Encode(ByVal inputText As String) As String
  Dim xmlDoc As Object
  Dim docNode As Variant
  Set xmlDoc = CreateObject("Msxml2.DOMDocument.3.0")
  Set docNode = xmlDoc.createElement("base64")
  docNode.DataType = "bin.base64"
  docNode.nodeTypedValue = Stream_StringToBinary(inputText)
  Base64Encode = docNode.Text
  Set docNode = Nothing
  Set xmlDoc = Nothing
End Function

Notes:

Are there any obvious errors that anyone sees in this code? Are there any suggestions for other methods to attempt to call the API and get results? I've had a suggestion to write a DLL in C# and call that, however, I don't have the time to learn enough C# to make that happen, so switching languages isn't really an option here.

Additional notes:

I tried this using curl in a Powershell session, and it gives me the same result:

PS H:\> curl -method Post -uri "https://ws.appointment-plus.com/Locations/GetLocations?Authorization:Basic=<ID>:<key>&response_type=json"

The result: 

StatusCode        : 200
StatusDescription : OK
Content           : {"resource":"locations",
                     "action":"getlocations",
                     "request":"",
                     "result":"fail",
                     "count":"0"
                    ,"errors":[
                        "Web Services authentication failed: invalid Site ID or API ...
RawContent        : HTTP/1.1 200 OK
                    Pragma: no-cache
                    Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
                    Date: Mon, 26 Aug 2019 17:28:41 GMT
                    Expires: Thu, 19 Nov 1981 08:52:00 GMT
                    Set-Cooki...
Forms             : {}
Headers           : {[Pragma, no-cache], [Cache-Control, no-store, no-cache, must-revalidate, post-check=0,
                    pre-check=0], [Date, Mon, 26 Aug 2019 17:28:41 GMT], [Expires, Thu, 19 Nov 1981 08:52:00 GMT]...}
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        : mshtml.HTMLDocumentClass
RawContentLength  : 207

Upvotes: 1

Views: 1147

Answers (1)

Ryan Wildry
Ryan Wildry

Reputation: 5677

Per the exchanges in the comments, the main issue appears to be how the basic authorization header was being formed.

For future readers, the format for the authorization header is:

.SetRequestHeader "Authorization", "Basic " & Base64Encode(SITE_ID & ":" & API_KEY)

Also, another issue you may run into is related here. Linebreaks are inserted into the Base64 encoded string with the current approach, which won't play nice with most (if not all) APIs. A suggested fix for this would be something like:

Public Function Base64Encode(ByVal inputText As String, Optional removeBlankLines = True) As String
    Dim xmlDoc As Object
    Dim docNode As Variant
    Set xmlDoc = CreateObject("Msxml2.DOMDocument.3.0")
    Set docNode = xmlDoc.createElement("base64")
    docNode.DataType = "bin.base64"
    docNode.nodeTypedValue = Stream_StringToBinary(inputText)
    Base64Encode = docNode.Text
    Set docNode = Nothing
    Set xmlDoc = Nothing

    'remove blank line characters ASCII --> 10,13,10 + 13
    If removeBlankLines Then Base64Encode = Replace(Replace(Replace(Base64Encode, vbCrLf, vbNullString), vbLf, vbNullString), vbCr, vbNullString)
End Function

Upvotes: 1

Related Questions