在 Excel 上使用 Google Maps Distance Matrix API 以减少 API 调用 [英] Using Google Maps Distance Matrix API on Excel with less API calls

问题描述

我正在创建的 excel 电子表格的一部分是一个由 8 个不同位置组成的网格，它们之间的距离是从 Google 地图距离矩阵 API 中提取的.位置是从表格中输入的，并且会定期更改.

Part of an excel spreadsheet I'm creating is a grid of 8 different locations and the distance between them pulled from the Google Maps Distance Matrix API. The locations are entered from a table and will be changed regularly.

我目前使用的 VBA 代码是:

The VBA code I'm currently using is:

   '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

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

I then call it in the spreadsheet using the simple function:

=GetDistance($D$14,B15)

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

This script works well but it does mean that I'm doing 56 API calls each time the spreadsheet loads and each time I change any of the locations, and hence I'm hitting the 2500 API call limit quite quickly.

有没有办法让函数只在特定时间(例如单击按钮)提取数据，或者只是在更少的 API 调用中获取相同的数据?

Is there a way of making the function only pull data at a specific time, (at the click of a button, for example), or simply getting the same data in less API calls?

推荐答案

通过添加一个按钮(仅在按下时才刷新)和一个包含您目前获得的所有值的集合，您应该能够减少电话...

By adding a button (to only refresh if it is pressed) and a collection holding all values you got so far, you should be able to decrease the amounds of calls...

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(如果您明白我的意思...

having a, b and c (which would load 6 times) will not load again if you change them to c, a and b (if you get what i mean...

如果你还有问题，尽管问:)

if you still have questions, just ask :)

这篇关于在 Excel 上使用 Google Maps Distance Matrix API 以减少 API 调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！