编码级别:初级
时长:10 分钟(
)
项目类型:自定义函数和
通过自定义菜单实现自动化
目标
- 了解此解决方案的用途。
- 了解 Apps 脚本服务在
解决方案。
- 设置脚本。
- 运行脚本。
关于此解决方案
使用自定义函数,您可以计算
并将距离从米换算为英里。额外
自动化功能提供了一个自定义菜单,让您可以从
将起始地址添加到新工作表中的结束地址。
![工作表中行车路线的屏幕截图。](https://developers.google.cn/static/apps-script/samples/images/calculate-driving-distance.png?hl=zh-cn)
工作原理
该脚本使用了 2 个自定义函数和 1 个自动化操作。
drivingDistance(origin, destination)
函数使用地图服务
计算
两个位置之间的行车路线,并返回两个位置之间的距离
表示两个地址(以米为单位)。
metersToMiles(meters)
函数计算等效的英里数
表示给定的米数。
- 自动化操作会提示用户输入
行起点和终点地址,用于计算 和 的行车路线
用于将分步行车路线添加到新工作表中。
Apps 脚本服务
此解决方案使用以下服务:
- 电子表格服务 - 添加自定义
菜单、添加演示数据以测试此解决方案,并在
脚本将添加行车路线。
- 基础服务 - 使用
Browser
类执行以下操作
提示用户输入路线的行号,并提醒用户
发生错误
- 实用工具服务 - 更新模板化字符串
包含用户指定的信息
- 地图服务 - 获取分步 Google 地图
从起点地址到终点地址的路线。
前提条件
如需使用此示例,您需要满足以下前提条件:
- Google 账号(Google Workspace 账号可能
需要管理员批准)。
- 可以访问互联网的网络浏览器。
设置脚本
- 复制计算行车距离并将米换算为英里
电子表格。Apps 脚本项目
此解决方案会附加到电子表格中。
复制
- 要在工作表中添加标题和演示数据,请点击路线
> 准备工作表。您可能需要
刷新页面即可显示此自定义菜单。
出现提示时,为脚本授权。
如果 OAuth 同意屏幕显示以下警告:“此应用未经验证”,
选择高级 > 以继续
前往“{Project Name}”(不安全)。
依次点击路线 > 准备表格
。
运行脚本
- 在单元格
C2
中输入公式 =DRIVINGDISTANCE(A2,B2)
,然后按 Enter 键。
如果您所在位置使用小数逗号,您可能需要在
=DRIVINGDISTANCE(A2;B2)
。
- 在单元格
D2
中输入公式 =METERSTOMILES(C2)
,然后按 Enter 键。
- (可选)添加其他出发地和目的地行,并复制
C
和 D
列中的公式,用于计算两者之间的行驶距离
不同位置。
- 点击路线 >
逐步生成。
- 在对话框中,输入目标地址的行号
生成路线,然后点击确定。
- 在脚本创建的新工作表中查看行车路线。
查看代码
如需查看此解决方案的 Apps 脚本代码,请点击查看源代码
如下:
查看源代码
Code.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;
}
贡献者
此示例由 Google 在 Google 开发者专家的帮助下进行维护。
后续步骤