Hitung jarak mengemudi & konversikan meter ke mil

Tingkat coding: Pemula
Durasi: 10 menit
Jenis project: Fungsi kustom dan otomatisasi dengan menu kustom

Tujuan

  • Pahami fungsi solusi.
  • Pahami fungsi layanan Apps Script dalam solusi.
  • Siapkan skrip.
  • Jalankan skrip.

Tentang solusi ini

Dengan menggunakan fungsi kustom, Anda dapat menghitung jarak mengemudi antara dua lokasi dan mengonversi jarak dari meter ke mil. Automasi tambahan menyediakan menu kustom yang memungkinkan Anda menambahkan petunjuk langkah demi langkah dari alamat awal ke alamat akhir dalam sheet baru.

Screenshot rute mengemudi di sheet.

Cara kerjanya

Skrip ini menggunakan 2 fungsi kustom dan otomatisasi.

  • Fungsi drivingDistance(origin, destination) menggunakan Maps Service untuk menghitung rute mengemudi antara dua lokasi dan menampilkan jarak antara dua alamat dalam meter.
  • Fungsi metersToMiles(meters) menghitung jumlah mil yang setara untuk jumlah meter tertentu.
  • Otomatisasi akan meminta pengguna untuk memasukkan baris alamat awal dan akhir yang akan dihitung rute perjalanannya dan menambahkan rute perjalanan langkah demi langkah ke sheet baru.

Layanan Apps Script

Solusi ini menggunakan layanan berikut:

  • Layanan spreadsheet–Menambahkan menu kustom, menambahkan data demo untuk menguji solusi ini, dan memformat sheet baru saat skrip menambahkan petunjuk arah.
  • Layanan dasar–Menggunakan class Browser untuk meminta pengguna memasukkan nomor baris untuk petunjuk arah dan memberi tahu pengguna jika terjadi error.
  • Layanan utilitas–Memperbarui string template dengan informasi yang ditentukan pengguna.
  • Layanan Maps–Mendapatkan petunjuk arah Google Maps langkah demi langkah dari alamat awal ke alamat akhir.

Prasyarat

Untuk menggunakan sampel ini, Anda memerlukan prasyarat berikut:

  • Akun Google (akun Google Workspace mungkin memerlukan persetujuan administrator).
  • Browser web dengan akses ke internet.

Menyiapkan skrip

  1. Buat salinan spreadsheet Calculate driving distance and convert meters to miles. Project Apps Script untuk solusi ini dilampirkan ke spreadsheet.
    Buat salinan
  2. Untuk menambahkan header dan data demo ke spreadsheet, klik Petunjuk > Siapkan spreadsheet. Anda mungkin perlu memuat ulang halaman agar menu kustom ini muncul.

  3. Saat diminta, izinkan skrip. Jika layar izin OAuth menampilkan peringatan Aplikasi ini belum diverifikasi, lanjutkan dengan memilih Lanjutan > Buka {Project Name} (tidak aman).

  4. Klik Petunjuk arah > Siapkan sheet lagi.

Jalankan skrip:

  1. Di sel C2, masukkan formula =DRIVINGDISTANCE(A2,B2), lalu tekan Enter. Jika Anda berada di lokasi yang menggunakan koma desimal, Anda mungkin perlu memasukkan =DRIVINGDISTANCE(A2;B2) sebagai gantinya.
  2. Di sel D2, masukkan formula =METERSTOMILES(C2), lalu tekan Enter.
  3. (Opsional) Tambahkan baris alamat awal dan akhir tambahan, lalu salin formula di kolom C dan D untuk menghitung jarak mengemudi antara berbagai tempat.
  4. Klik Rute > Buat langkah demi langkah.
  5. Dalam dialog, masukkan nomor baris alamat yang ingin Anda buat petunjuk arahnya, lalu klik Oke.
  6. Tinjau petunjuk arah mengemudi di sheet baru yang dibuat skrip.

Meninjau kode

Untuk meninjau kode Apps Script untuk solusi ini, klik Lihat kode sumber di bawah:

Melihat kode sumber

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

Kontributor

Contoh ini dikelola oleh Google dengan bantuan Pakar Developer Google.

Langkah berikutnya