Tính khoảng cách lái xe và chuyển đổi mét sang dặm

Cấp độ lập trình: Sơ cấp
Thời lượng: 10 phút
Loại dự án: Hàm tuỳ chỉnh và tự động hoá bằng trình đơn tuỳ chỉnh

Mục tiêu

• Tìm hiểu chức năng của giải pháp.
• Tìm hiểu chức năng của các dịch vụ Apps Script trong giải pháp.
• Thiết lập tập lệnh.
• Chạy tập lệnh.

Giới thiệu về giải pháp này

Bằng cách sử dụng các hàm tuỳ chỉnh, bạn có thể tính toán khoảng cách lái xe giữa hai vị trí và chuyển đổi khoảng cách từ mét sang dặm. Một quy trình tự động hoá bổ sung cung cấp một trình đơn tuỳ chỉnh cho phép bạn thêm chỉ dẫn từng bước từ địa chỉ bắt đầu đến địa chỉ kết thúc trong một trang tính mới.

Cách hoạt động

Tập lệnh này sử dụng 2 hàm tuỳ chỉnh và một quy trình tự động hoá.

• Hàm drivingDistance(origin, destination) sử dụng Dịch vụ Maps để tính toán chỉ đường lái xe giữa hai vị trí và trả về khoảng cách giữa hai địa chỉ bằng mét.
• Hàm metersToMiles(meters) tính số dặm tương đương cho một số mét nhất định.
• Chế độ tự động hoá sẽ nhắc người dùng nhập hàng địa chỉ bắt đầu và kết thúc để tính toán chỉ đường lái xe, đồng thời thêm chỉ đường lái xe từng bước vào một trang tính mới.

Các dịch vụ của Apps Script

Giải pháp này sử dụng các dịch vụ sau:

• Dịch vụ bảng tính – Thêm trình đơn tuỳ chỉnh, thêm dữ liệu minh hoạ để kiểm thử giải pháp này và định dạng các trang tính mới khi tập lệnh thêm chỉ đường lái xe.
• Dịch vụ cơ bản – Sử dụng lớp Browser để nhắc người dùng nhập số hàng cho chỉ đường và cảnh báo người dùng nếu xảy ra lỗi.
• Dịch vụ tiện ích – Cập nhật các chuỗi theo mẫu bằng thông tin do người dùng chỉ định.
• Dịch vụ Maps – Nhận chỉ đường từng bước trên Google Maps từ địa chỉ bắt đầu đến địa chỉ kết thúc.

Điều kiện tiên quyết

Để sử dụng mẫu này, bạn cần đáp ứng các điều kiện tiên quyết sau:

• Một Tài khoản Google (tài khoản Google Workspace có thể yêu cầu quản trị viên phê duyệt).
• Một trình duyệt web có quyền truy cập vào Internet.

Thiết lập tập lệnh

1. Tạo bản sao của bảng tính Tính khoảng cách lái xe và chuyển đổi mét sang dặm. Dự án Apps Script cho giải pháp này được đính kèm vào bảng tính.
   Tạo bản sao
2. Để thêm tiêu đề và dữ liệu minh hoạ vào trang tính, hãy nhấp vào Hướng dẫn > Chuẩn bị trang tính. Bạn có thể cần làm mới trang để trình đơn tuỳ chỉnh này xuất hiện.

3. Khi được nhắc, hãy cho phép tập lệnh chạy. Nếu màn hình đồng ý OAuth hiển thị cảnh báo Ứng dụng này chưa được xác minh, hãy tiếp tục bằng cách chọn Nâng cao > Chuyển đến {Tên dự án} (không an toàn).

4. Nhấp vào Chỉ dẫn > Chuẩn bị trang tính một lần nữa.

Chạy tập lệnh

1. Trong ô C2, hãy nhập công thức =DRIVINGDISTANCE(A2,B2) rồi nhấn phím Enter. Nếu ở một địa điểm sử dụng dấu phẩy thập phân, bạn có thể cần nhập =DRIVINGDISTANCE(A2;B2).
2. Trong ô D2, hãy nhập công thức =METERSTOMILES(C2) rồi nhấn phím Enter.
3. (Không bắt buộc) Thêm các hàng địa chỉ bắt đầu và kết thúc khác, đồng thời sao chép các công thức trong cột C và D để tính khoảng cách lái xe giữa nhiều địa điểm.
4. Nhấp vào Đường đi > Tạo hướng dẫn từng bước.
5. Trong hộp thoại, hãy nhập số hàng của những địa chỉ mà bạn muốn tạo chỉ đường rồi nhấp vào OK.
6. Xem lại chỉ đường lái xe trong trang tính mới mà tập lệnh tạo.

Xem lại mã

Để xem lại mã Apps Script cho giải pháp này, hãy nhấp vào Xem mã nguồn bên dưới:

Xem mã nguồn

Code.gs

sheets/customFunctions/customFunctions.gs

Xem trên 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;
}

Người đóng góp

Mẫu này do Google duy trì với sự trợ giúp của các Chuyên gia Google về phát triển.

Các bước tiếp theo

• Hàm tuỳ chỉnh trong Google Trang tính
• Trình đơn tuỳ chỉnh trong Google Workspace