API not passing Basic Auth credentials correctly

[yits05]

Hello,

I am a bit out of my depth here, and was hoping for some guidance on what I am doing wrong. I am trying to make the below API call through VBA, passing my API key as base64 encoded as required. However, the response has constantly been "invalid auth credentials". The API Key has been confirmed as working by the vendor.

Here is what the API documentation requires:

curl -X PUT 'https://harvest.greenhouse.io/v1/jobs/{id}'
-H "Content-Type: application/json"
-H "On-Behalf-Of: {greenhouse user ID}"
-H "Authorization: Basic abcdefg123"

It further states that the auth header needs to be structured as Authorization: Basic <base64("usernameassword")>

Since only a username needs to be provided in this call, I also need to append a : (colon) to to Greenhouse API token and then Base64 encode the resulting string.
Thank you!

Here is my code:

Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement

  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function
Public Sub ChangeCoordinators()

    On Error Resume Next
    Dim jobid As Variant
    Dim Strresponse As Variant
    Dim i As Long
    filepath = Sheets("Sheet2").Range("A" & i)
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim Sheet As Worksheet
For i = 1 To FinalRow
    Set Sheet = Sheets("Sheet2")

    Const APIkey = "sampleAPIkey123" & ":"

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

    Dim rootURL As String, registrationEndpointURL As String
    Dim registration As String
    Dim text As String
    Dim userid As Variant
    
    rootURL = "https://harvest.greenhouse.io/v1/jobs/" & filepath
    
    
    With httpReq
        .Open "PUT", rootURL, False
        xmlhttp.setRequestHeader "Content-Type: ", "application/json"
        xmlhttp.setRequestHeader "On-Behalf-Of: ", "678910"
        xmlhttp.setRequestHeader "Authorization: ", "Basic ", EncodeBase64(APIkey)
        .send ("{" & """coordinators""" & ":" & " [{" & """user_id""" & ":" & "12345" & "," & """responsible_for_future_work""" & ":" & " true" & "," & """responsible_for_active_work""" & ":" & "true" & "," & """responsible_for_inactive_work""" & ":" & "false" & "}" & "]" & "}")
        Strresponse = .responseText
        Worksheets("Users").Range("A1").Value = Strresponse
        
        
    End With

Next i

    
End Sub

 

[John_w]

Remove the colons and append the encoded API key.

        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "On-Behalf-Of", "678910"
        .setRequestHeader "Authorization", "Basic " & EncodeBase64(APIkey)

 

[yits05]

Remove the colons and append the encoded API key.

        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "On-Behalf-Of", "678910"
        .setRequestHeader "Authorization", "Basic " & EncodeBase64(APIkey)

Thanks - I tried that but still the same response

 

[John_w]

Are you still using the xmlhttp variable in those lines? You shouldn't because it's undefined and undeclared. Those lines should be exactly as I posted, inside the With httpReq ... End With block.

What is the On Error Resume Next for? Remove it because it only hides any errors. For example this line will produce an error because i is zero (the cell A0 doesn't exist):

filepath = Sheets("Sheet2").Range("A" & i)

and therefore filepath is empty. Correction - assign the correct row number to i.

Add Option Explicit at the top of the module and declare all variables.

 

[yits05]

Are you still using the xmlhttp variable in those lines? You shouldn't because it's undefined and undeclared. Those lines should be exactly as I posted, inside the With httpReq ... End With block.

What is the On Error Resume Next for? Remove it because it only hides any errors. For example this line will produce an error because i is zero (the cell A0 doesn't exist):

filepath = Sheets("Sheet2").Range("A" & i)

and therefore filepath is empty. Correction - assign the correct row number to i.

Add Option Explicit at the top of the module and declare all variables.

This worked, thank you so much.