Neste artigo, criaremos uma função do Excel para calcular a distância entre dois endereços usando a API de rotas do Google Maps. Isso permitirá que você obtenha o tempo de viagem entre os dois locais.
O formato da função será o seguinte: =TRAVELTIME(origin, destination, api_key), =TRAVELDISTANCE(origin, destination, apikey).
A origem e o destino serão strings e podem ser um endereço exato ou o nome de um local.
Para usar a função, é necessária uma chave de API. A página "Primeiros passos" pode ajudá-lo com isso: https://bit.ly/GoogleMapsGettingStarted. Crie um novo projeto e verifique se a API Directions foi adicionada.
No final deste arquivo baixe a planilha com os códigos já implementados nela. Pronta para você testar as funções VBA com suas cidades de referência.
Passo 1: Crie um novo arquivo de macro e adicione VBA-JSON
Como a API de rotas do Google Maps é uma API JSON, usaremos VBA-JSON para facilitar o uso dos resultados da solicitação da web. Você pode baixar a versão mais recente aqui: https://github.com/VBA-tools/VBA-JSON/releases . Baixe e extraia o arquivo zip. Em seguida, abra seu arquivo de macro. Abra o Editor do Visual Basic (Alt + F11).
Abra o editor do Visual Basic. Se você não tiver esse botão, certifique-se de que a guia “Desenvolvedor” esteja habilitada. Clique com o botão direito do mouse na faixa de opções, vá para “Personalizar a faixa de opções…” e marque Desenvolvedor nas guias principais.
Para importar o arquivo VBA-JSON, vá em Arquivo > Importar arquivo… (Ctrl + M). Selecione JsonConverter.bas. Um módulo JsonConverter aparecerá na barra lateral.
Em seguida, verifique se as referências apropriadas estão habilitadas. Vá em Ferramentas > Referências… Além das referências já selecionadas, marque “Microsoft Scripting Runtime” (para suporte de dicionário necessário para VBA-JSON) e “Microsoft WinHTTP Services, versão 5.1” (para fazer a solicitação HTTP para a API) . Se precisar de suporte para Excel para Mac, precisará instalar o VBA-Dictionary do autor do VBA-JSON. Mais detalhes podem ser encontrados na parte inferior da página inicial do projeto: https://github.com/VBA-tools/VBA-JSON.
Etapa 2: criar as funções
Com as referências configuradas, podemos agora escrever o código da função. O código é relativamente simples. Ele simplesmente pega os três parâmetros e os formata em uma solicitação da web. A resposta da solicitação da web é então analisada por VBA-JSON e a variável relevante retornada. Observe que a solicitação pode retornar várias rotas, mas a função simplesmente retorna a hora da primeira rota. O modo padrão é dirigir, mas consulte a documentação da Directions API para obter informações sobre outros modos e ajuste a variável strURL de acordo.
Para inserir o código, crie um novo módulo com Inserir > Módulo. Em seguida cole o seguinte código:
Function TEMPODEVIAGEM(origin, destination, apikey)
' Retorna o número de segundos que levaria para chegar de um lugar ao outro.
Dim strUrl As String
Dim response As String
Dim seconds As Integer
Dim leg As Dictionary
Dim parsed As Dictionary
Let 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
Let response = httpReq.ResponseText
Set parsed = JsonConverter.ParseJson(response)
For Each leg In parsed("routes")(1)("legs")
Let seconds = seconds + leg("duration")("value")
Next leg
Let TEMPODEVIAGEM = seconds
End Function
Function DISTANCIADAVIAGEM(origin, destination, apikey)
' Retorna o número de segundos necessários para se chegar de um lugar a outro.
Dim strUrl As String
Dim response As String
Dim meters As Integer
Dim leg As Dictionary
Dim parsed As Dictionary
Let 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
Let response = httpReq.ResponseText
Set parsed = JsonConverter.ParseJson(response)
For Each leg In parsed("routes")(1)("legs")
Let meters = meters + leg("distance")("value")
Next leg
Let DISTANCIADAVIAGEM = meters
End Function
Salve o arquivo. Agora você deve ser capaz de usar as funções de dentro do Excel. Coloque sua chave de API na célula A1 e tente o seguinte: =TEMPODEVIAGEM("Avenida Ana Costa 500", "Santos, SP", A1). Isso retorna um tempo de viagem de cerca de 435 segundos. Se você quiser que isso seja exibido em minutos e segundos, tente esta função: =ARREDMULTB.MAT(A8/60)&" minutes "&MOD(A8, 60)&" seconds"onde A8 é a célula com o tempo de viagem em segundos. Isso imprime um útil “7 minutos e 15 segundos” para o exemplo de 24 Sussex. Também podemos encontrar a distância. Tente o seguinte: =DISTANCIADAVIAGEM("Avenida Carangola", "Uberaba, MG", A1). Ele retorna uma distância de 2667 metros. Converta para quilômetros com isto: =ARREDONDAR.PARA.CIMA(A9/1000; 1)&" km".
Observação: a API de rotas do Google Maps sempre retorna as distâncias em metros. Converta para KM ou Milhas como desejar. Isso pode ser feito no Excel ou modificando as funções no VBA.
É isso! Agora você deve ter uma função de tempo de viagem funcionando. Tudo o que você precisa agora é uma lista de endereços para usá-lo. Se você quiser obter uma lista da Web ou de um arquivo JSON local, consulte Importar dados JSON no Excel 2016 ou 2019 ou Office 365 usando uma consulta Get & Transform.
Comente e compartilhe este artigo!
Contate-nos pelo What'sApp para avaliarmos seus projetos
brazilsalesforceeffectiveness@gmail.com
Leia também:
