חישוב מרחק הנסיעה - המרת מטרים למיילים

רמת תכנות: למתחילים
משך: 10 דקות
סוג הפרויקט: פונקציה מותאמת אישית וגם פעולות אוטומטיות באמצעות תפריט בהתאמה אישית

מטרות

• להבין מה הפתרון עושה.
• להבין מה שירותי Apps Script עושים במסגרת לפתרון הבעיה.
• מגדירים את הסקריפט.
• מריצים את הסקריפט.

מידע על הפתרון הזה

באמצעות פונקציות מותאמות אישית אפשר לחשב את מרחק הנסיעה בין שני אנשים וממירים את המרחק ממטרים למיילים. אפשרות נוספת אוטומטי מספק תפריט מותאם אישית שמאפשר לכם להוסיף מסלול מפורט מכתובת ההתחלה לכתובת הסופית בגיליון חדש.

איך זה עובד

הסקריפט משתמש בשתי פונקציות מותאמות אישית ובפעולה אוטומטית.

• הפונקציה drivingDistance(origin, destination) משתמשת בשירות מפות Google כדי לחשב את מסלול הנסיעה בין שני מיקומים והחזרת המרחק ביניהם שתי הכתובות במטרים.
• הפונקציה metersToMiles(meters) מחשבת את מספר המיילים המקביל. למספר מטרים נתון.
• הפעולה האוטומטית תבקש מהמשתמש להזין שורה של כתובות התחלה וסיום כדי לחשב את מסלול הנסיעה עבור מוסיף את מסלול הנסיעה המפורט לגיליון חדש.

שירותי Apps Script

הפתרון הזה משתמש בשירותים הבאים:

• שירות גיליונות אלקטרוניים – הוספת הקבצים המותאמים אישית מוסיף נתונים להדגמה כדי לבדוק את הפתרון הזה, ועיצוב הגיליונות החדשים הסקריפט מוסיף מסלול נסיעה.
• שירות בסיס – נעשה שימוש במחלקה Browser כדי להנחות את המשתמש להזין מספר שורה לקבלת מסלול, ולהתריע בפני המשתמש אם מתרחשת שגיאה.
• שירות תשתיות – עדכון מחרוזות בתבניות עם מידע שהמשתמש ציין.
• שירות מפות – קבלת מפות Google שלב אחרי שלב מסלול מכתובת ההתחלה לכתובת הסיום.

דרישות מוקדמות

כדי להשתמש בדוגמה הזו, צריך לעמוד בדרישות המוקדמות הבאות:

• חשבון Google (חשבונות Google Workspace עשויים נדרש אישור של האדמין).
• דפדפן אינטרנט עם גישה לאינטרנט.

הגדרת הסקריפט

1. יוצרים עותק של חישוב מרחק נסיעה והמרת מטרים למיילים. גיליון אלקטרוני. פרויקט Apps Script עבור הפתרון הזה מצורף לגיליון האלקטרוני.
   יצירת עותק
2. כדי להוסיף כותרות ונתונים להדגמה (דמו) לגיליון, לוחצים על מסלול > הכנת הגיליון. יכול להיות שתצטרכו צריך לרענן את הדף כדי שהתפריט המותאם אישית יופיע.

3. כשמוצגת הנחיה, מאשרים את הסקריפט. אם במסך ההסכמה של OAuth מוצגת האזהרה האפליקציה הזו לא מאומתת כדי להמשיך, בוחרים באפשרות מתקדם >. עוברים אל {Project Name} (לא בטוח).

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.

השלבים הבאים

• פונקציות מותאמות אישית ב-Google Sheets
• תפריטים בהתאמה אישית ב-Google Workspace