Calcular a distância ou tempo de viagem entre dois endereços usando fórmulas do Google Maps no Google Sheets é, na minha opinião, uma das funções mais legais que podem ser usadas no Apps Script. Isso tudo sem depender de criar ou pagar por uma chave de API, já que o Apps Script já conta com uma integração nativa do Google Maps.
Como eu trabalho em uma empresa de logística, essa possibilidade é e já foi útil em diversas situações. Mas a utilidade se estende as outras áreas de planejamento de rotas em delivery de restaurantes, precificação de serviços de guincho e até para tarefas pessoais de cálculo de deslocamentos em viagens, por exemplo.
Vamos detalhar abaixo como adicionar essas fórmulas na sua planilha e mostrar como funcionam na prática.
O que é e para que serve o Apps Script?
Por padrão, o Google Sheets tem funções nativas, como é o caso do PROCV, o SE, GOOGLEFINANCE, o SOMASE, etc. Em casos de uso mais avançados, você pode ter que criar uma função personalizada que precise se conectar a um serviço de terceiro, como o Google Maps nesse exemplo.
É aí que o Apps Script entra. O GAS (sigla para Google Apps Script) é uma plataforma de desenvolvimento baseada em JavaScript, que permite entre outras coisas, automatizar tarefas e estender funcionalidades do Google Sheets.
Apps Script: O que é e como funciona o VBA do Google Sheets
No caso, temos duas funções: GOOGLEMAPS_DISTANCIA e GOOGLEMAPS_DURACAO, onde uma calcula a distância em km entre dois endereços e outra o tempo de viagem. Ambas utilizam a API do Google Maps e armazenam a resposta em cache, para otimizar o tempo de resposta de consultas repetidas e reduzir a quota de uso.
Fórmulas do Google Maps no Google Sheets: Configurando o Apps Script
- Acesse o Apps Script: entre na sua planilha, no menu superior, vá em Extensões > Apps Script
- Cole o código abaixo no seu editor, salve e execute para conceder qualquer permissão necessária.
/**
* Calcula a distância entre dois locais usando Google Maps.
* =GOOGLEMAPS_DISTANCIA("Rua Augusta, 1500, São Paulo, SP", "Av. Paulista, 1000, São Paulo, SP", "driving")
*
* @param {String} origem O endereço do ponto de partida
* @param {String} destino O endereço do destino
* @param {String} modo O modo de viagem (driving, walking, bicycling ou transit)
* @return {String} A distância em milhas
* @customFunction
*/
const GOOGLEMAPS_DISTANCIA = (origem, destino, modo = 'driving') => {
const cache = CacheService.getScriptCache();
const cacheKey = `distancia_${origem}_${destino}_${modo}`;
let distancia = cache.get(cacheKey);
if (!distancia) {
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origem)
.setDestination(destino)
.setMode(modo)
.getDirections();
if (!data) {
throw new Error('Nenhuma rota encontrada!');
}
const { legs: [{ distance: { text } } = {}] = [] } = data;
distancia = text;
cache.put(cacheKey, distancia, 21600); // Cache de 21600 segundos ou 6 horas
}
return distancia;
};
/**
* Calcula o tempo de viagem entre dois locais usando Google Maps.
* =GOOGLEMAPS_DURACAO("Rua Augusta, 1500, São Paulo, SP", "Av. Paulista, 1000, São Paulo, SP", "driving")
*
* @param {String} origem O endereço do ponto de partida
* @param {String} destino O endereço do destino
* @param {String} modo O modo de viagem (driving, walking, bicycling ou transit)
* @return {String} O tempo em minutos
* @customFunction
*/
const GOOGLEMAPS_DURACAO = (origem, destino, modo = 'driving') => {
const cache = CacheService.getScriptCache();
const cacheKey = `duracao_${origem}_${destino}_${modo}`;
let tempo = cache.get(cacheKey);
if (!tempo) {
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origem)
.setDestination(destino)
.setMode(modo)
.getDirections();
if (!data) {
throw new Error('Nenhuma rota encontrada!');
}
const { legs: [{ duration: { text } } = {}] = [] } = data;
tempo = text;
cache.put(cacheKey, tempo, 21600); // Cache de 21600 segundos ou 6 horas
}
return tempo;
};Como usar a fórmula =GOOGLEMAPS_DISTANCIA()
A sintaxe da função GOOGLEMAPS_DISTANCIA é muito simples, conta com 3 argumentos e os 2 primeiros são obrigatórios:
=GOOGLEMAPS_DISTANCIA(origem; destino; modo)- Origem: endereço de partida (exemplo: Rua XPTO, 333 – São Paulo/SP)
- Destino: endereço de chegada (exemplo: Rua XYZ, 123 – São Paulo/SP)
- Modo: Modo de viagem, assim como se escolhe no Google Maps – de carro, a pé, de moto, de bike
- O modo deve estar escrito em inglês, conforme instruções da função:
- driving: modo dirigir (esse é o mais utilizado e o padrão, caso nenhum outro seja definido)
- walking: modo a pé
- bicycling: modo de bike
- transit: modo de transporte público (para algumas cidades pode não estar disponível)
Exemplo utilizado:
=GOOGLEMAPS_DISTANCIA(B2;C2;"driving")Como usar a fórmula =GOOGLEMAPS_DURACAO()
A função GOOGLEMAPS_DURACAO irá calcular o tempo de deslocamento em minutos entre em um endereço e outro, no modo que você escolher. Os parâmetros são os mesmos da função anterior, sendo o modo padrão definido para o driving.
Exemplo utilizado:
=GOOGLEMAPS_DURACAO(B2;C2;"driving")O tempo de resposta do cálculo de distância é muito rápido, isso considerando que é uma consulta em tempo real no Google Maps.
Note que fazendo a mesma consulta diretamente no Maps, a distância é exatamente igual e o tempo de deslocamento ficou um pouco diferente – não deve ser tarefa fácil prever o trânsito em SP 😀 – ou seja, é um dado muito confiável que reflete o estado do trânsito em tempo real.
Faça uma cópia da Planilha
Dicas de ouro
Bem, esperamos que você faça o uso consciente dessas funções personalizadas, já que esse serviço é uma cortesia da Google – o recurso de cálculo de distância necessita de processamento computacional para definir as rotas e efetuar o cálculo propriamente dito.
Por isso, definimos uma regra de cache para preservar sua cota diária de uso. Esse recurso apesar de estar disponível gratuitamente, possui uma limitação diária de em média 5.000 requisições. Dependendo do seu caso de uso, pode atender perfeitamente.
Do contrário, pode ser necessário um plano pago da API do Google Maps – apesar de existirem outras opções gratuitas como o Mapbox que oferece 100.000 requisições/mês e openrouteservice que é de código aberto.
Esperamos que tenha curtido essa solução. No futuro vamos publicar uma ferramenta no Sheets onde será possível planejar rotas de serviços de delivery, onde você lança múltiplos endereços que o entregador precisa ir e será montado uma rota otimizada no Google Maps com a ordem de cada parada, que será baseada em uma opção selecionável: considerando a menor distância em km ou minutos – o que pode contribuir significativamente para a redução de gastos e consumo de combustível.
Essas funções foram desenvolvidas pelo Amit Agarwal, nós apenas traduzimos para o português e aperfeiçoamos o cache.
No mais, para ficar por dentro das novidades e tirar dúvidas, entre na Comunidados: o nosso grupo 100% gratuito no WhatsApp.
