Cette page a été traduite par l'API Cloud Translation.

Calculer la distance en voiture et convertir des mètres en miles

Niveau de codage: Débutant
Durée: 10 minutes
Type de projet: fonction personnalisée et d'automatisation avec un menu personnalisé

Objectifs

Comprendre ce que fait la solution.
Vous connaissez le fonctionnement des services Apps Script dans le solution.
Configurez le script.
Exécutez le script.

À propos de cette solution

Les fonctions personnalisées vous permettent de calculer la distance en voiture entre deux et convertir la distance de mètres en miles. Une autre propose un menu personnalisé qui vous permet d'ajouter des instructions détaillées l’adresse de départ à l’adresse d’arrivée dans une nouvelle feuille.

Capture d'écran d'un itinéraire en voiture dans une feuille de calcul.

Fonctionnement

Le script utilise deux fonctions personnalisées et une automatisation.

La fonction drivingDistance(origin, destination) utilise le service Maps pour : calculer l'itinéraire en voiture entre deux lieux et renvoient la distance entre les deux adresses en mètres.
La fonction metersToMiles(meters) calcule le nombre équivalent de miles pour un nombre donné de mètres.
L'automatisation invite l'utilisateur à indiquer ligne d'adresses de départ et d'arrivée afin de calculer l'itinéraire pour et ajoute l'itinéraire étape par étape dans une nouvelle feuille.

Services Apps Script

Cette solution utilise les services suivants:

Service Spreadsheet (feuilles de calcul) : ajoute la classe personnalisée , ajoute des données de démonstration pour tester cette solution et met en forme les nouvelles feuilles lorsque le script ajoute les itinéraires routiers.
Service de base : utilise la classe Browser pour inviter l'utilisateur à saisir un numéro de ligne pour l'itinéraire et l'avertir si une erreur se produit.
Services publics : met à jour les chaînes modélisées avec des informations spécifiées par l'utilisateur.
Service Maps : obtenez des instructions détaillées dans Google Maps. l'itinéraire entre l'adresse de départ et l'adresse d'arrivée.

Prérequis

Pour utiliser cet exemple, vous devez remplir les conditions préalables suivantes:

Un compte Google (les comptes Google Workspace peuvent nécessitent l'approbation d'un administrateur).
Un navigateur Web ayant accès à Internet

Configurer le script

Créez une copie de l'atelier Calculer la distance de conduite et convertir des mètres en miles. feuille de calcul. Le projet Apps Script pour cette solution est jointe à la feuille de calcul.
Créer une copie
Pour ajouter des en-têtes et des données de démonstration à votre feuille, cliquez sur Itinéraires > Préparer la feuille. Vous devrez peut-être actualisez la page pour que ce menu personnalisé s'affiche.
Lorsque vous y êtes invité, autorisez le script. Si l'écran de consentement OAuth affiche l'avertissement Cette application n'est pas validée, cliquez sur Avancé > pour continuer. Accédez à {Project Name} (non sécurisé).
Cliquez sur Itinéraire > Préparer la feuille à nouveau.

Exécuter le script

Dans la cellule C2, saisissez la formule =DRIVINGDISTANCE(A2,B2), puis appuyez sur Entrée. Si vous vous trouvez dans un lieu où les virgules décimales sont utilisées, vous devrez peut-être saisir =DRIVINGDISTANCE(A2;B2) à la place.
Dans la cellule D2, saisissez la formule =METERSTOMILES(C2), puis appuyez sur Entrée.
(Facultatif) Ajoutez des lignes supplémentaires contenant les adresses de départ et d'arrivée, puis copiez les valeurs formules dans les colonnes C et D pour calculer les distances en voiture entre à différents endroits.
Cliquez sur Itinéraire > Générer une requête étape par étape
Dans la boîte de dialogue, saisissez le numéro de ligne des adresses pour lesquelles vous souhaitez générer un itinéraire, puis cliquer sur OK.
Passez en revue les itinéraires routiers dans la nouvelle feuille créée par le script.

Examiner le code

Pour examiner le code Apps Script de cette solution, cliquez sur Afficher le code source. ci-dessous:

Afficher le code source

Code.gs

sheets/customFunctions/customFunctions.gs

Afficher sur 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;
}

Contributeurs

Cet échantillon est géré par Google avec l'aide d'Experts Google Developers.