Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets, Google Apps Script : comment rendre des listes déroulantes interdépendantes avec un script

Google Sheets, Google Apps Script : comment rendre des listes déroulantes interdépendantes avec un script

Il est souvent demandé lors de nos accompagnements (webinaires ou remédiations auprès de clients), de créer des dépendances entre plusieurs listes déroulantes dans une même table de données. Ce simple besoin d’ergonomie soulève des questions […]

personnes ont consulté cet article

4 minutes

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

Ce que vous allez découvrir

  • Contexte :
  • Préparation des données
  • Script actualisant la validation des données d'une cellule en fonction du choix de la cellule précédente :
  • Détail du script de création de validation de données à la volée

Google Sheets, Google Apps Script : comment rendre des listes déroulantes interdépendantes avec un script

Il est souvent demandé lors de nos accompagnements (webinaires ou remédiations auprès de clients), de créer des dépendances entre plusieurs listes déroulantes dans une même table de données. Ce simple besoin d’ergonomie soulève des questions techniques qui ont challengé nos coachs !

Jérémi a proposé dans un article deux solutions à mettre en place pour rendre des liste de choix interdépendantes en utilisant uniquement des fonctions natives de Google Sheets.

Je vous propose dans ce nouvel article une approche utilisant un script.

Certes elle nécessite de se retrousser les manches et de mettre les mains dans Google Apps Script, mais elle permet :

  • de créer des dépendances sur un nombre infini de niveaux au lieu de seulement deux pour la solution avec fonctions natives ;
  • de demander moins de préparation de données en amont ;
  • de s’adapter plus rapidement à la modification des informations générant les listes déroulantes et les dépendances.

Contexte :

Voici une feuille dans laquelle l’utilisateur doit sélectionner une région, puis un département de cette région, et enfin une commune de ce département. La liste des départements doit dépendre de la région sélectionnée dans la première colonne et celle des communes doit dépendre du département choisi.

Préparation des données

Il faut dans un premier temps construire une table de données pour chaque dépendance :

feuille « reg-dep«  :
dépendance entre les régions et les départements :

listes interdépendantes préparez vos données région département

feuille « dep-com » :
dépendance entre les départements et les communes :

listes interdépendantes préparez vos données région département

Il faut ensuite créer une feuille de sélection accueillant les listes interdépendantes, que j’ai appelée « Choix ».

feuille « Choix ».

Nommez les colonnes « région, département, commune ».

Dans la première colonne, créez une validation des données : « liste créée à partir d’une plage », et sélectionnez la plage contenant les noms des régions (colonne A de la feuille « reg-dep »).

listes interdépendantes validation des données "régions"

Script actualisant la validation des données d’une cellule en fonction du choix de la cellule précédente :

/******
 *
 *
 * NUMERICOACH - tous droits réservés - Antoine MARTIN 2021
 *
 */
function onEdit(e) {
  // récupération des valeurs
  const range = e.range;
  const col = range.getColumn();
  const row = range.getRow();
  const value = range.getValue();
  // récupération de la feuille modifiée
  const source = e.source;
  const sourceId = source.getSheetId();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetChoixId = ss.getSheetByName("Choix").getSheetId();
// Si c'est bien la feuille choix qui héberge la modification
  if (sourceId == sheetChoixId) {
    if (row > 1) {
      // si c'est la première colonne qui est sélectionnée, lancer la validation de données des départements dans la colonne 2
      if (col == 1) {
        dataValidation(row, 2, "reg-dep", value);
      }
      // si c'est la deuxième colonne qui est sélectionnée, lancer la validation de données des communes dans la colonne 3
      if (col == 2) {
        dataValidation(row, 3, "dep-com", value);
      }
    }
  }
}
function dataValidation(row, col, table, cle) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Choix");
  const cellule = sheet.getRange(row, col);
  // initialisation de la liste
  let liste = [];
  if (cle != "") {
    liste = filtre(table, cle);
    const validation = SpreadsheetApp.newDataValidation().requireValueInList(liste).build();
    cellule.setDataValidation(validation);
  }
  else {
    cellule.clearDataValidations();
  }
}
function filtre(type, cle) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(type);
  const values = sheet.getDataRange().getValues();
  let filtre = [];
  values.map(function (row)
     {
     if (row[0] == cle && row[1] != null)
          {
             filtre.push(row[1]);
          }
      })
  return filtre;
}

Détail du script de création de validation de données à la volée

onEdit(e)

La fonction onEdit(e) se déclenche à chaque modification de cellule et renvoie les informations modifiées.

Le script récupère la cellule modifiée, son numéro de colonne et de ligne, sa valeur et sa feuille.

Après vérification qu’il s’agit de la bonne feuille, le script lance la construction d’une validation de données selon la colonne de la cellule modifiée.

Dans mon exemple, il n’y a que deux possibilités :

  • si c’est la première colonne qui est modifiée, il faut créer la validation de données de la cellule de la deuxième colonne en allant chercher les départements de la région sélectionnée, dans la table « reg-dep« ;
  • si c’est la deuxième colonne qui est modifiée, il faut créer la validation de données de la cellule de la troisième colonne en allant chercher les communes du département sélectionné, dans la table « dep-com« .

Par exemple, si l’utilisateur change la région dans la cellule A4, le script va déclencher la construction de la validation de données de la cellule voisine B4, en allant chercher les départements correspondant à la région sélectionnée en A4, et ce, grâce à la fonction dataValidation().

Remarque : Il est possible de multiplier les possibilités, en les ajoutant en dur ou en créant une boucle conditionnelle.

dataValidation(row, col, table, cle)

La fonction dataValidation(row, col, table, cle) avec ses quatre arguments, permet de construire la validation des données de la cellule située sur la ligne donnée par l’argument « row » et sur la colonne donnée par « col ». Les données sont filtrées dans « table » en fonction du critère « cle ».

Le script récupère dans la constante « cellule » la cellule sur laquelle il faut créer la validation de données.

Ensuite le script créé la variable « liste » qui est un tableau de données, vide pour l’instant, et qui rassemblera les éléments de la liste déroulante.

Si la valeur « cle » n’est pas vide :

Le script déclenche la fonction filtre(table,cle) (voir plus loin) qui renvoie les éléments à mettre dans le tableau « liste » en sélectionnant uniquement les valeurs de « table » qui correspondent à « cle ».

Il faut ensuite construire la validation des données avant de l’appliquer à une cellule.

pour obtenir une validation telle que représentée sur la capture ci-contre il faut :

  • appeler le service SpreadsheetApp ;
  • puis appeler la méthode newDataValidation() pour annoncer la création d’une validation de données ;
  • puis appeler la méthode qui indique les critères de validation : le type et les données (qui dépendent du type).
    Donc dans notre exemple : requireValueInList(liste) construit une « Liste d’éléments » parmi les valeurs données dans la variable « liste ».
    (voir dans la documentation la liste des types de validation de données)
  • enfin, terminer par build() pour finaliser la construction de la validation de données.
 const validation = SpreadsheetApp.newDataValidation().requireValueInList(liste).build();

le script applique enfin la validation sur la cellule ciblée avec la méthode setDataValidation() :

cellule.setDataValidation(validation);

Si la variable « cle » est vide :

Le script supprime la validation des données de la cellule ciblée :

cellule.clearDataValidations();

filtre(table,cle)

Cette dernière fonction est appelée dans la fonction dataValidation(row, col, table, cle) afin de construire la liste des éléments de la validation des données.

Elle ouvre la table qui porte le nom indiqué par la constante « table », puis elle récupère et lit les données une à une dans la fonction map().

Elle pousse dans un tableau les valeurs de la deuxième colonne (index 1) ayant la valeur « cle » dans la première colonne (index 0).

values.map(function (row)
     {
     if (row[0] == cle && row[1] != null)
          {
             filtre.push(row[1]);
          }
      })

Enfin le script retourne le tableau construit.


Que ce soit avec des fonctions natives ou du script, il est possible de répondre au besoin de créer des listes déroulantes interdépendantes. Merci pour vos retours d’expérience dans les commentaires !

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
guest
0 Commentaires
Commentaires en ligne
Afficher tous les commentaires