Im trying to get some data from a web api from our OEE system, Factbird. I build the query in postman and it works fine there (See image). I then tried it in Excel, but that gives me an error. In PowerQuery i got a bad responce, and vba module i got "Unexpected number in JSON at position 67".

Im pretty sure its due to the batch number i try to pass along. If i make a simple " query = "{ __schema { types { name } } }"" i get the complete list. How can i send a variable so i can get reports from various batchnumbers? As far as i can see i just need to use triple quotes.

Sub SendAPIQuery()
    Dim http As Object
    Dim url As String
    Dim query As String
    Dim apiKey As String
    Dim response As String
    
    ' Set the URL and API key
    url = ";
    apiKey = "MY-API-KEY"
    
    ' Define the batch number variable
    batchNumber = "38276"

    ' Define the query
    query = "query Company { lines { batches(filter: { batchNumber: """ & batchNumber & """  }) { items { batchNumber plannedStart actualStart actualStop amount comment state plannedEtc actualEtc product { attachedControlReceipts { name description entries { entryId title } attachedProducts { parameters { key value } attachedControlReceipts { entries { title initialsSettings fields { label description } } name description } } } } controls { controlReceiptName title status timeTriggered timeControlled timeControlUpdated comment initials initialsSettings fieldValues { value controlReceiptField { label description type limits { lower upper } } } } } } } } }"
    
    ' Create the HTTP request
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    
    ' Send the request
    http.send "{""query"": """ & query & """}"
    
    ' Get the response
    response = http.responseText
    
    ' Output the response to a cell
    Sheets("Sheet1").Range("A1").Value = response
    
    ' Clean up
    Set http = Nothing
End Sub

Succesful query in Postman