如何修改Excel的Google Maps API函数以识别路线是否包含轮渡

核心思路 #

Google Maps Directions API的返回数据中，轮渡信息会体现在路线步骤（steps）里：

• 自驾路线中，轮渡步骤的maneuver字段会包含"ferry"关键词
• 公共交通路线中，轮渡的transit_details.line.vehicle.type字段值为"FERRY"

我们需要遍历所有路线步骤，一旦检测到轮渡标识，直接返回"Ferry"；未检测到则继续计算行驶距离。

修改后的完整代码 #

Function TRAVELDISTANCE(origin As String, destination As String, apikey As String) As Variant
    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 = JsonConverter.ParseJson(response)
    
    ' 检测是否存在轮渡
    Dim route As Dictionary
    Dim leg As Dictionary
    Dim step As Dictionary
    Dim hasFerry As Boolean
    hasFerry = False
    
    For Each route In parsed("routes")
        For Each leg In route("legs")
            For Each step In leg("steps")
                ' 检测自驾路线中的轮渡操作
                If step.Exists("maneuver") Then
                    If InStr(LCase(step("maneuver")), "ferry") > 0 Then
                        hasFerry = True
                        GoTo CheckComplete
                    End If
                End If
                ' 检测公共交通中的轮渡类型
                If step.Exists("travel_mode") Then
                    If LCase(step("travel_mode")) = "transit" Then
                        If step("transit_details")("line")("vehicle")("type") = "FERRY" Then
                            hasFerry = True
                            GoTo CheckComplete
                        End If
                    End If
                End If
            Next step
        Next leg
    Next route
    
CheckComplete:
    If hasFerry Then
        TRAVELDISTANCE = "Ferry"
        Exit Function
    End If
    
    ' 未检测到轮渡时计算行驶距离
    Dim meters As Long
    meters = 0
    For Each leg In parsed("routes")(1)("legs")
        meters = meters + leg("distance")("value")
    Next leg
 
    TRAVELDISTANCE = meters
End Function

关键修改点说明 #

• 添加hasFerry变量标记是否检测到轮渡，找到后直接终止检测流程
• 嵌套遍历routes→legs→steps，覆盖所有路线环节
• 同时兼容自驾、公共交通两种场景的轮渡检测逻辑
• 补全了函数完整结构（包括函数声明、meters变量声明）

内容的提问来源于stack exchange，提问作者Michael Thøgersen