Numeriblog Google Workspace Google Apps Script, la puissance des outils Google à portée de scripts Google Sheets : comment créer une validation de données dynamique en cascade.

Google Sheets : comment créer une validation de données dynamique en cascade.

La gestion des bases de données nécessite de disposer de données propres et homogènes. Aussi lors de la saisie la validation de données de Google Sheets est là pour vous garantir une saisie homogène. Mais […]

personnes ont consulté cet article

3 minutes

Rédigé par Patrick Tellier - il y a 8 mois

Ce que vous allez découvrir

  • Construction du tableau :
  • Validation de données en cascade
  • Assurer la cohérence des données
  • Conclusion

Google Sheets : comment créer une validation de données dynamique en cascade.

La gestion des bases de données nécessite de disposer de données propres et homogènes. Aussi lors de la saisie la validation de données de Google Sheets est là pour vous garantir une saisie homogène. Mais il faut encore que les données soient cohérentes. Nous allons voir aujourd’hui comment créer une validation de données en cascade. Le thème sera le choix de communes en fonction des codes postaux. Vous aurez ainsi la certitude de la cohérence des données saisies : la commune sélectionnée aura bien pour code postal la valeur saisie.

Lien vers le tableau

Construction du tableau :

Une première feuille contient la liste des codes postaux Français et des communes associées. Plusieurs communes peuvent avoir le même code postal et parfois un nom de hameau ou de lieu-dit peut compléter la localisation.

La deuxième feuille appelée “base saisie” comporte trois colonnes : Code postal, Commune et Hameau / Lieu-dit.

Pour la première c’est simple vous sélectionnez une validation de donnée “suivant une liste créée à partir d’une plage” :

Validation de données en cascade

Vous allez devoir ensuite créer une validation de données pour ne proposer que la liste des communes associées à ce code postal. Pour cela vous créez une troisième feuille qui s’appellera “validation données”, le but est de reproduire sur chaque ligne la liste des communes associées au code postal de la même ligne de la feuille “base saisie”. Vous avez deux solutions possibles : soit FILTER soit QUERY pour cette première ligne nous retiendrons la fonction FILTER.

Vous placez donc en A2 la formule :  =TRANSPOSE(FILTER(laposte_CP!B:B;laposte_CP!C:C=  'Base saisie'!A2))

C’est-à-dire que vous filtrez la colonne B des communes pour lesquelles le code postal est égal à celui saisi en A2 et vous complétez le tout par la fonction TRANSPOSE pour que la liste soit sur la même ligne. Et vous recopiez cette formule jusqu’en bas du tableau.

Combien de colonnes pour ce besoin ?

En plaçant la formule suivante quelque part sur la feuille laposte_CP : 

=query(B:G;"select count(C) , C group by C order by count(C) desc limit 1")

on obtient : 

4651300

C’est-à-dire que le maximum d’items sera atteint pour le code postal 51300 qui va mobiliser 46 colonnes. Avec une petite marge de sécurité vous placerez la validation de données de la colonne A à la colonne AZ.

Ensuite il suffit de placer une validation de données en B2 suivant une “Liste créée à partir d’une plage” de la feuille validation données de A2 à AZ2.  

Notez bien qu’il n’y a pas de “$” devant le 2, ce qui vous permettra de recopier la validation de données jusqu’en bas du tableau et ainsi chaque cellule de la colonne B possède sa validation de données sur la même ligne de la feuille “validation données”

Mais il y a parfois en complément de la commune, un hameau ou un lieu-dit et vous arrivez au troisième niveau de validation en cascade. Pour varier les plaisirs, c’est la fonction QUERY qui sera retenue cette fois. En AA2 de la feuille vous placez la formule :

=TRANSPOSE(SIERREUR(query(laposte_CP!A1:F;"select D where C='"& 'Base saisie'!A2&"' AND E='"&'Base saisie'!B2&"'")))

La requête porte sur la feuille des codes postaux, la colonne D est sélectionnée lorsque le code postal est celui saisi en A2 ET la commune est celle saisie en B2 (attention plusieurs communes peuvent avoir le même nom dans différents départements).

La fonction TRANSPOSE complète la formule pour que la liste s’étale à l’horizontal et la formule est recopiée jusqu’en bas de la feuille.

Et pour terminer une validation de données est appliquée à la cellule C2 suivant la liste de la feuille Validation données de BA2 à BF2.

Voilà, vous pouvez maintenant saisir des valeurs de code postal en colonne A, les communes correspondantes vous seront proposées en liste déroulante en colonne B et les compléments éventuels en colonne C.

La feuille “Validation données” peut éventuellement être masquée puisqu’elle n’a qu’une utilité fonctionnelle.

Assurer la cohérence des données

Jusque-là tout va bien mais il y a un inconvénient pour garantir la cohérence des données comme attendu en préambule : la validation de données (avec option “refuser la saisie”) ne s’établit qu’au moment de la saisie. Et rien ne vous empêche de saisir un code postal puis la commune correspondante et de modifier à nouveau le code postal. Dans ce cas la cellule comporte un coin rouge avec une annotation : “NON-VALIDE : la valeur d’entrée doit être comprise dans la plage spécifiée”.

Pour parer ce défaut vous allez créer un script qui efface les données dans la même ligne de la colonne B si une cellule de la colonne A est modifiée ainsi que la colonne C :

function onEdit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var onglet = ss.getActiveSheet();
  var cellule = onglet.getCurrentCell();
  var colonne = cellule.getColumn();
  var ligne = cellule.getRow();
  var valeur = cellule.getValue();
if(onglet.getSheetName()=='Base saisie'  ){
  if(colonne==2 ){onglet.getRange(ligne,3).clearContent() }
  if(colonne==1 ){onglet.getRange(ligne,2,1,2).clearContent() }
}
}

Si vous êtes novices dans la création de script vous pouvez suivre cet excellent article : Créer un script

Dans l’ordre, la fonction s’appelle “onEdit” c’est-à-dire qu’elle va s’exécuter à chaque modification sur la feuille de calcul. 

La variable Onglet contient la feuille active, la variable cellule contient la cellule courante et les variables colonne et ligne contiennent…  je vous laisse deviner.

Ensuite si la feuille qui subit la modification est bien “base saisie” et si la colonne est la B (=2) alors on efface le contenu de la cellule en troisième colonne de la même ligne.

Et si la colonne est la A alors on efface le contenu des deux cellules des colonnes B et C de la même ligne.

Ainsi toute modification du code postal efface la commune sélectionnée et vous contraint à en saisir une nouvelle suivant la validation de données. La cohérence des données est assurée.

Lien pour créer le tableau à partir du modèle

Conclusion

La validation de données est essentielle dans une feuille pour assurer l’homogénéité des données, la validation de données dynamique en cascade comme présenté dans cet article permet d’assurer en plus la cohérence des données.

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
1 Commentaire
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Laurent COLLET

Cool !! Super ressources

Cet avis vous a été utile ?