Sheets
Agenda
Forms
Apps Script
Gmail
Chat
Meet
Contacts
Chrome
Sites
Looker Studio
Slides
Docs
Drive
AppSheet
Android
Chromebook
Equipements Google
Google Cloud Platform
Google Photos
Maps
Youtube
Google Sheets, Forms, Maps : comment calculer la distance d’un trajet en voiture entre deux adresses ?
La situation sanitaire récente a bousculé les modes de travail. Le télétravail comme les réunions en “visio” ont connu un développement exponentiel avec un impact environnemental non négligeable. Le cas d’usage présenté aujourd’hui consiste à enregistrer […]
Ce que vous allez découvrir
- Finalité
- Recueil des trajets économisés avec un formulaire Google Forms :
- Récupération des réponses dans un fichier Google Sheets
- Création de la formule personnalisée qui permet de calculer une distance entre deux adresses
- Conclusion
Google Sheets, Forms, Maps : comment calculer la distance d’un trajet en voiture entre deux adresses ?
La situation sanitaire récente a bousculé les modes de travail. Le télétravail comme les réunions en “visio” ont connu un développement exponentiel avec un impact environnemental non négligeable. Le cas d’usage présenté aujourd’hui consiste à enregistrer et quantifier les gains de trajets, de carburant ou de rejet de gaz à effets de serre engendrés par la réduction des déplacements automobiles.
Le but est de proposer un questionnaire Google Forms très simple à renseigner pour les utilisateurs, d’en déduire dans Google Sheets la distance entre deux adresses et de restituer mensuellement les gains réalisés dans un rapport DataStudio.
Finalité
La finalité de cette série d’articles est de créer un rapport sur Google Data Studio qui va permettre un envoi automatique par mail tous les mois aux utilisateurs et ainsi les sensibiliser aux économies réalisées.
La consommation de carburant et les rejets de CO2 sont estimés par une moyenne au kilomètre.
Et pour créer une émulation autour du projet, ajoutez un palmarès des contributions 😉!
Recueil des trajets économisés avec un formulaire Google Forms :
Le questionnaire est très simple, renseignez juste :
la date de l’évènement pour pouvoir analyser ensuite les périodes ; l’adresse de départ du trajet ; l’adresse d’arrivée.
L’utilisation de l’API Google Maps qui calcule ensuite les distances perme
Veuillez créer un compte pour lire la suite de cette ressource
L’accès à cette ressource se fait gratuitement en créant un compte Numerifan. Les abonnements payants donnent également accès aux cours gratuits.
Cet article est réservé aux abonnés. Pourquoi ?
Fournir tout ce contenu nous demande beaucoup de temps, des ressources et une veille permanente. Accédez à ce contenu en rejoignant notre super communauté, pour tous nous remercier 💪
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Tutos connexes
- Plus de l'auteur
Passionné par l'univers Google Workspace, Formateur interne, je suis toujours à la recherche de nouvelles solutions de productivité et d'automatisation à mettre en oeuvre et à partager.
5
Note du cours
(1)
(0)
(0)
(0)
(0)
Article très intéressant. Merci Patrick 🙂
Cet avis vous a été utile ?
Merci 😉
Cet avis vous a été utile ?
Bonjour,quand je fais executer le script, il me donne des erreurs. qu’est ce que je peux faire ? merci
Cet avis vous a été utile ?
Bonjour Sara,
C’est un type fonction qui nécessite des arguments (depart & destination), donc vous ne pouvez pas l’exécuter directement depuis l’éditeur. Il faut insérer la fonction dans le tableau.
Cet avis vous a été utile ?
Bravo pour cette réflexion très explicite et claire
Cet avis vous a été utile ?
Merci 😉
Cet avis vous a été utile ?
Bonjour,
merci pour cet article très intéressant.
j’ai regardé pour ajouter des étapes avec votre script …
je l’ai mis où vous indidiquiez mais en vain…
je n’ai pas dû faire comme il faut.
j’aimerais pouvoir ajouter des étapes entre les villes départ et arrivée.
pourriez-vous m’aider, s’il vous plait?
merci d’avance pour vote aide
Cet avis vous a été utile ?
Merci beaucoup très claire,
Cet avis vous a été utile ?
Merci Patrick ! Très clair
Cet avis vous a été utile ?
Bonjour M. Tellier,
Félicitation et merci pour le scripts, il fonctionne très bien.
Pouvez-vous m’aider? J’aimerais avoir le temp du trajet aller, retour dans une colonne du ficher sheets.
Merci beaucoup
Cet avis vous a été utile ?
Bonjour et Merci,
Pour le temps de trajet, c’est très simple : il suffit de remplacer « distance » par « duration » dans la dernière ligne du script :
return directions.routes[0].legs[0].duration.value;
le résultat retourné est en secondes (pour un aller) et le temps est bien sûr calculé à l’instant et tiens compte des conditions de circulation, il peut varier en période de pointe.
Cet avis vous a été utile ?
Merci pour ce script, c’est génial ! Très utile pour calculer des notes de frais de déplacement, également…
Pour ceux que cela intéresse : dans ma feuille de calcul, j’ai ajouté la possibilité d’inclure une étape B dans le trajet A>C.
– j’ai ajouté une condition SI(ESTVIDE), pour faire faire un calcul différent selon que j’ai une étape B ou pas (soit A>C, soit AC);
– pour avoir un lien vers l’itinéraire avec étape, j’ai juste eu à ajouter « &B2& »/ dans la formule créant le lien.
Cet avis vous a été utile ?
Correctif, je voulais dire : soit A>C, soit A>B + B>C…
Cet avis vous a été utile ?
Bonjour Cécile,
Merci pour votre commentaire, effectivement il y a de nombreux cas d’usage pour automatiser le calcul de distances. Et merci également pour le partage de cette évolution avec étape intermédiaire.
Cet avis vous a été utile ?
Hélas, j’ai entré tellement d’itinéraires à calculer et fait tellement d’opérations sur le fichier par la suite (obligeant à rafraîchir les calculs de distance), que « trajetDistance » ne fonctionne plus !
Voici le message d’erreur : Exception: Service invoked too many times for one day: route. (ligne 18).
C’est comme si j’avais dépassé mon quota, mais sans savoir qu’il y en avait un, ni à combien d’utilisations j’avais droit.
J’ai donc supprimé le script. J’ignore combien de temps il faudra attendre pour tenter de le remettre en fonction. Et sinon, adieu cette belle fonction si pratique…
Cet avis vous a été utile ?
J’ai trouvé les quotas, pour ceux que cela intéresse :
Requête de direction Google Map : 1 000 / jour
Appels Google Map Geocode : 1 000 / jour
https://developers.google.com/apps-script/guides/services/quotas
Cet avis vous a été utile ?
Bonjour Cécile
J’ai également rencontré ce problème d’un excès de trajets qui causait des ralentissements.
La parade est de faire un copier/coller les valeurs pour figer les résultats et éviter un recalcul complet à chaque saisie.
Cet avis vous a été utile ?
Bonjour,
je serai intéressée pour la possibilité d’ajouter une étape.
J’essaie mais …. je n’arrive pas à mettre la bonne formule.
Merci d’avance
Cet avis vous a été utile ?
Bonjour,
serait-ce possible de partager votre fichier?
j’essaie d’ajouter une étape (voire plus) et je n’y arrive pas.
merci d’avance pour votre aide
Stéphanie
Cet avis vous a été utile ?
Bonjour Stéphanie,
Ci-lié une copie du tableau avec 1 ou 2 étapes (Voir les formules en E2 et F2) : on peut avoir 0, 1 ou 2 étapes : https://docs.google.com/spreadsheets/d/12SjXPHUE5p3taulnOOJJbOQqqgdRuhViEDCbvirGXz8/template/preview
Bonne journée
Cet avis vous a été utile ?
ca semble très intéressant mais je n’y arrive pas sachant que je n’y connais pas grand chose.
j ai des messages d’erreur, et surtout je ne sais pas quoi utiliser comment déploiement. API probablement
»
Projet Cloud Platform géré par l’utilisateur requis
Vous utilisez actuellement un projet Cloud Platform géré par Apps Script. Pour pouvoir publier, vous devez passer à un projet Cloud Platform géré par un utilisateur pour ce script.
»
une piste?
Merci beaucoup
Cet avis vous a été utile ?
Bonjour,
Il n’y a pas de déploiement particulier à paramétrer.
Une fois le script enregistré, la fonction est opérationnelle dans la feuille de calcul.
Cet avis vous a été utile ?
où faut il enregistrer ce script?
sous apps script ? (dans menu « extentions » de Gsheet)
Cet avis vous a été utile ?
Bonjour Patrick,
Dans un premier temps, merci beaucoup pour votre macro, c’est génial.
Ensuite je souhaiterai savoir s’il était possible de rajouter des étapes sur mon trajet, est-ce possible ?
Par avance merci,
GADANHO Léan
Cet avis vous a été utile ?
Bonjour Gadanho,
C’est possible bien sûr , il suffit d’insérer une ligne : directionFinder.addWaypoint(etape);
entre depart et destination, et l’inclure dans les arguments de la fonction : function trajetDistance(depart,etape, destination) {
Cet avis vous a été utile ?
Bonjour,
Merci pour cette aide précieuse qui va grandement m’aider ♥️
Question :
J’ai un problème pour étirer la ligne, puisque j’ai une case où je rentre l’adresse de destination et un tableau avec mon carnet d’adresse.
De plus, je ne peux pas trier le tableau par ordre croissant ( du trajet le plus court au plus long)
Vous auriez une solution ?
Cet avis vous a été utile ?
Bonjour Benoit,
Si je comprends bien c’est une question d’adressage Absolu, relatif ou mixte : A1 -> $A$1 !
sinon il n’y a pas de problème pour « étirer » la ligne.
Cet avis vous a été utile ?
merci, je viens de gagné quelque heure de travail
une idée pour indiqué également le temps de trajet ?
Cet avis vous a été utile ?
Bonjour Vincent,
La suite le 21 Août : https://thierryvanoffe.com/google-sheets-maps-comment-calculer-le-temps-dun-trajet-en-voiture-entre-deux-adresses/
Cet avis vous a été utile ?
Bonjour, merci pour ce script
Mais j’aimerais pouvoir l’appliquer sans l’utilisation d’un formulaire, c’est à dire uniquement en entrant l’adresse dans une case donnée, est-ce possible?
Merci
Cet avis vous a été utile ?
Bonjour,
Aucun problème : dans la fonction trajetDistance(D2;E2) on peut remplacer D2 et E2 par n’importe quelle autres cellules sans utiliser de formulaire.
Bien cordialement
Cet avis vous a été utile ?
Bonjour Monsieur Tellier,
j’ai quelques difficultés à intégrer le script.
Cette fonction me serait pourtant des plus pratique.
je n’ai pas de souci en G2 pour obtenir le mapping, par contre je ne réussi pas à avoir ma donnée en nbre de km.
Pouvez vous m’aider svp ?
Cet avis vous a été utile ?
Bonjour Xavier,
Ci-joint un lien pour créer une copie du tableau (V2 avec en plus les temps de trajet) : https://docs.google.com/spreadsheets/d/1MToW9anTGSfw1HQIDMJM-K9MzRREkoDfTtNlmUNw7-g/template/preview
Cet avis vous a été utile ?
Bonjour Patrick,
merci infiniment.
le pb est que j’avais tout simplement dépassé les quotas ; ne sachant pas qu’il y en avait.
en tout cas un grand merci pour votre disponibilité et votre réactivité.
Je vous ai envoyé une invitation linkedin.
Au plaisir
Cet avis vous a été utile ?
Bravo pour le suivi Patrcik c’est super ce que tu fais sur le blog
Cet avis vous a été utile ?
Super ! Merci Patrick !!!!! un gain fou de temps !;-)
Cet avis vous a été utile ?
Merci Marie …
Cet avis vous a été utile ?
C’est une super fonction merci. Par contre ayant un tableau de quasi 3000 adresses la fonction est vite dépassée pour usage intensif ! Il y a des limitations à l’usage, il faut donc que je trouve une autre solution.
Cet avis vous a été utile ?
Bonjour Alex,
Merci , comme c’est une fonction, elle s’exécute à chaque modification d’une ligne et toutes les lignes sont recalculées à chaque fois. Je prépare une nouvelle version qui évitera cet inconvénient.
Cet avis vous a été utile ?
Super la fonction merci.
Aurais-tu une solution pour obtenir le même résultat avec près de 1 000 000 d’adresses ?
Cet avis vous a été utile ?
Bonjour Alexis,
Pour un million d’adresses ,il faut procéder autrement, il ne faut pas utiliser le script en tant que fonction dans le tableau Gsheet, mais créer un traitement Batch et figer les résultats dans le tableau. Tu peux t’appuyer sur mon article de Novembre : https://thierryvanoffe.com/apps-script-comment-executer-un-script-sans-limitation-de-duree/ si le script est trop long à s’exécuter. Mais ça reste jouable 😉
Cet avis vous a été utile ?