คำนวณระยะทางการขับขี่ & แปลงเมตรเป็นไมล์

ระดับการเขียนโค้ด: ผู้เริ่มต้น
ระยะเวลา: 10 นาที
ประเภทโปรเจ็กต์: ฟังก์ชันที่กำหนดเองและการทำงานอัตโนมัติ ด้วยเมนูที่กำหนดเอง

วัตถุประสงค์

• ทำความเข้าใจว่าโซลูชันทำอะไรได้บ้าง
• ทำความเข้าใจสิ่งที่บริการ Apps Script ทำภายในโซลูชัน
• ตั้งค่าสคริปต์
• เรียกใช้สคริปต์

เกี่ยวกับโซลูชันนี้

การใช้ฟังก์ชันที่กำหนดเองช่วยให้คุณคำนวณระยะทางในการขับรถระหว่าง 2 สถานที่และแปลงระยะทางจากเมตรเป็นไมล์ได้ นอกจากนี้ ระบบอัตโนมัติยังมีเมนูที่กำหนดเองซึ่งช่วยให้คุณเพิ่มเส้นทางแบบทีละขั้นตอนจาก ที่อยู่ต้นทางไปยังที่อยู่ปลายทางในชีตใหม่ได้

วิธีการทำงาน

สคริปต์ใช้ฟังก์ชันที่กำหนดเอง 2 รายการและการทำงานอัตโนมัติ

• ฟังก์ชัน drivingDistance(origin, destination) ใช้บริการ Maps เพื่อ คำนวณ เส้นทางการขับรถระหว่าง 2 สถานที่ และแสดงระยะทางระหว่าง ที่อยู่ทั้ง 2 ในหน่วยเมตร
• ฟังก์ชัน metersToMiles(meters) จะคำนวณจำนวนไมล์ที่เทียบเท่า สำหรับจำนวนเมตรที่ระบุ
• การทำงานอัตโนมัติจะแจ้งให้ผู้ใช้ป้อนแถวของที่อยู่ต้นทางและปลายทางเพื่อคำนวณเส้นทางการขับขี่ และเพิ่มเส้นทางการขับขี่แบบทีละขั้นตอนลงในชีตใหม่

บริการ Apps Script

โซลูชันนี้ใช้บริการต่อไปนี้

• บริการสเปรดชีต - เพิ่มเมนูที่กำหนดเอง เพิ่มข้อมูลตัวอย่างเพื่อทดสอบโซลูชันนี้ และจัดรูปแบบชีตใหม่เมื่อสคริปต์เพิ่มเส้นทางการขับรถ
• บริการพื้นฐาน - ใช้คลาส Browser เพื่อ แจ้งให้ผู้ใช้ป้อนหมายเลขแถวสำหรับเส้นทางและแจ้งเตือนผู้ใช้หาก เกิดข้อผิดพลาด
• บริการสาธารณูปโภค - อัปเดตสตริงที่สร้างจากเทมเพลต ด้วยข้อมูลที่ผู้ใช้ระบุ
• บริการ Maps - รับเส้นทางแบบทีละขั้นตอนของ Google Maps จากที่อยู่ต้นทางไปยังที่อยู่ปลายทาง

ข้อกำหนดเบื้องต้น

หากต้องการใช้ตัวอย่างนี้ คุณต้องมีข้อกำหนดเบื้องต้นต่อไปนี้

• บัญชี Google (บัญชี Google Workspace อาจต้องได้รับการอนุมัติจากผู้ดูแลระบบ)
• เว็บเบราว์เซอร์ที่มีสิทธิ์เข้าถึงอินเทอร์เน็ต

ตั้งค่าสคริปต์

1. ทำสำเนาสเปรดชีตคำนวณระยะทางขับรถและแปลงเมตรเป็นไมล์ โปรเจ็กต์ Apps Script สำหรับ โซลูชันนี้แนบมากับสเปรดชีต
   ทำสำเนา
2. หากต้องการเพิ่มส่วนหัวและข้อมูลตัวอย่างลงในชีต ให้คลิกวิธีการ > เตรียมชีต คุณอาจต้อง รีเฟรชหน้าเว็บเพื่อให้เมนูที่กำหนดเองนี้ปรากฏขึ้น

3. ให้สิทธิ์สคริปต์เมื่อได้รับข้อความแจ้ง หากหน้าจอคำยินยอมของ OAuth แสดงคำเตือนว่า แอปนี้ยังไม่ได้รับการยืนยัน ให้ดำเนินการต่อโดยเลือกขั้นสูง > ไปที่ {ชื่อโปรเจ็กต์} (ไม่ปลอดภัย)

4. คลิกเส้นทาง > เตรียมชีต อีกครั้ง

เรียกใช้สคริปต์

1. ในเซลล์ C2 ให้ป้อนสูตร =DRIVINGDISTANCE(A2,B2) แล้วกด Enter หากคุณอยู่ในสถานที่ที่ใช้คอมมาเป็นจุดทศนิยม คุณอาจต้องป้อน =DRIVINGDISTANCE(A2;B2)แทน
2. ในเซลล์ D2 ให้ป้อนสูตร =METERSTOMILES(C2) แล้วกด Enter
3. (ไม่บังคับ) เพิ่มแถวที่อยู่เริ่มต้นและสิ้นสุดเพิ่มเติม แล้วคัดลอกสูตรในคอลัมน์ C และ D เพื่อคำนวณระยะทางในการขับรถระหว่างสถานที่ต่างๆ
4. คลิกเส้นทาง > สร้างทีละขั้นตอน
5. ในกล่องโต้ตอบ ให้ป้อนหมายเลขแถวของที่อยู่ที่ต้องการ สร้างเส้นทาง แล้วคลิกตกลง
6. ตรวจสอบเส้นทางการขับรถในชีตใหม่ที่สคริปต์สร้างขึ้น

ตรวจสอบโค้ด

หากต้องการตรวจสอบโค้ด Apps Script สำหรับโซลูชันนี้ ให้คลิกดูซอร์สโค้ด ด้านล่าง

ดูซอร์สโค้ด

Code.gs

sheets/customFunctions/customFunctions.gs

ดูใน 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;
}

ผู้ร่วมให้ข้อมูล

ตัวอย่างนี้ได้รับการดูแลโดย Google โดยได้รับความช่วยเหลือจาก Google Developer Expert

ขั้นตอนถัดไป

• ฟังก์ชันที่กำหนดเองใน Google ชีต
• เมนูที่กำหนดเองใน Google Workspace