{"id":11129,"date":"2023-03-18T09:48:24","date_gmt":"2023-03-18T08:48:24","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=11129"},"modified":"2023-03-18T10:02:43","modified_gmt":"2023-03-18T09:02:43","slug":"google-maps","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=11129","title":{"rendered":"Excel : Obtenir les distances et temps de parcours d&#8217;un trajet Google Maps"},"content":{"rendered":"<p style=\"text-align: justify;\">Le <strong>calcul d\u2019itin\u00e9raires<\/strong>, de temps de parcours ou de distances entre deux adresses, quel que soit le mode de transport retenu (voiture, \u00e0 pied, v\u00e9lo, train\u2026), est utile pour planifier un d\u00e9placement chez un client, valider une note de frais\u2026 Ces calculs peuvent \u00eatre r\u00e9alis\u00e9s \u00e0 l&#8217;aide de sites internet comme Mappy ou <strong>Google Maps<\/strong>. Ce dernier dispose d&#8217;une <strong>API<\/strong> qui permet de <strong>collecter le r\u00e9sultat<\/strong> des calculs (distances et temps de parcours) directement dans un tableau Excel.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9357\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Google-Maps-Caen-Lisieux.png\" alt=\"Google Maps Caen Lisieux\" width=\"1102\" height=\"704\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Google-Maps-Caen-Lisieux.png 1102w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Google-Maps-Caen-Lisieux-300x192.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Google-Maps-Caen-Lisieux-1024x654.png 1024w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Google-Maps-Caen-Lisieux-768x491.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Google-Maps-Caen-Lisieux-730x466.png 730w\" sizes=\"auto, (max-width: 1102px) 100vw, 1102px\" \/><\/p>\n<p style=\"text-align: justify;\">Une API assure l\u2019<strong>interface entre deux syst\u00e8mes<\/strong>. Par exemple, les logiciels de paye t\u00e9l\u00e9transmettent les D\u00e9clarations Sociales Nominatives (DSN) vers les serveurs de l\u2019\u00c9tat par l\u2019interm\u00e9diaire d\u2019une API. Le fonctionnement d\u2019une API est simple. Un logiciel envoie une <strong>requ\u00eate \u00e0 un site internet<\/strong> ou \u00e0 un autre logiciel par le biais de son API. Le site internet ex\u00e9cute la requ\u00eate, interroge ses bases de donn\u00e9es et renvoie le r\u00e9sultat au logiciel, qui int\u00e8gre les donn\u00e9es re\u00e7ues dans son processus de calcul. Les API assurent ainsi l\u2019interaction entre des logiciels d\u2019\u00e9diteurs diff\u00e9rents.<\/p>\n<p style=\"text-align: justify;\">L&#8217;acc\u00e8s \u00e0 l&#8217;API Google Maps est payant. Documentation en ligne : <a href=\"https:\/\/developers.google.com\/maps\/documentation\/javascript\/distancematrix?hl=f\" target=\"_blank\" rel=\"noopener\">https:\/\/developers.google.com\/maps\/documentation\/javascript\/distancematrix?hl=f<\/a><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11130\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLEAPIS.png\" alt=\"GOOGLEAPIS\" width=\"1357\" height=\"269\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLEAPIS.png 1357w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLEAPIS-300x59.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLEAPIS-1024x203.png 1024w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLEAPIS-768x152.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLEAPIS-730x145.png 730w\" sizes=\"auto, (max-width: 1357px) 100vw, 1357px\" \/><\/p>\n<p style=\"text-align: justify;\">C&#8217;est la <strong>fonction Excel SERVICEWEB(url)<\/strong> qui interroge un site internet (API) et collecte le r\u00e9sultat de la requ\u00eate sous la forme d&#8217;une cha\u00eene de caract\u00e8res. Le param\u00e8tre URL correspond \u00e0 la <strong>requ\u00eate HTTP<\/strong>. Dans le cas de l&#8217;API Google Maps, la requ\u00eate est constitu\u00e9e :<\/p>\n<ul>\n<li>de l&#8217;adresse internet de l&#8217;API : https:\/\/maps.googleapis.com\/maps\/api\/distancematrix\/<\/li>\n<li>des param\u00e8tres origins= et destinations= suivis respectivement de l&#8217;adresse de d\u00e9part et d&#8217;arriv\u00e9e de l&#8217;itin\u00e9raire<\/li>\n<li>du param\u00e8tre mode= suivi du mode de locomotion voulu : driving (voiture), walking (pi\u00e9ton), bicycling (v\u00e9lo)<\/li>\n<li>du param\u00e8tre key= suivi de la clef API communiqu\u00e9e par Google (payant) ; en l&#8217;absence de clef, l&#8217;API retournera un message d&#8217;erreur (<em>You must use an API key to authenticate each request to Google Maps Platform APIs<\/em>)<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Exemples de calculs de trajets :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11134\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLE-MAPS-CALCULS.png\" alt=\"GOOGLE MAPS CALCULS\" width=\"593\" height=\"238\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLE-MAPS-CALCULS.png 593w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2023\/02\/GOOGLE-MAPS-CALCULS-300x120.png 300w\" sizes=\"auto, (max-width: 593px) 100vw, 593px\" \/><\/p>\n<p style=\"text-align: justify;\">Exemple de requ\u00eate :<\/p>\n<p style=\"text-align: center;\">=SERVICEWEB(&#8220;https:\/\/maps.googleapis.com\/maps\/api\/distancematrix\/xml?origins=&#8221;&amp;A4&amp;&#8221; &#8220;&amp;B4&amp;&#8221;&amp;destinations=&#8221;&amp;A5&amp;&#8221; &#8220;&amp;B5&amp;&#8221;&amp;mode=driving&amp;key=xxxxxxxx&#8221;)<\/p>\n<p style=\"text-align: justify;\">L&#8217;adresse de d\u00e9part est constitu\u00e9e de la concat\u00e9nation (<strong><a href=\"https:\/\/www.auditsi.eu\/?p=10419\">op\u00e9rateur de concat\u00e9nation<\/a> = &amp;<\/strong>, \u00e0 prononcer esperluette) de la rue (ici en cellule A4) et de la ville (ici B4). L&#8217;adresse de destination est obtenue de la m\u00eame mani\u00e8re. Le mode de transport est ici un v\u00e9hicule automobile (<em>driving<\/em>).<\/p>\n<p style=\"text-align: justify;\">R\u00e9sultat de la requ\u00eate pour un trajet d\u00e9part 8 rue L\u00e9opold S\u00e9dar Senghor, 14460 COLOMBELLES et arriv\u00e9e 13 rue du Centre, 14730 GIBERVILLE :<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"xml\">\"&lt;?xml version=\"\"1.0\"\" encoding=\"\"UTF-8\"\"?&gt;\r\n&lt;DistanceMatrixResponse&gt;\r\n &lt;status&gt;OK&lt;\/status&gt;\r\n &lt;origin_address&gt;8 Rue L\u00e9opold S\u00e9dar-Senghor, 14460 Colombelles, France&lt;\/origin_address&gt;\r\n &lt;destination_address&gt;13 Rue du Ctre, 14730 Giberville, France&lt;\/destination_address&gt;\r\n &lt;row&gt;\r\n  &lt;element&gt;\r\n   &lt;status&gt;OK&lt;\/status&gt;\r\n   &lt;duration&gt;\r\n    &lt;value&gt;296&lt;\/value&gt;\r\n    &lt;text&gt;5 mins&lt;\/text&gt;\r\n   &lt;\/duration&gt;\r\n   &lt;distance&gt;\r\n    &lt;value&gt;2650&lt;\/value&gt;\r\n    &lt;text&gt;2.6 km&lt;\/text&gt;\r\n   &lt;\/distance&gt;\r\n  &lt;\/element&gt;\r\n &lt;\/row&gt;\r\n&lt;\/DistanceMatrixResponse&gt;\r\n\"\r\n<\/pre>\n<p style=\"text-align: justify;\">Le r\u00e9sultat de la requ\u00eate est retranscrit au <strong>format XML<\/strong>. Il comprend les adresses de d\u00e9part et de destination telles qu&#8217;interpr\u00e9t\u00e9es par Google Maps, le statut de la requ\u00eate (OK ou erreur) et le temps et la distance de parcours calcul\u00e9s par Google Maps.<\/p>\n<p style=\"text-align: justify;\">Les champs de donn\u00e9es sont identifi\u00e9s par des <strong>balises<\/strong> dispos\u00e9es en amont (&lt;nom_du_champ&gt;) et en aval (&lt;\/nom_du_champ&gt;).<\/p>\n<p style=\"text-align: justify;\">Pour isoler les donn\u00e9es souhait\u00e9es, il est possible de les filtrer avec la fonction Excel <strong>FILTRE.XML(Cha\u00eeneXML,XPath)<\/strong>. La fonction FILTRE.XML fait partie des nouvelles fonctions d&#8217;Excel (au m\u00eame titre que <a href=\"https:\/\/www.auditsi.eu\/?p=10572\">RECHERCHEX, FILTRE, TRIER<\/a>&#8230;). La param\u00e8tre XPath correspond au chemin d&#8217;acc\u00e8s \u00e0 la donn\u00e9e XML (arborescence de balises).<\/p>\n<p style=\"text-align: justify;\">Par exemple, le chemin d&#8217;acc\u00e8s XPath au <strong>temps de parcours<\/strong> (<em>duration<\/em> en anglais) :<\/p>\n<ul>\n<li>\/DistanceMatrixResponse\/row\/element\/duration\/text correspond \u00e0 la valeur 5 mins (cf r\u00e9sultat XML ci-avant)<\/li>\n<li>\/DistanceMatrixResponse\/row\/element\/duration\/value correspond \u00e0 la valeur 296, soit le temps de trajet en secondes (soit pr\u00e8s de cinq minutes&#8230;)<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">De m\u00eame pour la <strong>distance<\/strong> \u00e0 parcourir :<\/p>\n<ul>\n<li>\/DistanceMatrixResponse\/row\/element\/distance\/text correspond \u00e0 la valeur 2.6 km<\/li>\n<li>\/DistanceMatrixResponse\/row\/element\/distance\/value correspond \u00e0 la valeur 2650, soit 2 650 m\u00e8tres.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Autres chemins XPath int\u00e9ressants :<\/p>\n<ul>\n<li>\/DistanceMatrixResponse\/ : renvoie le statut de la requ\u00eate ; OK si le trajet a pu \u00eatre calcul\u00e9 (adresses de d\u00e9part et d&#8217;arriv\u00e9e identifi\u00e9es)<\/li>\n<li>\/DistanceMatrixResponse\/Origin_address et \/DistanceMatrixResponse\/Destination_address : correspondent respectivement aux adresses de d\u00e9part et d&#8217;arriv\u00e9es telles que reconnues par Google Maps.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Ainsi pour obtenir dans Excel la <strong>distance du trajet (en km) avec la fonction FILTRE.XML<\/strong>, il faut r\u00e9diger la formule de calcul :<\/p>\n<p style=\"text-align: center;\">=FILTRE.XML(C5;&#8221;\/DistanceMatrixResponse\/row\/element\/distance\/value&#8221;)\/1000<\/p>\n<p>De m\u00eame, pour le <strong>temps de parcours en minutes<\/strong> :<\/p>\n<p style=\"text-align: center;\">=FILTRE.XML(C5;&#8221;\/DistanceMatrixResponse\/row\/element\/duration\/value&#8221;)\/60<\/p>\n<p style=\"text-align: justify;\">Le calcul d\u2019itin\u00e9raires peut \u00e9galement \u00eatre lanc\u00e9 \u00e0 partir d&#8217;Excel avec de simples liens hypertextes, comme \u00e9voqu\u00e9 <a href=\"https:\/\/www.auditsi.eu\/?p=9356\">dans cet article<\/a>.<\/p>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?p=9377\">API<\/a><\/p>\n<div id=\"sconnect-is-installed\" style=\"display: none; text-align: justify;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20%3A%20Obtenir%20les%20distances%20et%20temps%20de%20parcours%20d%27un%20trajet%20Google%20Maps\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Le calcul d\u2019itin\u00e9raires, de temps de parcours ou de distances entre deux adresses, quel que soit le mode de transport retenu (voiture, \u00e0 pied, v\u00e9lo, train\u2026), est utile pour planifier un d\u00e9placement chez un client, valider une note de frais\u2026 Ces calculs peuvent \u00eatre r\u00e9alis\u00e9s \u00e0 l&#8217;aide de sites internet comme Mappy ou Google Maps. &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=11129\" class=\"more-link\">Continue reading &lsquo;Excel : Obtenir les distances et temps de parcours d&#8217;un trajet Google Maps&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20%3A%20Obtenir%20les%20distances%20et%20temps%20de%20parcours%20d%27un%20trajet%20Google%20Maps\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[48],"tags":[2116,2136,2410,1633,2909,2389,64,2430,2140,2395,2910,2409],"class_list":["post-11129","post","type-post","status-publish","format-standard","hentry","category-excel","tag-api","tag-balise","tag-distancier","tag-excel","tag-filtre-xml","tag-google-maps","tag-interface","tag-itineraire","tag-langage-xml","tag-requete-http","tag-serviceweb","tag-temps-de-parcours"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/11129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11129"}],"version-history":[{"count":8,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/11129\/revisions"}],"predecessor-version":[{"id":11138,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/11129\/revisions\/11138"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}