Função do Excel para calcular o tempo e a distância da viagem usando a API do Google Maps com VBA

Função do Excel para calcular o tempo e a distância da viagem usando a API do Google Maps com VBA

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.

 DOWNLOAD DA PLANILHA 


Comente e compartilhe este artigo!


Contate-nos pelo What'sApp para avaliarmos seus projetos 

brazilsalesforceeffectiveness@gmail.com




 DOWNLOAD DOS LIVROS 


Leia também:

eBook: Série DONUT PROJECT 2015: Projetos e Códigos de Visual Basic for Applications - Autor: André Luiz Bernardes  eBook: Série Top 10 Funções: Top 10 Funções VBA para o Microsoft Excel - Autor: André Luiz Bernardes

eBook: Série Funções Poderosas: 13 Funções Poderosas no MS Excel - Autor: André Luiz Bernardes  eBook: Série Visual Basic For Application: Criando Logs de acesso: Dicas e Códigos de Visual Basic for Applications - Autor: André Luiz Bernardes

eBook: Série VBA Tips: Rastrei seus Dashboards, Scorecards, Reports, Relatórios, Planilhas e Aplicações - Dicas e Códigos - Autor: André Luiz Bernardes  eBook: Série Data Science: Big Data, Como? - Autor: André Luiz Bernardes

eBook: Série Smarter Analytic: 5 Previsões de Big Data - Autor: André Luiz Bernardes

Nenhum comentário:

Postar um comentário

diHITT - Notícias