Calcular rutas con distancias y tiempos entre puntos con Google Sheets

Google Sheets nos permite hacer uso de varias funciones relacionadas al manejo de coordenadas y direcciones, esto por estar altamente integrado con Google Cloud Platform, que incluye Google Maps. Una de las cosas más interesantes que podemos hacer es crear rutas secuenciales para planear recorridos, y obtener información súper útil, como la distancia y el tiempo de cada parte del recorrido. Hoy veremos cómo hacer esto en Google Sheets, usando un poco de código de Apps Script y nuestro propio ingenio.

Preparando nuestras coordenadas

Lo primero que tenemos que hacer es tener preparadas nuestras coordenadas en una hoja llamada Direcciones. Esto puede ser en forma de direcciones físicas o de latitud y longitud, que será mucho más preciso. Sí puedes combinarlas, solo ten en cuenta que para el caso de latitud y longitud, debes usar el formato adecuado, es decir, en la misma celda deben estar ambos datos separados por una coma.

Preparando nuestra hoja de rutas y distancias

Ahora, debemos hacer una hoja nueva en el mismo documento en la que escribiremos la ruta completa en el orden de las direcciones que tenemos. Esta hoja se llamará Ruta, y en ella necesitamos crear cuatro encabezados: De, A, Distancia (km) y Tiempo (min).

Configurando nuestro proyecto de Apps Script

A partir de ahora, vamos a tener que usar un poco de código de Apps Script para comunicarnos con Google Maps y pedirle que interprete estas direcciones.

Para eso, vamos al menú Extensiones – Apps Script 

Ahora, con un poco de código creamos un menú llamado Calcular ruta, que incluye a su vez el submenú ‘Calcular distancias de ruta‘, el cual ejecutará la función calcular_distancias() . El código que te aparece por default, lo reemplazaremos por este, que hace lo que acabamos de decir:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet_direcciones = ss.getSheetByName('Direcciones');
var sheet_ruta = ss.getSheetByName('Ruta');

function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Calcular ruta')
      .addItem('Calcular distancias de ruta', 'calcular_distancias')
      .addToUi();
}Lenguaje del código: JavaScript (javascript)

Como variables globales, estamos obteniendo primero cada una de las hojas que creamos hace unos momentos: Direcciones y Ruta, para poder manipularlas luego.

Creando la ruta entre los puntos

Ahora sí, lo que tenemos que hacer ahora es crear esta función de calcular_distancias() para revisar las direcciones o coordenadas en la hoja actual, y adaptarlas a como la documentación de Google Maps en Apps Script nos pide que configuremos la petición. Entonces, tenemos que revisar antes, justamente cómo funciona esta petición:

Como puedes ver lo primero que hacemos es crear un objeto de tipo Maps.newDirectionFinder(), el cual debemos configurar. Tenemos que elegir un punto de partida, opcionalmente agregar puntos intermedios, y agregar un punto final. Entonces, nuestra primera función se podría ver así, si construyéramos manualmente un objeto llamado instrucciones usando Maps.newDirectionFinder()

function calcular_distancias() {
  var instrucciones = Maps.newDirectionFinder()
  .setOrigin('Plaza, Av. de la República S/N, Tabacalera, 06030 Ciudad de México, CDMX')
  .addWaypoint('Paseo de la Reforma y Eje 2 PTE, Juárez, Cuauhtémoc, 06500 Ciudad de México, CDMX')
  .addWaypoint('Blvd. Miguel de Cervantes Saavedra 303, Granada, 11520 Ciudad de México, CDMX')
  .addWaypoint('Plaza de la Constitución S/N, Centro, Cuauhtémoc, 06000 Ciudad de México, CDMX')
  .addWaypoint('19.433921,-99.1406428')
  .addWaypoint('Seminario 8, Centro Histórico de la Cdad. de México, Cuauhtémoc, 06060 Ciudad de México, CDMX')
  .setDestination('Centro Histórico de la Cdad. de México, Guerrero, 06300 Ciudad de México, CDMX')
  .setMode(Maps.DirectionFinder.Mode.DRIVING)
  .getDirections();

  var ruta = instrucciones.routes[0];
}Lenguaje del código: JavaScript (javascript)

También debemos elegir si el trayecto se realizará en automóvil, caminando, en bicicleta, etc. Para no complicarnos, vamos a asumir que el recorrido se hará en auto y dejemos esa opción como está.

Finalmente, obtenemos las direcciones y todo esto lo guardamos en nuestra variable ruta. Lo que haremos es pedirle a Google Maps que nos devuelva las instrucciones necesarias para recorrer esta ruta, y dentro de las mismas se encontrará la información del número de kilómetros entre cada punto, así como el tiempo de recorrido.

Si ejecutamos este código así como está y usamos la función de depuración (debugger) podemos deducir que en la respuesta que nos devuelve Google Maps, está todo lo que necesitamos.

Fíjate cómo en este objeto de ruta que nos devuelve Google Maps, tenemos varias cosas interesantes. Hay un arreglo llamado legs en el que se encuentra la información de cuánta distancia y tiempo hay entre cada punto del mapa en un recorrido simulado por Google. También tenemos la latitud y longitud exacta de la dirección humana que le dimos a interpretar.

Calcular distancias entre direcciones de nuestra hoja

Lo que nos resta por hacer es integrar la información de nuestra hoja de cálculo en el código que acabamos de ver, para solamente guardar la información. Para eso, tenemos que recorrer cada fila con direcciones y construir el objeto correctamente, y eso cambia un poco el código de la función principal.

Si te fijas bien, estamos haciendo lo mismo que hace unos momentos, pero ahora tenemos que leer las celdas con las direcciones desde la hoja, y decidir cuál punto debe ser el origen, cuál debe ser el destino final, y cuáles son los puntos intermedios obligatorios por los que se debe recorrer la ruta. También ponemos algunos controles necesarios para que no tengamos errores, como exigir que la ruta tenga al menos dos puntos, y que las direcciones no estén vacías.

function calcular_distancias() {
  var filaInicial = 2;
  var ultimaFila = sheet_direcciones.getLastRow();
  var direccionesDataRange = sheet_direcciones.getRange(filaInicial, 1,ultimaFila, 1);
  var direccionesData = direccionesDataRange.getValues();

  var instrucciones = Maps.newDirectionFinder();

  // Necesitamos al menos dos coordenadas para que nuestro código funcione
  if (direccionesData.length > 2) {
    for (var i = 0; i < (direccionesData.length - 1); i++) {
      var direccion = direccionesData[i][0];
      // Solamente haremos algo si no están vacías las coordenadas, 
      // para no generar errores inesperados
      if (direccion != "") {
        // Si es el primer punto, ponemos el origen
        if (i == 0) {
          instrucciones.setOrigin(direccion);
          // Si es el último punto, ponemos el destino
        } else if (i == (direccionesData.length - 2)) {
          instrucciones.setDestination(direccion);
          // Si es un punto intermedio, lo agregamos como waypoint 
        } else {
          instrucciones.addWaypoint(direccion);
        }
      }
    }
  } else {
    SpreadsheetApp.getActive().toast('Se necesitan al menos dos direcciones o coordenadas.', '⚠️ Error');
    return;
  }

  instrucciones.setMode(Maps.DirectionFinder.Mode.DRIVING);

  instrucciones = instrucciones.getDirections();

  var ruta = instrucciones.routes[0];
  escribir_ruta(ruta);
}Lenguaje del código: JavaScript (javascript)

Escribir la ruta en la hoja

Después, tenemos que escribir en las columnas siguientes los resultados que obtuvimos. Para eso haremos una función nueva llamada escribir_ruta(), a la que le estamos pasando el objeto de ruta. En las columnas de De y A usaremos el valor correspondiente de leg.start_address y leg.end_address . En la columna que marcamos como distancia, ingresamos la distancia de esa parte del recorrido dividida entre 1000, porque el valor original viene en metros, y en la de tiempo, el tiempo dividido entre 60, porque viene en segundos, y lo queremos en minutos. También usamos una función adicional que nos servirá para limpiar la hoja de ruta antes de empezar una nueva:

function escribir_ruta(ruta) {
  var filaInicial = 2;

  // Limpiamos las celdas de la ruta actual
  limpiar_ruta();

  // Escribimos la información de la ruta en la hoja de Ruta
  for(var i = 0; i < ruta.legs.length; i++) {
    var leg = ruta.legs[i];
    var distancia = (leg.distance.value) / 1000; // m a km
    var tiempo = (leg.duration.value) / 60; // segundos a min
    var de = leg.start_address;
    var a = leg.end_address;

    sheet_ruta.getRange(filaInicial + i, 1).setValue(de); // columna De
    sheet_ruta.getRange(filaInicial + i, 2).setValue(a); // columna A
    sheet_ruta.getRange(filaInicial + i, 3).setValue(distancia); // columna Distancia (km)
    sheet_ruta.getRange(filaInicial + i, 4).setValue(tiempo); // columna Tiempo (min)
  }
}

function limpiar_ruta() {
  var filaInicial = 2;
  var ultimaFila = sheet_ruta.getLastRow();
  var rutaDataRange = sheet_ruta.getRange(filaInicial, 1,ultimaFila, 1);
  var rutaSheetData = rutaDataRange.getValues();
  for(var i = 0; i < rutaSheetData.length; i++) {
    sheet_ruta.getRange(filaInicial + i, 1).setValue('');
    sheet_ruta.getRange(filaInicial + i, 2).setValue('');
    sheet_ruta.getRange(filaInicial + i, 3).setValue('');
    sheet_ruta.getRange(filaInicial + i, 4).setValue('');
  }
}Lenguaje del código: PHP (php)

Si guardamos nuestro código y lo ejecutamos, después de ver las pantallas de siempre sobre los permisos y obtener nuestro menú personalizado, podremos ver que nuestra hoja de Ruta se llena con las coordenadas que le dimos y tenemos la información de distancia y tiempo de cada parte del recorrido:

Parece magia, ¿no? Esto te debe dar ideas para muchos usos prácticos reales, y puedes jugar el código para adaptarlo a lo que necesites.

Código completo para calcular rutas en Google Sheets

Como siempre, te comparto el código completo para que puedas copiar y pegar:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet_direcciones = ss.getSheetByName('Direcciones');
var sheet_ruta = ss.getSheetByName('Ruta');

function onOpen() {  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Calcular ruta')
      .addItem('Calcular distancia de ruta', 'calcular_distancias')
      .addToUi();
}

function calcular_distancias() {
  var filaInicial = 2;
  var ultimaFila = sheet_direcciones.getLastRow();
  var direccionesDataRange = sheet_direcciones.getRange(filaInicial, 1,ultimaFila, 1);
  var direccionesData = direccionesDataRange.getValues();

  var instrucciones = Maps.newDirectionFinder();

  // Necesitamos al menos dos coordenadas para que nuestro código funcione
  if (direccionesData.length > 2) {
    for (var i = 0; i < (direccionesData.length - 1); i++) {
      var direccion = direccionesData[i][0];
      // Solamente haremos algo si no están vacías las coordenadas, 
      // para no generar errores inesperados
      if (direccion != "") {
        // Si es el primer punto, ponemos el origen
        if (i == 0) {
          instrucciones.setOrigin(direccion);
          // Si es el último punto, ponemos el destino
        } else if (i == (direccionesData.length - 2)) {
          instrucciones.setDestination(direccion);
          // Si es un punto intermedio, lo agregamos como waypoint 
        } else {
          instrucciones.addWaypoint(direccion);
        }
      }
    }
  } else {
    SpreadsheetApp.getActive().toast('Se necesitan al menos dos direcciones o coordenadas.', '⚠️ Error');
    return;
  }

  instrucciones.setMode(Maps.DirectionFinder.Mode.DRIVING);

  instrucciones = instrucciones.getDirections();

  var ruta = instrucciones.routes[0];

  escribir_ruta(ruta);
}

function escribir_ruta(ruta) {
  var filaInicial = 2;

  // Limpiamos las celdas de la ruta actual
  limpiar_ruta();

  // Escribimos la información de la ruta en la hoja de Ruta
  for(var i = 0; i < ruta.legs.length; i++) {
    var leg = ruta.legs[i];
    var distancia = (leg.distance.value) / 1000; // m a km
    var tiempo = (leg.duration.value) / 60; // segundos a min
    var de = leg.start_address;
    var a = leg.end_address;

    sheet_ruta.getRange(filaInicial + i, 1).setValue(de); // columna De
    sheet_ruta.getRange(filaInicial + i, 2).setValue(a); // columna A
    sheet_ruta.getRange(filaInicial + i, 3).setValue(distancia); // columna Distancia (km)
    sheet_ruta.getRange(filaInicial + i, 4).setValue(tiempo); // columna Tiempo (min)
  }
}

function limpiar_ruta() {
  var filaInicial = 2;
  var ultimaFila = sheet_ruta.getLastRow();
  var rutaDataRange = sheet_ruta.getRange(filaInicial, 1,ultimaFila, 1);
  var rutaSheetData = rutaDataRange.getValues();
  for(var i = 0; i < rutaSheetData.length; i++) {
    sheet_ruta.getRange(filaInicial + i, 1).setValue('');
    sheet_ruta.getRange(filaInicial + i, 2).setValue('');
    sheet_ruta.getRange(filaInicial + i, 3).setValue('');
    sheet_ruta.getRange(filaInicial + i, 4).setValue('');
  }
}Lenguaje del código: PHP (php)

Corroborando los datos en Google Maps

Ahora, debes cerciorarte de que la información sea correcta. Una forma muy sencilla de hacerlo, es comparar nuestro resultado en el mismo Google Maps. Si juntamos todas las direcciones y las concatenamos con la dirección base de Google Maps, fácilmente lo lograremos si usamos esta fórmula:

=HYPERLINK(ArrayFormula("https://www.google.com/maps/dir/"&CONCATENATE(IF(Direcciones!A2:A<>"",ENCODEURL(Direcciones!A2:A)&"/",""))), "Ver en mapa")

Documentación oficial y límites de uso

En la documentación oficial de Google puedes revisar más información sobre todo lo que se puede hacer con el resultado que devuelve Google Maps, ya que aquí solamente tocamos una pequeña parte de todo lo que se puede lograr.

También es importante que revises los límites diarios de uso de las peticiones de Maps, porque si sobrepasas estos límites, tal vez tengas que considerar más bien usar la API de Google Maps en vez de las funciones que provee Apps Script, que son un poco más limitadas, con la ventaja de no requerir una API Key.

¡Y listo! Ahora ya sabes cómo crear rutas con distancias y tiempos precisos en hojas de cálculo de Google Sheets y Google Maps fácilmente.

Plantilla terminada para descargar

Plantilla para Calcular rutas con distancias y tiempos entre puntos con Google Sheets

Ver más

Guía en video

Si tienes dudas puedes ver esta misma guía explicada en video:

¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.