在Excel上使用Google Maps距离矩阵API减少API调用次数

Question

在Excel上使用Google Maps距离矩阵API减少API调用次数

3

我正在创建一个Excel电子表格，其中包含8个不同位置和它们之间距离的网格，这些距离是从Google Maps Distance Matrix API中获取的。这些位置是从一个表格中输入的，并且会经常更改。

我目前使用的VBA代码是：

   'Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
    Dim firstVal As String, secondVal As String, lastVal As String
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "+UK&destinations="
    lastVal = "+UK&mode=car&language=en&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal)
    Exit Function
ErrorHandl:
    GetDistance = -1
End Function

然后，我使用简单的函数在电子表格中调用它：

=GetDistance($D$14,B15)

这个脚本运行得很好，但这意味着每次电子表格加载和更改任何位置时都会进行56个API调用，因此我很快就会达到2500个API调用限制。

有没有办法使函数仅在特定时间拉取数据（例如在点击按钮时），或者以更少的API调用获取相同的数据？

- baelaelael

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dirk Reichel · Accepted Answer

通过添加一个按钮（只有在按钮被按下时才刷新）和一个收集所有已获取值的集合，您应该能够减少调用次数...

Option Explicit

Public gotRanges As New Collection 'the collection which holds all the data
Public needRef As Range 'the ranges which need to be recalculated
Public refMe As Boolean 'if true GetDistance will update if not in collection

Public Function GetDistance(start As String, dest As String)
  Dim firstVal As String, secondVal As String, lastVal As String, URL As String, tmpVal As String
  Dim runner As Variant, objHTTP, regex, matches
  If gotRanges.Count > 0 Then
    For Each runner In gotRanges
      If runner(0) = start And runner(1) = dest Then
        GetDistance = runner(2)
        Exit Function
      End If
    Next
  End If
  If refMe Then
    firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    secondVal = "+UK&destinations="
    lastVal = "+UK&mode=car&language=en&sensor=false"
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
    Set matches = regex.Execute(objHTTP.responseText)
    tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
    GetDistance = CDbl(tmpVal)
    gotRanges.Add Array(start, dest, GetDistance)
    Exit Function
  Else
    If needRef Is Nothing Then
      Set needRef = Application.Caller
    Else
      Set needRef = Union(needRef, Application.Caller)
    End If
  End If
ErrorHandl:
  GetDistance = -1
End Function

Public Sub theButtonSub() 'call this to update the actual settings
  Dim runner As Variant
  refMe = True
  If Not needRef Is Nothing Then
    For Each runner In needRef
      runner.Offset.Formula = runner.Formula
    Next
  End If
  Set needRef = Nothing
  refMe = False
End Sub

如果你有a、b和c三个元素（这将加载6次），但是你把它们改成c、a和b，它们就不会再重新加载了（如果你明白我的意思...）

如果还有问题，请随时问我 :)