如何在Excel VBA中传递参数发送GraphQL查询至Factbird API

问题根源 #

你遇到的Unexpected number in JSON at position 67错误，核心原因是VBA拼接GraphQL查询和JSON请求时，引号转义不完整，导致最终发送的JSON格式无效。直接拼接字符串极易出现嵌套引号的转义失误，尤其是复杂查询场景。

解决方案1：使用GraphQL变量传递参数（推荐） #

GraphQL支持通过变量传递参数，能彻底规避字符串拼接的转义问题，代码也更易维护。修改后的VBA代码如下：

Sub SendAPIQueryWithVariables()
    Dim http As Object
    Dim url As String
    Dim query As String
    Dim apiKey As String
    Dim payload As String
    Dim response As String
    Dim batchNumber As String
    
    ' 配置基础信息
    url = "https://api.cloud.factbird.com"
    apiKey = "MY-API-KEY"
    batchNumber = "38276"

    ' 定义带变量的GraphQL查询
    query = "query Company($batchNum: String!) { lines { batches(filter: { batchNumber: $batchNum }) { 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 } } } } } } } }"
    
    ' 构建包含查询和变量的JSON payload
    ' 用Chr(34)代替硬编码双引号，提升可读性
    payload = "{" & _
        Chr(34) & "query" & Chr(34) & ": " & Chr(34) & Replace(query, Chr(34), "\" & Chr(34)) & Chr(34) & ", " & _
        Chr(34) & "variables" & Chr(34) & ": {" & Chr(34) & "batchNum" & Chr(34) & ": " & Chr(34) & batchNumber & Chr(34) & "}" & _
    "}"
    
    ' 发送请求
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    http.send payload
    
    ' 获取响应并输出
    response = http.responseText
    Sheets("Sheet1").Range("A1").Value = response
    
    ' 清理对象
    Set http = Nothing
End Sub

解决方案2：修复原代码的引号转义 #

如果坚持直接拼接查询字符串，需确保所有双引号正确转义为JSON要求的\"（VBA中通过Replace函数批量处理）：

修改原代码中发送请求的部分：

' 替换原有的send语句
Dim escapedQuery As String
escapedQuery = Replace(query, """", "\""")
http.send "{""query"": """ & escapedQuery & """}"

这种方式仅适用于简单查询，复杂场景下仍推荐使用GraphQL变量方案。

关键说明 #

• 使用GraphQL变量时，需在查询开头定义变量类型（如$batchNum: String!），再在查询逻辑中引用该变量
• 构建JSON时，必须保证所有字符串的双引号都完成转义，否则会触发格式错误

内容的提问来源于stack exchange，提问作者Morten Jepsen