Google Maps Directions API在Excel自定义函数中突然失效问题排查

问题详情 #

• 此前通过自定义VBA函数TRAVELTIME和TRAVELDISTANCE调用Google Maps Directions API，用于计算两个邮编间的距离与出行时间，今日突然全部返回#VALUE错误。
• 观察到谷歌地图搜索邮编时不再显示定位针，仅高亮区域，怀疑与此相关；且历史正常运行的版本也报错，推测是API逻辑或后台JSON响应结构变化导致。

原TRAVELTIME函数代码 #

Function TRAVELTIME(origin, destination, apikey)

Dim strUrl As String
strUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" & origin & "&destination=" & destination & "&key=" & apikey
 
Set httpReq = CreateObject("MSXML2.XMLHTTP")
With httpReq
     .Open "GET", strUrl, False
     .Send
End With
 
Dim response As String
response = httpReq.ResponseText
 
Dim parsed As Dictionary
Set parsed = Module2.ParseJson(response)
Dim seconds As Integer
 
Dim leg As Dictionary
 
For Each leg In parsed("routes")(1)("legs")
    seconds = seconds + leg("duration")("value")
Next leg
 
TRAVELTIME = seconds
End Function

排查与修复方案 #

1. 先排查API响应错误

原代码未处理API返回的错误信息，直接解析JSON，一旦API返回错误（比如邮编解析失败、密钥失效、配额耗尽），就会触发#VALUE错误。需要先添加状态判断逻辑：

• 获取响应后，先解析status字段，判断是否为"OK"，非OK状态则返回对应错误提示。

2. 修正JSON索引问题

Google Maps API的routes和legs数组从索引0开始，原代码中parsed("routes")(1)会取第二个元素，若只有一条路线就会报错，需改为parsed("routes")(0)。

3. 适配邮编解析逻辑变化

谷歌地图对邮编的解析逻辑可能调整，直接传入纯邮编可能无法生成精确坐标，导致API返回"ZERO_RESULTS"。可尝试给邮编添加国家前缀（比如美国邮编加"US "），确保API准确定位。

修正后的TRAVELTIME函数代码 #

Function TRAVELTIME(origin, destination, apikey)
    Dim strUrl As String
    ' 可选：根据实际区域添加国家前缀，示例为美国邮编
    origin = "US " & origin
    destination = "US " & destination
    
    strUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" & _
             URLEncode(origin) & "&destination=" & URLEncode(destination) & "&key=" & apikey
 
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
         .Open "GET", strUrl, False
         .Send
    End With
 
    Dim response As String
    response = httpReq.ResponseText
 
    Dim parsed As Dictionary
    Set parsed = Module2.ParseJson(response)
    
    ' 检查API返回状态
    Select Case parsed("status")
        Case "OK"
            Dim seconds As Integer
            Dim leg As Dictionary
            
            ' 修正数组索引为0
            For Each leg In parsed("routes")(0)("legs")
                seconds = seconds + leg("duration")("value")
            Next leg
            TRAVELTIME = seconds
        Case Else
            ' 返回错误状态便于排查
            TRAVELTIME = "API错误: " & parsed("status")
    End Select
End Function

' 辅助函数：URL编码，避免特殊字符破坏请求格式
Function URLEncode(str As String) As String
    Dim ScriptEngine As Object
    Set ScriptEngine = CreateObject("ScriptControl")
    ScriptEngine.Language = "JScript"
    URLEncode = ScriptEngine.CodeObject.encodeURIComponent(str)
    Set ScriptEngine = Nothing
End Function

额外排查步骤 #

• 检查API密钥配额：登录Google Cloud控制台，查看Maps Directions API的使用情况，确认是否超出免费额度或配额限制。
• 直接测试API请求：在浏览器中拼接完整请求URL（替换参数），查看返回的JSON内容，确认是否有明确错误提示。
• 验证邮编格式：确保输入的邮编是目标区域的有效格式，避免格式错误导致API无法解析。

内容的提问来源于stack exchange，提问作者Edward Tillen