Fórmulas do Google Maps para Planilhas Google

Publicados: 2022-02-26

Você pode trazer o poder do Google Maps para o Planilhas Google usando fórmulas simples sem codificação. Você não precisa se inscrever na API do Google Maps e todos os resultados do Google Maps são armazenados em cache na planilha, portanto, é improvável que você atinja os limites de cota.

Para dar um exemplo rápido, se você tiver o endereço inicial na coluna A e o endereço de destino na coluna B, uma fórmula como =GOOGLEMAPS_DISTANCE(A1, B1, "driving") calculará rapidamente a distância entre os dois pontos.

Ou modifique ligeiramente a fórmula =GOOGLEMAPS_TIME(A1, B1, "walking") para saber quanto tempo levará para uma pessoa caminhar de um ponto a outro.

Se você quiser experimentar as fórmulas do Google Maps sem entrar nos detalhes técnicos, basta fazer uma cópia desta Planilha Google e pronto.

Google Maps in Google Sheets

Usando o Google Maps dentro do Planilhas Google

Este tutorial explica como você pode escrever facilmente funções personalizadas do Google Maps dentro do Planilhas Google que o ajudarão a:

  1. Calcule distâncias entre duas cidades ou qualquer endereço.
  2. Calcule o tempo de viagem (a pé, de carro ou de bicicleta) entre dois pontos.
  3. Obtenha as coordenadas de latitude e longitude de qualquer endereço no Google Maps.
  4. Use geocodificação reversa para encontrar o endereço postal das coordenadas do GPS.
  5. Imprima instruções de direção entre quaisquer pontos da Terra.
  6. Obtenha o endereço do próprio CEP.

1. Calcular distâncias no Planilhas Google

Especifique a origem, o destino, o modo de viagem (a pé ou dirigindo) e a função retornará a distância entre os dois pontos em milhas.

=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. Geocodificação reversa no Planilhas Google

Especifique a latitude e longitude e obtenha o endereço completo do ponto através da geocodificação reversa de coordenadas.

=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. Obtenha as coordenadas GPS de um endereço

Obtenha a latitude e longitude de qualquer endereço no Google Maps.

=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. Imprima as instruções de direção entre os endereços

Especifique o endereço de origem, o endereço de destino, o modo de viagem e a função usará a API do Google Maps para imprimir instruções de direção passo a passo.

=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. Meça o tempo de viagem com o Google Maps

Especifique o endereço de origem, o endereço de destino, o modo de viagem e a função medirá seu tempo de viagem aproximado entre os endereços especificados, desde que exista uma rota.

=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 ; } ;

Funções do Google Maps no Planilhas

Dica: melhore o desempenho armazenando em cache os resultados

Todas as funções do Google Sheets acima usam internamente a API do Google Maps para calcular rotas, distâncias e tempo de viagem. O Google oferece uma cota limitada para operações do Maps e, se sua planilha realizar muitas consultas em um curto período, é provável que você veja erros como ""Serviço invocado muitas vezes em um dia" ou algo semelhante.

Para contornar esse problema, é recomendável que você use o cache interno do Apps Script para armazenar os resultados e, se os resultados de uma função já existirem no caso, você fará uma solicitação a menos ao Google Maps. O Google Sheet também usa cache e veja como você pode implementá-lo.

 // 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 ; } ;

Veja também: Incorporar o Google Maps em e-mails e documentos