Calcula la distancia en automóvil y convierte los metros a millas

Nivel de programación: Principiante
Duración: 10 minutos
Tipo de proyecto: Función personalizada y automatización con un menú personalizado

Objetivos

• Comprende qué hace la solución.
• Comprende qué hacen los servicios de Apps Script dentro de la solución.
• Configura la secuencia de comandos.
• Ejecuta la secuencia de comandos.

Acerca de esta solución

Con las funciones personalizadas, puedes calcular la distancia en automóvil entre dos ubicaciones y convertir la distancia de metros a millas. Una automatización adicional proporciona un menú personalizado que te permite agregar instrucciones paso a paso desde la dirección de inicio hasta la dirección de destino en una hoja nueva.

Cómo funciona

La secuencia de comandos usa 2 funciones personalizadas y una automatización.

• La función drivingDistance(origin, destination) usa el servicio de Maps para calcular las instrucciones de conducción entre dos ubicaciones y devolver la distancia entre las dos direcciones en metros.
• La función metersToMiles(meters) calcula la cantidad equivalente de millas para una cantidad determinada de metros.
• La automatización le solicita al usuario que ingrese qué fila de direcciones de inicio y finalización se debe usar para calcular las instrucciones sobre cómo llegar en automóvil y agrega las instrucciones paso a paso a una nueva hoja.

Servicios de Apps Script

En esta solución, se usan los siguientes servicios:

• Servicio de hojas de cálculo: Agrega el menú personalizado, agrega datos de demostración para probar esta solución y da formato a las hojas nuevas cuando la secuencia de comandos agrega instrucciones para llegar en automóvil.
• Servicio base: Usa la clase Browser para solicitarle al usuario que ingrese un número de fila para las instrucciones y alertarlo si se produce un error.
• Servicio de utilidades: Actualiza cadenas basadas en plantillas con información especificada por el usuario.
• Servicio de Maps: Obtiene instrucciones paso a paso de Google Maps desde la dirección de inicio hasta la dirección de destino.

Requisitos previos

Para usar esta muestra, necesitas cumplir con los siguientes requisitos previos:

• Una Cuenta de Google (es posible que las cuentas de Google Workspace requieran la aprobación del administrador)
• Un navegador web con acceso a Internet

Configura la secuencia de comandos

1. Haz una copia de la hoja de cálculo Calcula la distancia en automóvil y convierte metros en millas. El proyecto de Apps Script para esta solución se adjunta a la hoja de cálculo.
   Crear una copia
2. Para agregar encabezados y datos de demostración a tu hoja, haz clic en Instrucciones > Preparar hoja. Es posible que debas actualizar la página para que aparezca este menú personalizado.

3. Cuando se te solicite, autoriza la secuencia de comandos. Si la pantalla de consentimiento de OAuth muestra la advertencia Esta app no está verificada, selecciona Opciones avanzadas > Ir a {Nombre del proyecto} (no seguro) para continuar.

4. Haz clic en Cómo llegar > Preparar hoja de nuevo.

Ejecuta la secuencia de comandos:

1. En la celda C2, ingresa la fórmula =DRIVINGDISTANCE(A2,B2) y presiona Intro. Si te encuentras en una ubicación que usa comas decimales, es posible que debas ingresar =DRIVINGDISTANCE(A2;B2).
2. En la celda D2, ingresa la fórmula =METERSTOMILES(C2) y presiona Intro.
3. (Opcional) Agrega filas adicionales de direcciones de inicio y finalización, y copia las fórmulas de las columnas C y D para calcular las distancias de conducción entre varios lugares.
4. Haz clic en Cómo llegar > Generar instrucciones paso a paso.
5. En el diálogo, ingresa el número de fila de las direcciones para las que deseas generar instrucciones sobre cómo llegar y haz clic en Aceptar.
6. Revisa las instrucciones para llegar en auto en la nueva hoja de cálculo que crea la secuencia de comandos.

Revisa el código

Para revisar el código de Apps Script de esta solución, haz clic en Ver código fuente a continuación:

Ver el código fuente

Code.gs

sheets/customFunctions/customFunctions.gs

Ver en GitHub

/**
 * @OnlyCurrentDoc Limits the script to only accessing the current sheet.
 */

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  try {
    const spreadsheet = SpreadsheetApp.getActive();
    const menuItems = [
      {name: 'Prepare sheet...', functionName: 'prepareSheet_'},
      {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}
    ];
    spreadsheet.addMenu('Directions', menuItems);
  } catch (e) {
    // TODO (Developer) - Handle Exception
    console.log('Failed with error: %s' + e.error);
  }
}

/**
 * A custom function that converts meters to miles.
 *
 * @param {Number} meters The distance in meters.
 * @return {Number} The distance in miles.
 */
function metersToMiles(meters) {
  if (typeof meters !== 'number') {
    return null;
  }
  return meters / 1000 * 0.621371;
}

/**
 * A custom function that gets the driving distance between two addresses.
 *
 * @param {String} origin The starting address.
 * @param {String} destination The ending address.
 * @return {Number} The distance in meters.
 */
function drivingDistance(origin, destination) {
  const directions = getDirections_(origin, destination);
  return directions.routes[0].legs[0].distance.value;
}

/**
 * A function that adds headers and some initial data to the spreadsheet.
 */
function prepareSheet_() {
  try {
    const sheet = SpreadsheetApp.getActiveSheet().setName('Settings');
    const headers = [
      'Start Address',
      'End Address',
      'Driving Distance (meters)',
      'Driving Distance (miles)'];
    const initialData = [
      '350 5th Ave, New York, NY 10118',
      '405 Lexington Ave, New York, NY 10174'];
    sheet.getRange('A1:D1').setValues([headers]).setFontWeight('bold');
    sheet.getRange('A2:B2').setValues([initialData]);
    sheet.setFrozenRows(1);
    sheet.autoResizeColumns(1, 4);
  } catch (e) {
    // TODO (Developer) - Handle Exception
    console.log('Failed with error: %s' + e.error);
  }
}

/**
 * Creates a new sheet containing step-by-step directions between the two
 * addresses on the "Settings" sheet that the user selected.
 */
function generateStepByStep_() {
  try {
    const spreadsheet = SpreadsheetApp.getActive();
    const settingsSheet = spreadsheet.getSheetByName('Settings');
    settingsSheet.activate();

    // Prompt the user for a row number.
    const selectedRow = Browser
        .inputBox('Generate step-by-step', 'Please enter the row number of' +
        ' the' + ' addresses to use' + ' (for example, "2"):',
        Browser.Buttons.OK_CANCEL);
    if (selectedRow === 'cancel') {
      return;
    }
    const rowNumber = Number(selectedRow);
    if (isNaN(rowNumber) || rowNumber < 2 ||
      rowNumber > settingsSheet.getLastRow()) {
      Browser.msgBox('Error',
          Utilities.formatString('Row "%s" is not valid.', selectedRow),
          Browser.Buttons.OK);
      return;
    }


    // Retrieve the addresses in that row.
    const row = settingsSheet.getRange(rowNumber, 1, 1, 2);
    const rowValues = row.getValues();
    const origin = rowValues[0][0];
    const destination = rowValues[0][1];
    if (!origin || !destination) {
      Browser.msgBox('Error', 'Row does not contain two addresses.',
          Browser.Buttons.OK);
      return;
    }

    // Get the raw directions information.
    const directions = getDirections_(origin, destination);

    // Create a new sheet and append the steps in the directions.
    const sheetName = 'Driving Directions for Row ' + rowNumber;
    let directionsSheet = spreadsheet.getSheetByName(sheetName);
    if (directionsSheet) {
      directionsSheet.clear();
      directionsSheet.activate();
    } else {
      directionsSheet =
        spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());
    }
    const sheetTitle = Utilities
        .formatString('Driving Directions from %s to %s', origin, destination);
    const headers = [
      [sheetTitle, '', ''],
      ['Step', 'Distance (Meters)', 'Distance (Miles)']
    ];
    const newRows = [];
    for (const step of directions.routes[0].legs[0].steps) {
      // Remove HTML tags from the instructions.
      const instructions = step.html_instructions
          .replace(/<br>|<div.*?>/g, '\n').replace(/<.*?>/g, '');
      newRows.push([
        instructions,
        step.distance.value
      ]);
    }
    directionsSheet.getRange(1, 1, headers.length, 3).setValues(headers);
    directionsSheet.getRange(headers.length + 1, 1, newRows.length, 2)
        .setValues(newRows);
    directionsSheet.getRange(headers.length + 1, 3, newRows.length, 1)
        .setFormulaR1C1('=METERSTOMILES(R[0]C[-1])');

    // Format the new sheet.
    directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');
    directionsSheet.getRange('A1:2').setFontWeight('bold');
    directionsSheet.setColumnWidth(1, 500);
    directionsSheet.getRange('B2:C').setVerticalAlignment('top');
    directionsSheet.getRange('C2:C').setNumberFormat('0.00');
    const stepsRange = directionsSheet.getDataRange()
        .offset(2, 0, directionsSheet.getLastRow() - 2);
    setAlternatingRowBackgroundColors_(stepsRange, '#ffffff', '#eeeeee');
    directionsSheet.setFrozenRows(2);
    SpreadsheetApp.flush();
  } catch (e) {
    // TODO (Developer) - Handle Exception
    console.log('Failed with error: %s' + e.error);
  }
}

/**
 * Sets the background colors for alternating rows within the range.
 * @param {Range} range The range to change the background colors of.
 * @param {string} oddColor The color to apply to odd rows (relative to the
 *     start of the range).
 * @param {string} evenColor The color to apply to even rows (relative to the
 *     start of the range).
 */
function setAlternatingRowBackgroundColors_(range, oddColor, evenColor) {
  const backgrounds = [];
  for (let row = 1; row <= range.getNumRows(); row++) {
    const rowBackgrounds = [];
    for (let column = 1; column <= range.getNumColumns(); column++) {
      if (row % 2 === 0) {
        rowBackgrounds.push(evenColor);
      } else {
        rowBackgrounds.push(oddColor);
      }
    }
    backgrounds.push(rowBackgrounds);
  }
  range.setBackgrounds(backgrounds);
}

/**
 * A shared helper function used to obtain the full set of directions
 * information between two addresses. Uses the Apps Script Maps Service.
 *
 * @param {String} origin The starting address.
 * @param {String} destination The ending address.
 * @return {Object} The directions response object.
 */
function getDirections_(origin, destination) {
  const directionFinder = Maps.newDirectionFinder();
  directionFinder.setOrigin(origin);
  directionFinder.setDestination(destination);
  const directions = directionFinder.getDirections();
  if (directions.status !== 'OK') {
    throw directions.error_message;
  }
  return directions;
}

Colaboradores

Google mantiene esta muestra con la ayuda de los Google Developer Experts.

Próximos pasos

• Funciones personalizadas en Hojas de cálculo de Google
• Menús personalizados en Google Workspace