Google Maps-Formeln für Google Sheets

Veröffentlicht: 2022-02-26

Sie können die Leistung von Google Maps mit einfachen Formeln ohne Codierung in Ihre Google-Tabellen bringen. Sie müssen sich nicht für die Google Maps-API anmelden und alle Ergebnisse von Google Maps werden in der Tabelle zwischengespeichert, sodass Sie wahrscheinlich keine Kontingentgrenzen erreichen.

Um Ihnen ein kurzes Beispiel zu geben: Wenn Sie die Startadresse in Spalte A und die Zieladresse in Spalte B haben, berechnet eine Formel wie =GOOGLEMAPS_DISTANCE(A1, B1, "driving") schnell die Entfernung zwischen den beiden Punkten.

Oder ändern Sie die Formel leicht =GOOGLEMAPS_TIME(A1, B1, "walking") , um zu wissen, wie lange es dauert, bis eine Person von einem Punkt zum anderen geht.

Wenn Sie die Google Maps-Formeln ausprobieren möchten, ohne auf die technischen Details einzugehen, erstellen Sie einfach eine Kopie dieses Google Sheets und schon sind Sie fertig.

Google Maps in Google Sheets

Verwenden von Google Maps in Google Sheets

Dieses Tutorial erklärt, wie Sie ganz einfach benutzerdefinierte Google Maps-Funktionen in Google Sheets schreiben können, die Ihnen helfen:

  1. Berechnen Sie Entfernungen zwischen zwei Städten oder beliebigen Adressen.
  2. Berechnen Sie die Reisezeit (zu Fuß, mit dem Auto oder mit dem Fahrrad) zwischen zwei Punkten.
  3. Holen Sie sich die Breiten- und Längenkoordinaten jeder Adresse auf Google Maps.
  4. Verwenden Sie Reverse Geocoding, um die Postanschrift aus GPS-Koordinaten zu finden.
  5. Drucken Sie Wegbeschreibungen zwischen beliebigen Punkten auf der Erde.
  6. Holen Sie sich die Adresse aus der Postleitzahl selbst.

1. Berechnen Sie Entfernungen in Google Sheets

Geben Sie den Ausgangspunkt, das Ziel, den Reisemodus (zu Fuß oder mit dem Auto) an und die Funktion gibt die Entfernung zwischen den beiden Punkten in Meilen zurück.

=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. Umgekehrte Geocodierung in Google Sheets

Geben Sie den Breiten- und Längengrad an und erhalten Sie die vollständige Adresse des Punktes durch umgekehrte Geokodierung der Koordinaten.

=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. Holen Sie sich die GPS-Koordinaten einer Adresse

Holen Sie sich den Breiten- und Längengrad einer beliebigen Adresse auf 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. Drucken Sie die Wegbeschreibungen zwischen den Adressen aus

Geben Sie die Ausgangsadresse, die Zieladresse, den Reisemodus an und die Funktion verwendet die Google Maps-API, um Schritt-für-Schritt-Wegbeschreibungen zu drucken.

=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. Fahrzeit mit Google Maps messen

Geben Sie die Startadresse, die Zieladresse, den Reisemodus an und die Funktion misst Ihre ungefähre Fahrzeit zwischen den angegebenen Adressen, sofern eine Route vorhanden ist.

=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-Funktionen in Tabellen

Tipp: Verbessern Sie die Leistung, indem Sie Ergebnisse zwischenspeichern

Alle oben genannten Google Sheets-Funktionen verwenden intern die Google Maps-API, um Routen, Entfernungen und Reisezeiten zu berechnen. Google bietet ein begrenztes Kontingent für Maps-Operationen an, und wenn Ihr Blatt in kurzer Zeit zu viele Abfragen durchführt, sehen Sie wahrscheinlich Fehler wie ""Dienst wurde zu oft für einen Tag aufgerufen" oder ähnliches.

Um dieses Problem zu umgehen, wird empfohlen, den integrierten Cache von Apps Script zum Speichern von Ergebnissen zu verwenden und, falls die Ergebnisse einer Funktion bereits vorhanden sind, eine Anfrage weniger an Google Maps zu stellen. Die darin enthaltenen Maps-Funktionen Google Sheet verwendet auch Caching und hier erfahren Sie, wie Sie es implementieren können.

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

Siehe auch: Google Maps in E-Mails und Dokumente einbetten