调用OpenAI API遇JSON解析错误（状态码400）求解决方案

问题现象 #

• 单行prompt调用OpenAI API正常；传入包含员工薪资表格的多段prompt时，触发400错误，提示无法解析JSON请求体。

错误详情 #

API调用设置出现问题，返回状态码400，原始响应信息为：

{"error": {"message": "我们无法解析您请求的JSON请求体。（提示：这可能意味着您未正确使用HTTP库。OpenAI API期望接收JSON格式的请求体，但您发送的内容不是有效的JSON。如果您无法解决此问题，请发送邮件至support@openai.com并附上相关代码以寻求帮助。）", "type": "invalid_request_error", "param": null, "code": null }}

相关代码与触发Prompt #

常量定义 #

Constants for API endpoint and request properties
Const API_ENDPOINT As String = "https://api.openai.com/v1/completions"
Const MODEL As String = "text-davinci-003"
Const MAX_TOKENS As String = "1024"
Const TEMPERATURE As String = "0.5"

Prompt处理逻辑 #

' Get the prompt
Dim prompt As String
80        prompt = ActiveCell.Value

' Check if there is anything in the selected cell
90        If Trim(prompt) <> "" Then
    ' Clean prompt to avoid parsing error in JSON payload
100           prompt = CleanJSONString(prompt)
110       Else
120           MsgBox "Please enter some text in the selected cell before executing the macro", vbCritical, "Empty Input"
130           Application.ScreenUpdating = True
140           Exit Sub
150       End If

错误提示代码 #

Else
440           MsgBox "Request failed with status " & httpRequest.Status & vbCrLf & vbCrLf & "ERROR MESSAGE:" & vbCrLf & httpRequest.responseText, vbCritical, "OpenAI Request Failed"
450       End If

触发错误的Prompt内容 #

Analyse below data and find Employee Name having highest salary
Employee Name   Salary  Position    Joining Date
Jack    20303   Developer   01-Jan-22
Tinus   32444   Senior Consultant   02-Jan-22
Dave    57644   Manager 03-Jan-22
Manika  9665    Assistant   04-Jan-22
Sumit   23443   Analyst 05-Jan-22

解决方案 #

1. 完善CleanJSONString函数，覆盖所有JSON特殊字符转义 #

原函数未完全处理多段文本中的换行、制表符等特殊字符，替换为以下实现：

Function CleanJSONString(inputStr As String) As String
    Dim cleanedStr As String
    cleanedStr = inputStr
    
    ' 转义JSON核心特殊字符
    cleanedStr = Replace(cleanedStr, """", "\""")
    cleanedStr = Replace(cleanedStr, "\", "\\")
    
    ' 统一换行符为JSON标准的\n
    cleanedStr = Replace(cleanedStr, vbCrLf, "\n")
    cleanedStr = Replace(cleanedStr, vbCr, "\n")
    cleanedStr = Replace(cleanedStr, vbLf, "\n")
    
    ' 转义制表符为\t
    cleanedStr = Replace(cleanedStr, vbTab, "\t")
    
    ' 移除无效控制字符（保留制表、换行、回车）
    Dim i As Integer
    For i = 0 To 31
        If i <> 9 And i <> 10 And i <> 13 Then
            cleanedStr = Replace(cleanedStr, Chr(i), "")
        End If
    Next i
    
    CleanJSONString = cleanedStr
End Function

2. 修正请求体构建逻辑，避免类型错误 #

MAX_TOKENS和TEMPERATURE是数值类型，无需加双引号，先修改常量定义：

Const MAX_TOKENS As Integer = 1024
Const TEMPERATURE As Double = 0.5

再正确拼接JSON请求体：

Dim jsonPayload As String
jsonPayload = "{""model"": """ & MODEL & """, ""prompt"": """ & prompt & """, ""max_tokens"": " & MAX_TOKENS & ", ""temperature"": " & TEMPERATURE & "}"

3. 提前验证JSON格式 #

发送请求前，输出请求体到调试窗口，确认格式有效性：

Debug.Print jsonPayload

4. 使用专业JSON库（推荐） #

避免手动拼接JSON的错误，引入VBA-JSON库构建请求体：

' 需先导入VBA-JSON库
Dim jsonObj As Object
Set jsonObj = CreateObject("Scripting.Dictionary")
jsonObj("model") = MODEL
jsonObj("prompt") = prompt
jsonObj("max_tokens") = MAX_TOKENS
jsonObj("temperature") = TEMPERATURE

Dim jsonPayload As String
jsonPayload = JsonConverter.ConvertToJson(jsonObj)

内容的提问来源于stack exchange，提问作者Sunil Singh Sikarwar