Numeriblog Google Workspace Google Forms, des formulaires interactifs gratuit Google Sheets, Forms, Maps : comment calculer la distance d’un trajet en voiture entre deux adresses ?

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 […]

5(1)
personnes ont consulté cet article

3 minutes

Rédigé par Patrick Tellier - il y a 2 ans

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.

Paywall image

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

Rédacteur

Photo de profil de l'auteur
Patrick Tellier

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.

S’abonner
Notification pour
guest

5

Note du cours

(1)

(0)

(0)

(0)

(0)

41 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Mathilde
Mathilde

Article très intéressant. Merci Patrick 🙂

Cet avis vous a été utile ?

TELLIER PATRICK
Répondre à  Mathilde
1 année il y a

Merci 😉

Cet avis vous a été utile ?

sara kachkach
sara kachkach
Répondre à  TELLIER PATRICK
5 mois il y a

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 ?

Tellier
Tellier

Bravo pour cette réflexion très explicite et claire

Cet avis vous a été utile ?

TELLIER PATRICK
Répondre à  Tellier
1 année il y a

Merci 😉

Cet avis vous a été utile ?

Stéphanie
Stéphanie
Répondre à  TELLIER PATRICK
6 mois il y a

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 ?

Mehdi
Mehdi

Merci beaucoup très claire,

Cet avis vous a été utile ?

Caupin
Caupin

Merci Patrick ! Très clair

Cet avis vous a été utile ?

Norman Gaudet
Norman Gaudet

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 ?

Patrick TELLIER
Répondre à  Norman Gaudet
1 année il y a

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 ?

Cécile LE MASSON

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 ?

Cécile LE MASSON
Répondre à  Cécile LE MASSON
1 année il y a

Correctif, je voulais dire : soit A>C, soit A>B + B>C…

Cet avis vous a été utile ?

Cécile LE MASSON
Répondre à  Patrick Tellier
1 année il y a

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 ?

Cécile LE MASSON
Répondre à  Cécile LE MASSON
1 année il y a

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 ?

Patrick Tellier
Répondre à  Cécile LE MASSON
1 année il y a

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 ?

Stéphanie
Stéphanie
Répondre à  Cécile LE MASSON
9 mois il y a

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 ?

Stéphanie
Stéphanie
Répondre à  Cécile LE MASSON
6 mois il y a

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 ?

Patrick Tellier
Répondre à  Stéphanie
6 mois il y a

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 ?

thierry
thierry

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 ?

Patrick TELLIER
Répondre à  thierry
1 année il y a

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 ?

thierry
thierry
Répondre à  Patrick TELLIER
1 année il y a

où faut il enregistrer ce script?
sous apps script ? (dans menu « extentions » de Gsheet)

Cet avis vous a été utile ?

GADANHO
GADANHO

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 ?

Patrick Tellier
Répondre à  GADANHO
1 année il y a

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 ?

Benoit
Benoit

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 ?

Patrick Tellier
Répondre à  Benoit
1 année il y a

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 ?

vincent
vincent

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 ?

Patrick Tellier
Répondre à  vincent
1 année il y a
Thierry
Thierry

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 ?

Patrick TELLIER
Répondre à  Thierry
1 année il y a

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 ?

xavier
xavier

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 ?

Xavier
Xavier
Répondre à  Patrick Tellier
10 mois il y a

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 ?

briand marie
briand marie

Super ! Merci Patrick !!!!! un gain fou de temps !;-)

Cet avis vous a été utile ?

Patrick TELLIER
Répondre à  briand marie
10 mois il y a

Merci Marie …

Cet avis vous a été utile ?

Alex
Alex

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 ?

Votre note :
     

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 ?