Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : recensez et indiquez à chacun de vos clients le temps passé sur leur projet

Google Sheets : recensez et indiquez à chacun de vos clients le temps passé sur leur projet

Cet article permet de voir comment utiliser différentes fonctionnalités de Sheets pour créer un tableau de bord du temps passé sur des activités et de communiquer ces informations à qui de droit. Il évoque des […]

personnes ont consulté cet article

3 minutes

Rédigé par Antoine MARTIN - il y a 3 ans

Ce que vous allez découvrir

  • Contexte
  • Le classeur
  • Tableaux de bord par client

Google Sheets : recensez et indiquez à chacun de vos clients le temps passé sur leur projet

Cet article permet de voir comment utiliser différentes fonctionnalités de Sheets pour créer un tableau de bord du temps passé sur des activités et de communiquer ces informations à qui de droit.

Il évoque des fonctionnalités qui permettent d’exploiter des données de différentes feuilles : #recherchev(); #filter(); #query()…, quelques fonctions de calcul : #somme() et #somme.si() et enfin l’exploitation de la fonction #publier sur le web.

Contexte

Je vous propose de vous glisser dans la peau d’un graphiste de l’agence 10Zaïne. Vous travaillez pour plusieurs clients en même temps sur des projets divers de design pour le print ou le web.

Il faut informer chaque client du temps passé et restant sur chaque projet.

PS : retrouvez cette agence sur une autre problématique : générer et éditer des devis.

Votre logo…
  • Comment comptabiliser le temps passé sur chaque projet ?
  • Comment donner l’accès de ces informations à chaque client, de façon automatique et sécurisée ?

Je vous propose, dans un premier temps, de créer un classeur Sheet qui recense vos clients, vos projets et vos temps d’intervention. Vous mettrez à jour ce fichier en appelant le projet et en indiquant la date du jour, l’heure de début et de fin de l’intervention et l’intitulé de l’intervention.

Ensuite, il faudra trouver un moyen pour publier automatiquement les temps passés et restant à chaque client.

Suivez le guide :

Le classeur

Voici un exemple de fichier associant trois feuilles de calcul dont la finalité est de saisir pour chaque projet de chaque client le nombre d’heures effectuées au jour le jour.

Feuille « CLIENTS »

La première feuille recense vos clients.

Base de données « clients »

Feuille « PROJETS »

Base de données « Projets »

Feuille « Interventions »

Mise à jour 4 juin 2021 : retrouvez ici un article proposant d’automatiser la récupération des temps d’interventions depuis Google Agenda.

Cette feuille compile les informations Projets et Client et permet de saisir une date et les horaires de l’intervention.

Pour info j’ai pris l’habitude de distinguer les colonnes à saisir en orange des colonnes qui se complètent toutes seules en gris.

Interventions

Voyons ensemble chaque cellule grise.

1/ Affichage automatique du client en fonction du projet sélectionné

la formule pour la cellule B2 de l’exemple serait :

SI(A2<>"";IFNA(RECHERCHEV(A2;Projets!B:C;2;FAUX));"")
=SI( Si…
A2<>"" … un projet est renseigné dans A2
; alors :
IFNA( si il n’y a pas d’erreur…
RECHERCHEV( recherche ligne par ligne…
A2; le projet de la cellule A2…
Projets!B:C; dans le tableau de la feuille Projet en commençant par la colonne B (parce que c’est là que sont les noms des projets)…
2; et donne-moi l’information qui se trouve dans la 2ème colonne aprés le nom du projet…
FAUX et qui n’est pas approximatif.
) Fin de Recherchev
) Fin de IFNA
; sinon (si A2$ n’est pas renseigné)
"" ne fais rien.
Fin du SI

Étirez cette formule sur toute la colonne B.

2/ Calcul du nombre de jours consommés en fonction de l’horaire de début et de fin

Partant du principe qu’il y sept heures travaillées dans une journée, il suffit de calculer la différence entre les deux horaires divisée par 7.

=IF(A2<>"";(F2-E2)/7;"")

La valeur calculée peut être arrondie au dixième avec le bouton :

3/ Affichage du nombre de jours prévus dans le projet

Sur le même modèle que le client, il faut aller chercher l’information dans la feuille ‘Projets’. Dans cette feuille, l’information est dans le colonne « E », soit la 4ème après la « B » :

SI(A2<>"";IFNA(RECHERCHEV(A2;Projets!B:E;4;FAUX));"")

4/ Calcul du nombre de jours restant sur le projet

Dernier calcul pour notre exemple : sur chaque ligne d’un projet, il faut calculer la différence entre le nombre de jours total du projet (colonne précédente) et le nombre total déjà consommé sur ce projet.

Pour trouver ce nombre de jours déjà consommés, utilisons la fonction SOMME.SI.

=SOMME.SI(A:A;"="&A2;G:G)

Voyons cette « somme.si » en détail :

=SOMME.SI( recherche les lignes
A:A;

dont le nom du projet dans la colonne « A »…

  "="&A2 ; correspond au nom du projet de la ligne…
G:G et fais la somme des valeurs de la colonne des jours consommés
Fin du SI

Avec ces trois feuilles, que vous pouvez adapter à votre besoins, vous pouvez chaque jour reporter le temps passé sur chaque projet. Voyons maintenant comment extraire ces données pour chaque client :

Tableaux de bord par client

Créez un modèle

Cette feuille permet d’afficher les informations filtrées par client.

Ajoutez une cellule avec validation des données qui va chercher la liste des clients dans la feuille Clients.

Affichez les détails en fonction du client sélectionné : vous pouvez utiliser FILTER() ou QUERY().

Voyons avec QUERY() ! formule à saisir dans la première cellule du tableau de récap :

=QUERY(Interventions!A:I;"SELECT A,C,D,E,F,G,H,I WHERE B = '"&C4&"' ORDER BY B,G DESC ")

Query en détail… :

=QUERY( Lance une requête …
Interventions!A:I; dans le tableau de la feuille « Interventions »…
" Début de la consigne :
SELECT affiche…
A,C,D,E,F,G,H,I les colonnes projets, tâche effectuées, jour, nombre de jours consommés, nombre total de jour du projet, jours restant…
WHERE dont…
B = '"&C4&"'  le client est celui sélectionné dans la cellule C4 (Attention, comme on compare du texte, il faut mettre des simples quotes (‘) puis des doubles (« ) pour sortir de l’instruction et coller la valeur avec des « & »…)
ORDER BY  en les triant par…
A le projet en ordre  alphabétique..
, puis …
D par date …
DESC dans l’ordre décroissant (pour afficher les interventions les plus récentes en premier.
" Fin de la consigne
) Fin de l’a requête

Dupliquez et fixez la feuille de chaque client

Dupliquez la feuille MODELE CLIENT
Renommez la feuille et supprimez la validation des données

Publiez la feuille et transmettez l’URL au client

L’écran suivant affiche une URL Qu’il suffit de copier coller et transmettre au client :

Un conseil : ajoutez cette URL dans le fichier CLIENTS, pour garder une trace facile à récupérer !

Cette URL pointe sur une page web qui affiche les informations demandées. il ne s’agit pas d’une page Sheets. Le visiteur n’a aucune action à faire. La page se met à jour à chaque modification de la feuille. Ajoutez une intervention, et le client pourra le voir sur sa page dédiée.

A vous de jouer !

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
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour
5 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
JEAN JACQUES FENOUIL

Bravo Antoine,
Cette approche correspond à un de mes besoins et j’aima particulierement le pragmatisme et la concision de votre approche …. l’essentiel est là . MERCI BEAUCOUP
Je suis normalement déjà abonné à la newsletter …

[…] que nous avons déjà croisée dans l’article suivant : Recenser le temps passé sur les projets […]

Merci Antoine pour cet excellent article si bien expliqué et ses formules détaillées.

Félix COTTIN

Bonjour et merci pour ce super tutoriel. Quel temps gagné !
Néanmoins, je n’arrive pas à me rendre sur la page « Mise à jour 4 juin 2021 : retrouvez ici un article proposant d’automatiser la récupération des temps d’interventions depuis Google Agenda. »
Lorsque je clique sur le lien, voici le message affiché : « Désolé, vous n’avez pas l’autorisation de prévisualiser les brouillons. »
Serait-il possible d’y avoir accès ?
Merci encore