코딩 수준: 초급
시간: 10분
프로젝트 유형: 커스텀 함수 및 맞춤 메뉴를 사용한 자동화
목표
- 솔루션의 기능을 이해합니다.
- 솔루션 내에서 Apps Script 서비스가 하는 작업을 이해합니다.
- 스크립트를 설정합니다.
- 스크립트를 실행합니다.
이 솔루션 정보
맞춤 함수를 사용하면 두 위치 간의 운전 거리를 계산하고 거리를 미터에서 마일로 변환할 수 있습니다. 추가 자동화는 새 시트에서 출발지 주소부터 도착지 주소까지 단계별 경로를 추가할 수 있는 맞춤 메뉴를 제공합니다.
사용 방법
스크립트는 2개의 맞춤 함수와 자동화를 사용합니다.
drivingDistance(origin, destination)
함수는 지도 서비스를 사용하여 두 위치 간의 운전 경로를 계산하고 두 주소 간의 거리를 미터 단위로 반환합니다.
metersToMiles(meters)
함수는 지정된 미터 수에 상응하는 마일 수를 계산합니다.
- 자동화는 사용자에게 운전 경로를 계산할 출발지와 도착지의 행을 입력하라는 메시지를 표시하고 단계별 운전 경로를 새 시트에 추가합니다.
Apps Script 서비스
이 솔루션은 다음 서비스를 사용합니다.
- 스프레드시트 서비스–맞춤 메뉴를 추가하고, 이 솔루션을 테스트하기 위한 데모 데이터를 추가하고, 스크립트가 운전 경로를 추가할 때 새 시트의 서식을 지정합니다.
- 기본 서비스:
Browser
클래스를 사용하여 사용자에게 경로의 행 번호를 입력하라는 메시지를 표시하고 오류가 발생하면 사용자에게 알립니다.
- 유틸리티 서비스–사용자 지정 정보로 템플릿 문자열을 업데이트합니다.
- 지도 서비스–출발 주소에서 도착지까지의 단계별 Google 지도 경로를 가져옵니다.
기본 요건
이 샘플을 사용하려면 다음과 같은 기본 요건이 필요합니다.
- Google 계정 (Google Workspace 계정은 관리자 승인이 필요할 수 있음)
- 인터넷에 액세스할 수 있는 웹브라우저
스크립트 설정
- 운전 거리 계산 및 미터를 마일로 변환 스프레드시트의 사본을 만듭니다. 이 솔루션의 Apps Script 프로젝트는 스프레드시트에 첨부되어 있습니다.
사본 만들기
- 시트에 헤더와 데모 데이터를 추가하려면 경로
> 시트 준비를 클릭합니다. 이 맞춤 메뉴를 표시하려면 페이지를 새로고침해야 할 수 있습니다.
메시지가 표시되면 스크립트를 승인합니다.
OAuth 동의 화면에 이 앱이 확인되지 않았습니다라는 경고가 표시되면 고급 >
{프로젝트 이름}으로 이동(안전하지 않음)을 선택하여 계속 진행합니다.
경로 > 시트 준비를 다시 클릭합니다.
스크립트 실행
C2
셀에 =DRIVINGDISTANCE(A2,B2)
수식을 입력하고 Enter 키를 누릅니다.
쉼표를 사용하는 위치에 있다면 =DRIVINGDISTANCE(A2;B2)
를 대신 입력해야 할 수도 있습니다.
D2
셀에 =METERSTOMILES(C2)
수식을 입력하고 Enter 키를 누릅니다.
- (선택사항) 출발지와 도착지 행을 더 추가하고
C
및 D
열의 수식을 복사하여 여러 장소 간의 운전 거리를 계산합니다.
- 경로 >
단계별 생성을 클릭합니다.
- 대화상자에서 경로를 생성할 주소의 행 번호를 입력하고 확인을 클릭합니다.
- 스크립트가 만드는 새 시트에서 운전경로를 검토합니다.
코드 검토
이 솔루션의 Apps Script 코드를 검토하려면 아래의 소스 코드 보기를 클릭합니다.
소스 코드 보기
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 Developer Expert의 도움을 받아 Google에서 관리합니다.
다음 단계