在Excel上使用谷歌地图距离矩阵API和更少的API调用 [英] Using Google Maps Distance Matrix API on Excel with less API calls

问题描述

我创建的Excel电子表格的一部分是由8个不同位置组成的网格，它们之间的距离是从Google Maps Distance Matrix API中提取的。这些位置是从表格中输入的，并且会定期更改。

我目前使用的VBA代码是：

 '计算两个地址之间的Google Maps距离
 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&替换（start，，+）& secondVal&替换（dest，，+）& lastVal 
 objHTTP.OpenGET，URL，False 
 objHTTP.setRequestHeaderUser-Agent，Mozilla / 4.0（compatible; MSIE 6.0; Windows NT 5.0）
 objHTTP.send （）
如果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）
退出函数
 ErrorHandl：
 GetDistance = -1 
 End Function 
  

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

  = GetDistance（$ D $ 14，B15）
  

这个脚本运行良好，但它确实意味着每次电子表格加载时我都会执行56次API调用，并且每次更改任何位置时，我就是h很快就会出现2500 API调用限制。

有没有一种方法可以使函数仅在特定时间提取数据（例如点击一个按钮，例如），或简单地在较少的API调用中获得相同的数据？

解决方案

通过添加一个按钮）和一个集合，其中包含您到目前为止获得的所有值，您应该能够减少调用的方法...

  Option显式
 
公共gotRanges作为新集合'保存所有数据的集合
公共needRef作为范围'需要重新计算的范围
公共refMe作为布尔'如果为真GetDistance将更新，如果不在集合中
 
 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，match 
如果gotRanges.Count> 0然后
对于每个运行者在gotRanges 
如果runner（0）= start并且runner（1）= dest然后
 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&替换（start，，+）& secondVal&替换（dest，，+）& lastVal 
 objHTTP.OpenGET，URL，False 
 objHTTP.setRequestHeaderUser-Agent，Mozilla / 4.0（compatible; MSIE 6.0; Windows NT 5.0）
 objHTTP.send （）
如果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 
否则
如果needRef是Nothing然后
 Set needRef = Application.Caller 
其他
 Set needRef = Union（needRef，Application.Caller）
 End If 
 End If 
 ErrorHandl：
 GetDistance = -1 
 End Function 
 
 Public Sub theButtonSub（）'调用它来更新实际设置
昏暗的奔跑呃作为变种
 refMe =真
如果不需要参考是没有然后
对于每个参赛选手需要参考
 runner.Offset.Formula = runner.Formula 
下一个
 End If 
 Set needRef = Nothing 
 refMe = False 
 End Sub 
  

如果你将它们改为c，a和b（如果你明白我的意思......），那么有a，b和c（它将加载6次）不会再次加载。

如果您仍有疑问，只需询问：）

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.

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)

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.

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

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上使用谷歌地图距离矩阵API和更少的API调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！