Nesse passo a passo, além de aprender um novo método para calcular distância entre endereços no Google Sheets, você vai aprender sobre APIs – como integrar um serviço de terceiros a sua planilha do Sheets.
Para além da API do Mapbox, que é um serviço semelhante ao Google Maps, aprender sobre consumo de APIs REST pode ser extremamente útil para o seu trabalho, pois permite a integração de outros aplicativos aos seus dados.
Lembrando que já ensinamos a calcular distância com Apps Script usando o próprio serviço do Google Maps, mas o limite diário é de cerca de 5.000 cálculos. Com o Mapbox, você não terá uma restrição diária, mas sim mensal.
Vamos ao que interessa!
O que é uma API?
Uma API (ou Application Programming Interface) pode parecer algo técnico, mas em resumo, é uma forma de dois sistemas se comunicarem e trocarem informações entre si. Pense nela como um “garçom” que leva o pedido de um cliente em um restaurante (você, usando uma planilha) para a cozinha (o sistema ou serviço externo) e traz de volta a resposta (os dados que você precisa).
Por exemplo, suponhamos que você queira buscar diretamente da sua planilha, como está a condição meteorológica de uma cidade naquele instante. Sua planilha irá fazer um pedido para uma API de previsão do tempo e receberá a temperatura e demais condições climáticas como resposta.
Cenário: base de dados de exemplo
Agora, nosso desafio é pegar o endereço da nossa base de clientes e calcular qual a distância deles em quilômetros (km) até a nossa loja. Isso pode ser extremamente útil para mapear o perfil dos seus clientes, fazer ações de marketing mais segmentadas, entre outras razões.
Essa é a nossa base de dados:
Perceba que temos o endereço completo do cliente e isso é essencial para que o cálculo de distância seja feito com confiança. Pois o endereço completo será convertido para coordenadas geográficas: latitude e longitude.
Esse é o endereço da nossa única loja de exemplo: Av. Roque Petroni Júnior, 1089 – Jardim das Acacias, São Paulo – SP, 04707-900
Nesse caso, a latitude e longitude desse endereço, são, respectivamente: -23.622957, -46.699424
Se você tem somente um endereço, como apenas uma loja desse exemplo, é ideal deixar essa coordenada fixada no código, para que a cota de uso do Mapbox seja utilizada somente para converter o endereço dos clientes para latitude e longitude – embora nós adotaremos uma política de cache.
Faça o seu cadastro no Mapbox
Ao se cadastrar no Mapbox, você ganha direito a usar a API de cálculo de distância gratuitamente. No entanto, existe um limite mensal de 100.000 (cem mil) cálculos. É bem provável que esse limite te atenda bem.
Você pode acompanhar a sua cota de uso diretamente no console do Mapbox:
Tome cuidado para não ultrapassar, pois uma fatura de cobrança pode ser emitida e sua API será bloqueada até que o pagamento seja feito.
Passo a passo para se cadastrar no Mapbox:
- Acesse: https://account.mapbox.com/auth/signup/
- Selecione o tipo de conta (Business ou Individual)
- Preencha todos os dados e confirme o seu e-mail.
- Faça login no console e pegue seu token de API
- ATENÇÃO: mantenha o token em segredo, ele é equivalente ao seu e-mail e senha para acessar os serviços do Mapbox. Qualquer pessoa que tenha o seu token poderá consumir o seu limite mensal de uso.
Copie o seu token, pois usaremos ele na função do Apps Script para poder efetuar os cálculos de distância.
Documentação da API: coordenadas e cálculo de distância
Antes de efetuar o cálculo de distância, precisamos converter o endereço do cliente (Rua XYZ, SP) para coordenadas (latitude e longitude). Para isso, existe um endpoint na API do Mapbox para realizar essa busca.
/search/geocode/v6/forward?q={endereço}&limit=1&access_token={MAPBOX_TOKEN}
Um endpoint é um dos conceitos mais importantes quando falamos de APIs. Em termos simples, um endpoint é como o endereço (link) específico de onde você vai buscar ou enviar informações usando a API. No caso do exemplo do restaurante que usamos lá no início, pense em algo como:
- Você quer uma pizza: você escolhe o endpoint
/pizza. - Você quer uma sobremesa: o endpoint seria
/sobremesa.
No caso do Mapbox, você pode conferir mais detalhes sobre esse endpoint de converter endereço em coordenadas na documentação: https://docs.mapbox.com/api/search/geocoding
Construindo a função Apps Script para converter endereço em coordenadas
Antes de tudo, precisamos definir uma variável para armazenar a chave de API.
Na primeira linha do seu script, adicione:
const MAPBOX_TOKEN = 'pk.eyXYZ...'Seguiremos esses preceitos para construir a função =OBTER_COORDENADAS(endereco; tipo):
- essa função irá receber 1 endereço e o tipo de informação a ser retornado:
- LATITUDE ou LONGITUDE, ou AMBOS.
- uma política de cache será usada para poupar o uso dos limites da sua API. Isso é interessante para não consultar a API toda vez que buscar um mesmo endereço dentro de um curto intervalo (6 horas).
- A partir de então, chamaremos a API do Mapbox e aguardaremos uma resposta contendo as coordenadas.
Isso se traduz nessa função do Apps Script:
/**
* Obtém as coordenadas de um endereço utilizando a API Mapbox.
* @param {string} endereco - Endereço para o qual as coordenadas serão obtidas.
* @param {string} [tipo] - Tipo de retorno: 'LATITUDE', 'LONGITUDE' ou 'AMBOS'. Padrão é 'AMBOS'.
* @returns {string|number|Array} Retorna latitude, longitude ou ambos.
* @customfunction
*/
function OBTER_COORDENADAS(endereco, tipo = 'AMBOS') {
const cache = CacheService.getScriptCache(); // Obtém o cache do script
const cacheKey = `coords_${endereco}`;
const cachedData = cache.get(cacheKey);
let coordenadas;
if (cachedData) {
// Se os dados estiverem no cache, usa os dados armazenados
coordenadas = JSON.parse(cachedData);
} else {
const url = `https://api.mapbox.com/search/geocode/v6/forward?q=${encodeURIComponent(endereco)}&limit=1&access_token=${MAPBOX_TOKEN}`;
const response = UrlFetchApp.fetch(url);
const dados = JSON.parse(response.getContentText());
if (dados.features && dados.features.length > 0) {
const longitude = dados.features[0].properties.coordinates.longitude;
const latitude = dados.features[0].properties.coordinates.latitude;
coordenadas = { latitude, longitude };
// Armazena no cache por 6 horas
cache.put(cacheKey, JSON.stringify(coordenadas), 21600); // 6 horas
} else {
throw new Error('Endereço não encontrado.');
}
}
// Processa o retorno com base no tipo solicitado
switch (tipo.toUpperCase()) {
case 'LATITUDE':
return coordenadas.latitude;
case 'LONGITUDE':
return coordenadas.longitude;
case 'AMBOS':
default:
// Retorna as coordenadas formatadas como string ou array para facilitar o uso
return [coordenadas.latitude, coordenadas.longitude].join(', ');
}
}Agora já temos uma função para buscar latitude e longitude de qualquer endereço no Brasil:
Calcular distância entre endereços: construindo a função no Apps Script
Novamente, precisaremos de um endpoint específico da API do Mapbox para efetuar o cálculo de distância entre um endereço (lat, lon) e outro. Para isso, existe a Directions API, sob a URL:
/directions/v5/mapbox/driving/${coordenadas1.longitude},${coordenadas1.latitude};${coordenadas2.longitude},${coordenadas2.latitude}?access_token=${MAPBOX_TOKEN}
Através desse recurso, conseguimos obter a distância entre um endereço e outro no modo driving (carro). Para conhecer mais sobre esse endpoint, inclusive sobre outros modos além do driving, acesse a documentação oficial: https://docs.mapbox.com/api/navigation/directions/
Dito isso, mãos na massa novamente! Hora de criar a função =CALCULAR_DISTANCIA().
Essa função respeitará os seguintes preceitos:
- Receberá dois argumentos: endereço de partida e endereço de chegada.
- Também terá uma política de cache semelhante a função de obter coordenadas.
- Retornará a distância em km com até 2 dígitos decimais.
/**
* Calcula a distância entre dois endereços em quilômetros utilizando a API Mapbox.
* @param {string} endereco1 - Primeiro endereço.
* @param {string} endereco2 - Segundo endereço.
* @returns {number} Distância em quilômetros, com duas casas decimais.
* @customfunction
*/
function CALCULAR_DISTANCIA(endereco1, endereco2) {
const cache = CacheService.getScriptCache(); // Obtém o cache do script
const cacheKey = `distance_${endereco1}_${endereco2}`;
const cachedData = cache.get(cacheKey);
if (cachedData) {
// Se os dados estiverem no cache, retorna os dados armazenados
return parseFloat(cachedData);
}
const coordenadas1 = OBTER_COORDENADAS(endereco1).split(',').map(Number);
const coordenadas2 = OBTER_COORDENADAS(endereco2).split(',').map(Number);
console.log(coordenadas1, typeof(coordenadas1))
const url = `https://api.mapbox.com/directions/v5/mapbox/driving/${coordenadas1[1]},${coordenadas1[0]};${coordenadas2[1]},${coordenadas2[0]}?access_token=${MAPBOX_TOKEN}`;
console.log(url)
const response = UrlFetchApp.fetch(url);
const dados = JSON.parse(response.getContentText());
if (dados.routes && dados.routes.length > 0) {
const distancia = dados.routes[0].distance / 1000; // Distância em quilômetros
const distanciaFormatada = Number(distancia.toFixed(2));
// Armazena no cache por 6 horas
cache.put(cacheKey, distanciaFormatada.toString(), 21600); // 21600 segundos = 6 horas
return distanciaFormatada;
} else {
throw new Error('Não foi possível calcular a distância.');
}
}No seu editor do Apps Script, insira todo o código abaixo:
const MAPBOX_TOKEN = 'seu-token-de-api-aqui';
/**
* Obtém as coordenadas de um endereço utilizando a API Mapbox.
* @param {string} endereco - Endereço para o qual as coordenadas serão obtidas.
* @param {string} [tipo] - Tipo de retorno: 'LATITUDE', 'LONGITUDE' ou 'AMBOS'. Padrão é 'AMBOS'.
* @returns {string|number|Array} Retorna latitude, longitude ou ambos.
* @customfunction
*/
function OBTER_COORDENADAS(endereco, tipo = 'AMBOS') {
const cache = CacheService.getScriptCache(); // Obtém o cache do script
const cacheKey = `coords_${endereco}`;
const cachedData = cache.get(cacheKey);
let coordenadas;
if (cachedData) {
// Se os dados estiverem no cache, usa os dados armazenados
coordenadas = JSON.parse(cachedData);
} else {
const url = `https://api.mapbox.com/search/geocode/v6/forward?q=${encodeURIComponent(endereco)}&limit=1&access_token=${MAPBOX_TOKEN}`;
const response = UrlFetchApp.fetch(url);
const dados = JSON.parse(response.getContentText());
if (dados.features && dados.features.length > 0) {
const longitude = dados.features[0].properties.coordinates.longitude;
const latitude = dados.features[0].properties.coordinates.latitude;
coordenadas = { latitude, longitude };
// Armazena no cache por 6 horas
cache.put(cacheKey, JSON.stringify(coordenadas), 21600); // 6 horas
} else {
throw new Error('Endereço não encontrado.');
}
}
// Processa o retorno com base no tipo solicitado
switch (tipo.toUpperCase()) {
case 'LATITUDE':
return coordenadas.latitude;
case 'LONGITUDE':
return coordenadas.longitude;
case 'AMBOS':
default:
// Retorna as coordenadas formatadas como string ou array para facilitar o uso
return [coordenadas.latitude, coordenadas.longitude].join(', ');
}
}
/**
* Calcula a distância entre dois endereços em quilômetros utilizando a API Mapbox.
* @param {string} endereco1 - Primeiro endereço.
* @param {string} endereco2 - Segundo endereço.
* @returns {number} Distância em quilômetros, com duas casas decimais.
* @customfunction
*/
function CALCULAR_DISTANCIA(endereco1, endereco2) {
const cache = CacheService.getScriptCache(); // Obtém o cache do script
const cacheKey = `distance_${endereco1}_${endereco2}`;
const cachedData = cache.get(cacheKey);
if (cachedData) {
// Se os dados estiverem no cache, retorna os dados armazenados
return parseFloat(cachedData);
}
const coordenadas1 = OBTER_COORDENADAS(endereco1).split(',').map(Number);
const coordenadas2 = OBTER_COORDENADAS(endereco2).split(',').map(Number);
console.log(coordenadas1, typeof(coordenadas1))
const url = `https://api.mapbox.com/directions/v5/mapbox/driving/${coordenadas1[1]},${coordenadas1[0]};${coordenadas2[1]},${coordenadas2[0]}?access_token=${MAPBOX_TOKEN}`;
console.log(url)
const response = UrlFetchApp.fetch(url);
const dados = JSON.parse(response.getContentText());
if (dados.routes && dados.routes.length > 0) {
const distancia = dados.routes[0].distance / 1000; // Distância em quilômetros
const distanciaFormatada = Number(distancia.toFixed(2));
// Armazena no cache por 6 horas
cache.put(cacheKey, distanciaFormatada.toString(), 21600); // 21600 segundos = 6 horas
return distanciaFormatada;
} else {
throw new Error('Não foi possível calcular a distância.');
}
}Salve, clique em executar para conceder as autorizações necessárias.
Feito isso, você já pode utilizar a função diretamente na planilha:
Caso queira fazer uma cópia dessa planilha de exemplo, já com o script implementado, use o formulário abaixo.
Faça uma cópia da Planilha
OBSERVAÇÃO: É preciso abrir o editor do Apps Script e alterar para a sua chave de API do Mapbox.
Conclusão
Esperamos que tenha gostado dessa nova possibilidade de calcular distância usando dois endereços. Em um passo a passo anterior, ensinamos a utilizar o próprio Google Maps que já é integrado ao Apps Script.
A vantagem do Mapbox está em oferecer um limite diário maior que o Maps. Em breve, uma das continuidades para esse passo a passo será a criação de uma nova funcionalidade para montar rotas otimizadas – utilizando mais de um endereço de parada. O que irá ajudar a decidir a ordem de cada parada.
Além disso, ao dominar essa integração, você está pronto para utilizar outras APIs nos seus projetos do Google Sheets e Apps Script. Por exemplo, caso você utilize o Slack, já pode pensar em como criar notificações nos seus canais – isso é totalmente possível.
Se ficou com dúvidas ou teve problemas na implementação, deixe um comentário abaixo. Participe também da Comunidados, nosso grupo 100% gratuito no WhatsApp.
