Calcular distância entre endereços com API do Mapbox – Google Sheets

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.

• Como usar fórmulas do Google Maps no Google Sheets

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:

1. Acesse: https://account.mapbox.com/auth/signup/
2. Selecione o tipo de conta (Business ou Individual)
3. Preencha todos os dados e confirme o seu e-mail.
4. 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):

1. essa função irá receber 1 endereço e o tipo de informação a ser retornado:
   • LATITUDE ou LONGITUDE, ou AMBOS.
2. 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).
3. 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:

1. Receberá dois argumentos: endereço de partida e endereço de chegada.
2. Também terá uma política de cache semelhante a função de obter coordenadas.
3. 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.

❎

Clique no botão abaixo para acessar o Google Sheets.

Abrir Planilha

Faça uma cópia da Planilha​

E-mail

Ao fazer uma cópia, você continua aceitando a nossa Política de Privacidade. 🔒

FAZER UMA CÓPIA

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.