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

• Comprender qué hace la solución
• Comprender 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 partida 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 sobre cómo llegar en automóvil entre dos ubicaciones y mostrar 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 la fila de las direcciones de inicio y destino para calcular las instrucciones sobre cómo llegar en automóvil y agrega las instrucciones sobre cómo llegar en automóvil paso a paso a una hoja nueva.

Servicios de Apps Script

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

• Servicio de hoja de cálculo: Agrega el menú personalizado, agrega datos de demostración para probar esta solución y aplica formato a las hojas nuevas cuando la secuencia de comandos agrega instrucciones sobre cómo llegar.
• Servicio básico: Usa la clase Browser para solicitarle al usuario que ingrese un número de fila para obtener instrucciones sobre cómo llegar y le avisa si se produce un error.
• Servicio de utilidades: Actualiza cadenas con plantillas con la información especificada por el usuario.
• Servicio de Maps: Obtén instrucciones paso a paso de Google Maps desde la dirección de partida hasta la de destino.

Requisitos previos

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

• Una Cuenta de Google (es posible que las cuentas de Google Workspace requieran 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 Calculate driving distance and convert meters to miles. 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, continúa seleccionando Avanzado > Ir a {nombre del proyecto} (no seguro).

4. Vuelve a hacer clic en Instrucciones > Preparar hoja.

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 destino, 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 Instrucciones sobre cómo llegar > Generar instrucciones paso a paso.
5. En el cuadro de diálogo, ingresa el número de fila de las direcciones para las que deseas generar instrucciones y haz clic en Aceptar.
6. Revisa las instrucciones sobre cómo llegar en la hoja nueva 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 expertos en desarrollo de Google.

Próximos pasos

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