Google 스프레드시트용 Google 지도 수식

게시 됨: 2022-02-26

코딩 없이 간단한 수식을 사용하여 Google 지도의 강력한 기능을 Google 스프레드시트로 가져올 수 있습니다. Google Maps API에 가입할 필요가 없으며 Google 지도의 모든 결과가 시트에 캐시되므로 할당량 한도에 도달하지 않을 것입니다.

예를 들어 A열에 시작 주소가 있고 B열에 목적지 주소가 있는 경우 =GOOGLEMAPS_DISTANCE(A1, B1, "driving") 와 같은 공식이 두 지점 사이의 거리를 빠르게 계산합니다.

또는 수식을 약간 수정하여 =GOOGLEMAPS_TIME(A1, B1, "walking") 한 지점에서 다른 지점까지 사람이 걷는 데 걸리는 시간을 알 수 있습니다.

기술적인 세부 사항을 다루지 않고 Google 지도 공식을 시도하고 싶다면 이 Google 시트를 복사하면 모든 준비가 완료됩니다.

Google Maps in Google Sheets

Google 스프레드시트 내에서 Google 지도 사용

이 튜토리얼에서는 다음과 같은 도움이 되는 Google 스프레드시트 내에서 맞춤 Google 지도 기능을 쉽게 작성하는 방법을 설명합니다.

  1. 두 도시 또는 주소 간의 거리를 계산합니다.
  2. 두 지점 사이의 이동 시간(도보, 운전 또는 자전거)을 계산합니다.
  3. Google 지도에서 모든 주소의 위도 및 경도 좌표를 가져옵니다.
  4. 역 지오코딩을 사용하여 GPS 좌표에서 우편 주소를 찾습니다.
  5. 지구상의 모든 지점 사이의 운전 경로를 인쇄하십시오.
  6. 우편 번호 자체에서 주소를 가져옵니다.

1. Google 스프레드시트에서 거리 계산

출발지, 목적지, 이동 모드(도보 또는 운전)를 지정하면 두 지점 사이의 거리(마일)가 반환됩니다.

=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")

 /** * Calculate the distance between two * locations on Google Maps. * * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The distance in miles * @customFunction */ const GOOGLEMAPS_DISTANCE = ( origin , destination , mode ) => { const { routes : [ data ] = [ ] } = Maps . newDirectionFinder ( ) . setOrigin ( origin ) . setDestination ( destination ) . setMode ( mode ) . getDirections ( ) ; if ( ! data ) { throw new Error ( 'No route found!' ) ; } const { legs : [ { distance : { text : distance } } = { } ] = [ ] } = data ; return distance ; } ;

2. Google 스프레드시트의 역 지오코딩

위도와 경도를 지정하고 좌표의 역 지오코딩을 통해 지점의 전체 주소를 가져옵니다.

=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")

 /** * Use Reverse Geocoding to get the address of * a point location (latitude, longitude) on Google Maps. * * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude) * * @param {String} latitude The latitude to lookup. * @param {String} longitude The longitude to lookup. * @return {String} The postal address of the point. * @customFunction */ const GOOGLEMAPS_REVERSEGEOCODE = ( latitude , longitude ) => { const { results : [ data = { } ] = [ ] } = Maps . newGeocoder ( ) . reverseGeocode ( latitude , longitude ) ; return data . formatted_address ; } ;

3. 주소의 GPS 좌표 가져오기

Google 지도에서 모든 주소의 위도와 경도를 가져옵니다.

=GOOGLEMAPS_LATLONG("10 Hanover Square, NY")

 /** * Get the latitude and longitude of any * address on Google Maps. * * =GOOGLEMAPS_LATLONG("10 Hanover Square, NY") * * @param {String} address The address to lookup. * @return {String} The latitude and longitude of the address. * @customFunction */ const GOOGLEMAPS_LATLONG = ( address ) => { const { results : [ data = null ] = [ ] } = Maps . newGeocoder ( ) . geocode ( address ) ; if ( data === null ) { throw new Error ( 'Address not found!' ) ; } const { geometry : { location : { lat , lng } } = { } } = data ; return ` ${ lat } , ${ lng } ` ; } ;

4. 주소간 운전경로 출력

출발지 주소, 목적지 주소, 이동 모드를 지정하고 기능은 Google Maps API를 사용하여 단계별 운전 경로를 인쇄합니다.

=GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking")

 /** * Find the driving direction between two * locations on Google Maps. * * =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The driving direction * @customFunction */ const GOOGLEMAPS_DIRECTIONS = ( origin , destination , mode = 'driving' ) => { const { routes = [ ] } = Maps . newDirectionFinder ( ) . setOrigin ( origin ) . setDestination ( destination ) . setMode ( mode ) . getDirections ( ) ; if ( ! routes . length ) { throw new Error ( 'No route found!' ) ; } return routes . map ( ( { legs } ) => { return legs . map ( ( { steps } ) => { return steps . map ( ( step ) => { return step . html_instructions . replace ( / <[^>]+> / g , '' ) ; } ) ; } ) ; } ) . join ( ', ' ) ; } ;

5. Google 지도로 이동 시간 측정

출발지 주소, 목적지 주소, 이동 모드를 지정하면 경로가 존재하는 경우 지정된 주소 간의 대략적인 이동 시간을 측정할 수 있습니다.

=GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")

 /** * Calculate the travel time between two locations * on Google Maps. * * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The time in minutes * @customFunction */ const GOOGLEMAPS_DURATION = ( origin , destination , mode = 'driving' ) => { const { routes : [ data ] = [ ] } = Maps . newDirectionFinder ( ) . setOrigin ( origin ) . setDestination ( destination ) . setMode ( mode ) . getDirections ( ) ; if ( ! data ) { throw new Error ( 'No route found!' ) ; } const { legs : [ { duration : { text : time } } = { } ] = [ ] } = data ; return time ; } ;

스프레드시트의 Google 지도 기능

팁: 결과를 캐싱하여 성능 향상

위의 모든 Google 스프레드시트 기능은 내부적으로 Google Maps API를 사용하여 경로, 거리 및 이동 시간을 계산합니다. Google은 지도 작업에 대해 제한된 할당량을 제공하며 시트가 단기간에 너무 많은 쿼리를 수행하는 경우 ""서비스가 하루 동안 너무 많이 호출되었습니다." 또는 이와 유사한 오류가 표시될 수 있습니다.

이 문제를 해결하려면 Apps Script의 내장 캐시를 사용하여 결과를 저장하는 것이 좋습니다. 함수의 결과가 이미 케이스에 있는 경우 Google 지도에 요청을 한 번 덜 만듭니다. Google Sheet에서도 캐싱을 사용하며 이를 구현하는 방법은 다음과 같습니다.

 // The cache key for "New York" and "new york " should be same const md5 = ( key = '' ) => { const code = key . toLowerCase ( ) . replace ( / \s / g , '' ) ; return Utilities . computeDigest ( Utilities . DigestAlgorithm . MD5 , key ) . map ( ( char ) => ( char + 256 ) . toString ( 16 ) . slice ( - 2 ) ) . join ( '' ) ; } ; const getCache = ( key ) => { return CacheService . getDocumentCache ( ) . get ( md5 ( key ) ) ; } ; // Store the results for 6 hours const setCache = ( key , value ) => { const expirationInSeconds = 6 * 60 * 60 ; CacheService . getDocumentCache ( ) . put ( md5 ( key ) , value , expirationInSeconds ) ; } ; /** * Calculate the travel time between two locations * on Google Maps. * * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking") * * @param {String} origin The address of starting point * @param {String} destination The address of destination * @param {String} mode The mode of travel (driving, walking, bicycling or transit) * @return {String} The time in minutes * @customFunction */ const GOOGLEMAPS_DURATION = ( origin , destination , mode = 'driving' ) => { const key = [ 'duration' , origin , destination , mode ] . join ( ',' ) ; // Is result in the internal cache? const value = getCache ( key ) ; // If yes, serve the cached result if ( value !== null ) return value ; const { routes : [ data ] = [ ] } = Maps . newDirectionFinder ( ) . setOrigin ( origin ) . setDestination ( destination ) . setMode ( mode ) . getDirections ( ) ; if ( ! data ) { throw new Error ( 'No route found!' ) ; } const { legs : [ { duration : { text : time } } = { } ] = [ ] } = data ; // Store the result in internal cache for future setCache ( key , time ) ; return time ; } ;

참조: 이메일 및 문서에 Google 지도 포함