Obliczanie dystansu jazdy i przeliczaj metry na mile

Poziom umiejętności programowania: początkujący
Czas trwania: 10 minut
Typ projektu: funkcja niestandardowa i automatyzacja z menu niestandardowym

Cele

  • Dowiedz się, na czym polega działanie rozwiązania.
  • Dowiedz się, jak usługi Apps Script działają w ramach rozwiązania.
  • Skonfiguruj skrypt.
  • Uruchom skrypt.

Informacje o rozwiązaniu

Za pomocą funkcji niestandardowych możesz obliczyć odległość jazdy między dwoma lokalizacjami i przeliczyć ją z metrów na mile. Dodatkowa automatyzacja zapewnia menu niestandardowe, które umożliwia dodawanie szczegółowych wskazówek od adresu początkowego do końcowego na nowym arkuszu.

Zrzut ekranu z trasą dojazdu w arkuszu.

Jak to działa

Skrypt używa 2 funkcji niestandardowych i automatyzacji.

  • Funkcja drivingDistance(origin, destination) korzysta z usługi Mapy, aby obliczyć wskazówki dojazdu z jednego miejsca do drugiego i zwrócić odległość między tymi dwoma adresami w metrach.
  • Funkcja metersToMiles(meters) oblicza odpowiednią liczbę mil dla danej liczby metrów.
  • Automatyzacja prosi użytkownika o wpisanie adresów początkowego i końcowego, aby obliczyć wskazówki dojazdu, a potem dodaje szczegółowe wskazówki dojazdu do nowego arkusza.

Usługi Apps Script

To rozwiązanie korzysta z tych usług:

  • Usługa arkuszy kalkulacyjnych – dodaje menu niestandardowe, dodaje dane demonstracyjne do przetestowania tego rozwiązania i formatuje nowe arkusze, gdy skrypt dodaje wskazówki dojazdu.
  • Usługa podstawowa – używa klasy Browser, aby poprosić użytkownika o wpisanie numeru wiersza dla wskazówek i o powiadomienie go, jeśli wystąpi błąd.
  • Usługa Utilities – aktualizuje szablonowe ciągi znaków za pomocą informacji określonych przez użytkownika.
  • Usługa Mapy – umożliwia wyświetlenie szczegółowych wskazówek dojazdu z adresu początkowego do końcowego.

Wymagania wstępne

Aby skorzystać z tego szablonu, musisz spełnić te wymagania wstępne:

  • Konto Google (konta Google Workspace mogą wymagać zatwierdzenia przez administratora).
  • przeglądarka internetowa z dostępem do internetu;

Konfigurowanie skryptu

  1. Utwórz kopię arkusza kalkulacyjnego Obliczanie odległości jazdy i przeliczanie metrów na mile. Projekt Apps Script dla tego rozwiązania jest dołączony do arkusza kalkulacyjnego.
    Utwórz kopię
  2. Aby dodać nagłówki i dane demonstracyjne do arkusza, kliknij Wskazówki > Przygotuj arkusz. Aby wyświetlić to menu niestandardowe, może być konieczne odświeżenie strony.

  3. Gdy pojawi się odpowiedni komunikat, autoryzuj skrypt. Jeśli na ekranie zgody OAuth wyświetla się ostrzeżenie Ta aplikacja nie została zweryfikowana, kontynuuj, wybierając Zaawansowane > Przejdź do {Nazwa projektu} (niebezpieczne).

  4. Ponownie kliknij Wskazówki > Przygotuj arkusz.

Uruchamianie skryptu

  1. W komórce C2 wpisz formułę =DRIVINGDISTANCE(A2,B2) i naciśnij Enter. Jeśli znajdujesz się w miejscu, w którym używa się przecinka do oddzielania miejsc po przecinku, być może zamiast tego musisz wpisać =DRIVINGDISTANCE(A2;B2).
  2. W komórce D2 wpisz formułę =METERSTOMILES(C2) i naciśnij Enter.
  3. (Opcjonalnie) Dodaj dodatkowe wiersze adresów początkowego i końcowego oraz skopiuj formuły w kolumnach C i D, aby obliczyć odległość dojazdu między różnymi miejscami.
  4. Kliknij Trasa > Wygeneruj trasę krok po kroku.
  5. W oknie wpisz numer wiersza adresów, dla których chcesz wygenerować wskazówki, i kliknij OK.
  6. Sprawdź wskazówki dojazdu na nowym arkuszu utworzonym przez skrypt.

Sprawdzanie kodu

Aby sprawdzić kod Apps Script dla tego rozwiązania, kliknij Wyświetl kod źródłowy poniżej:

Pokaż kod źródłowy

Code.gs

sheets/customFunctions/customFunctions.gs
/**
 * @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;
}

Współtwórcy

Ten przykład jest obsługiwany przez Google przy pomocy ekspertów Google ds. programowania.

Dalsze kroki