Формулы Google Maps для Google Sheets

Опубликовано: 2022-02-26

Вы можете применить возможности Google Maps к своим таблицам Google, используя простые формулы без кодирования. Вам не нужно регистрироваться в Google Maps API, и все результаты из Google Maps кэшируются на листе, поэтому вы вряд ли достигнете каких-либо квот.

Чтобы дать вам краткий пример, если у вас есть начальный адрес в столбце A и адрес назначения в столбце B, формула вроде =GOOGLEMAPS_DISTANCE(A1, B1, "driving") быстро рассчитает расстояние между двумя точками.

Или немного измените формулу =GOOGLEMAPS_TIME(A1, B1, "walking") , чтобы узнать, сколько времени потребуется человеку, чтобы пройти от одной точки до другой.

Если вы хотите попробовать формулы Google Maps, не вдаваясь в технические детали, просто сделайте копию этой таблицы Google, и все готово.

Google Maps in Google Sheets

Использование Google Maps внутри Google Sheets

В этом руководстве объясняется, как легко создавать собственные функции Google Maps в Google Sheets, которые помогут вам:

  1. Рассчитать расстояния между двумя городами или любыми адресами.
  2. Рассчитайте время в пути (пешком, на машине или на велосипеде) между двумя точками.
  3. Получите координаты широты и долготы любого адреса на Картах Google.
  4. Используйте обратное геокодирование, чтобы найти почтовый адрес по GPS-координатам.
  5. Печатайте маршруты проезда между любыми точками на земле.
  6. Получите адрес из самого почтового индекса.

1. Расчет расстояний в Google Sheets

Укажите пункт отправления, пункт назначения, способ передвижения (пешком или на автомобиле), и функция вернет расстояние между двумя точками в милях.

=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 Sheets

Укажите широту и долготу и получите полный адрес точки через обратное геокодирование координат.

=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. Распечатайте маршруты проезда между адресами.

Укажите исходный адрес, адрес назначения, режим передвижения, и функция будет использовать API Google Maps для печати пошаговых маршрутов проезда.

=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 Maps

Укажите адрес отправления, адрес назначения, способ передвижения, и функция измерит приблизительное время поездки между указанными адресами при наличии маршрута.

=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 Maps в Таблицах

Совет: повысьте производительность за счет кэширования результатов

Все вышеперечисленные функции Google Sheets внутренне используют API Google Maps для расчета маршрутов, расстояний и времени в пути. Google предлагает ограниченную квоту для операций Карт, и если ваша таблица выполняет слишком много запросов за короткий промежуток времени, вы, вероятно, увидите такие ошибки, как «Служба вызывалась слишком много раз в течение одного дня» или что-то подобное.

Чтобы обойти эту проблему, рекомендуется использовать встроенный кеш Apps Script для хранения результатов и, если результаты функции уже существуют в этом случае, вы будете делать на один запрос к Google Maps меньше функций Maps внутри этого 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 в сообщения электронной почты и документы.