mercredi 3 juillet 2013

Créer une liste déroulante conditionnelle dans Google Spreadsheet

31 commentaires
Après une bonne question sur le forum de Google Drive voici une proposition pour créer une liste dynamique qui dépend d'une valeur saisie dans une précédente liste.

Objectif
Avoir deux listes, la deuxième dépend de la première et les valeurs qu'elle contient changent quand la valeur de la première liste change.

Comment faire
1. Dans un premier il faut créer les données :
2. Ensuite on crée la première liste en utilisant la fonction unique() :
=unique(A2:A13)
3. Ensuite on crée une liste de données en utilisant la fonction "Validation..." dans "Données". Pour mon exemple cette liste est créée en G1.
4. Pour finir on crée la liste dynamique avec la fonction filter(), ça nous permet d'avoir la liste des valeurs :
=filter(B2:B13;A2:A13=G1)

5. Pour finir on crée une liste déroulante avec l'outil "Validation". L'astuce est de choisir une plage de valeur un peu plus grande si le nombre de valeurs retournées n'est pas toujours le même.

Tutorial
Pour compléter cette explication et rendre cela plus intéractif voici une vidéo qui montre comment créer les listes depuis le début et une démonstration du fonctionnement.

31 Responses so far

  1. Anonyme says:

    Super bonne idée et merci de l'avoir partagée.

  2. Anonyme says:

    Peut on étirer cette liste conditionnelle ?
    Si on veut par exemple afficher plusieurs choix. Client 1 : Bateau Bleu.
    Puis Client 2 : Voiture Rouge...
    Est ce possible ?

    Merci d'avance pour vos réponses

  3. st3ph says:

    Bonjour

    Oui on peut ajouter autant de couple de valeur que l'on souhaite il suffit juste de bien sélectionner la plage de données.

    Stéphane

  4. Anonyme says:

    Ma question était peut être pas claire.
    Dans l'exemple ci-dessus la condition ne marche qu'avec la cellule G1. Je voulais savoir s'il était possible d'avoir ce principe de validation conditionnelle qui marche aussi pour les cellules G1; G2; G3...

    On pourrait donc imaginer un tableau dans lequel nous avons la "validation conditionnelle" pour chaque ligne. Il faudrait donc pouvoir changer la cellule G1 en G2, G3.. dans la formule FILTER.
    Savez-vous comment faire?

  5. st3ph says:

    Je ne sais pas si je comprends bien mais de ce que je comprends oui il est possible de dupliquer les listes conditionnelles il suffit juste d'adapter les formules en les faisant pointer sur la bonne liste de données.

    C'est un peu flou quand même la question désolé.

  6. Anonyme says:

    Ok.
    Je vais essayer de reformuler en prenant un exemple. On arrivera peut être à se comprendre :)
    J'ai un tableau de 3 colonnes et pleins de lignes :
    - colonne 1 = nom
    - colonne 2 = véhicule
    - colonne 3 = marque

    Je veux utiliser la liste déroulante conditionnelle (exactement pareille que celle expliquée dans la vidéo) pour remplir le tableau.
    Je créé donc un tableau annexe avec les différents véhicules et les différentes marques. J'utilise la fonction UNIQUE pour faire apparaitre voiture, moto... et la fonction FILTER pour faire apparaitre Peugeot, Citroen, Ford... (si c'est voiture qui est sélectionné)

    Passons maintenant au remplissage du tableau. Si je laisse la validation conditionnelle comme expliqué dans la vidéo seule ma première ligne va marcher puisque dans FILTER je parle de B2 pour remplir C2.
    Je veux maintenant pouvoir remplir C3 en fonction de B3, C4 en fonction de B4... mais en gardant les mêmes données. (il s'agit toujours des mêmes véhicules et des mêmes marques)
    Il faudrait donc implémenter la cellule dans le FILTER.
    N'ayant pas de nombre défini de ligne je ne peux pas créer une liste par ligne.

    Est ce plus clair ?
    Avez-vous une réponse?

    Merci d'avance,

    Edouard


  7. st3ph says:

    Pas de problèmes

    Faut juste faire =filter(FeuilleX!B2:B13;FeuilleX!A2:A13=A1) et cette formule est en B1

    FeuilleX représente la feuille ou se trouve la tableau de données.
    Il n'y a pas de problème à procéder comme cela.

    Si les données changent ce qu'il faut faire c'est mettre dès le départ A2:A100 comme cela on peut aller jusque 100 ligne de données.

    stéphane

  8. Anonyme says:

    Décidément on ne se comprend pas! Récupérer les données ne me pose pas de problème. En utilisant :
    =filter(FeuilleX!B2:B13;FeuilleX!A2:A13=A1)
    j'ai une liste conditionnelle qui apparait en B1 -> OK ?
    Jusque là tout va bien pour moi

    Maintenant je veux remplir B2 et avoir une même liste conditionnelle qui dépende maintenant de A2.
    Le problème est que je ne peux pas changer ma validation conditionnelle et écrire : =filter(FeuilleX!B2:B13;FeuilleX!A2:A13=A2)
    car je ne sais pas, pour le moment, combien de ligne je vais avoir...

    J'aimerai tout simplement "étirer" cette validation de façon à ce que les conditions correspondent aux lignes que je veux remplir.

    Est-ce plus clair ?

    PS: Si je fais A1=C1-B1 et que j'étire vers le bas j'aurai A2=B2-C2
    Je veux la même chose pour ma condition. Il faudrait donc que j'ai :
    =filter(FeuilleX!B2:B13;FeuilleX!A2:A13=A1) pour ma premère ligne
    et =filter(FeuilleX!B2:B13;FeuilleX!A2:A13=A2) pour ma deuxième ligne
    et ainsi de suite jusqu'à ce que je n'ai plus de client.

    Merci de continuer à te battre pour répondre à ma question :)

    Edouard

  9. Anonyme says:

    Bonjour,
    J'ai exactement le même soucis qu'Edouard.
    A savoir que cette méthode ne fonctionne que pour une seule ligne.
    Avez vous trouvé une solution (j'ai cherché du coté de la fonction indirect (que j'arrive à faire marcher sous excel mais pas sur google)

    Merci

  10. Anonyme says:

    Idem. Même problème ? Y a t-il une solution finalement ?
    Merci

  11. st3ph says:

    Pour le problème d’Édouard il n'y a pas de réponse.

  12. Ce commentaire a été supprimé par l'auteur.
  13. Est-ce possible d'avoir une liste dynamique dans google formulaire comme celle dans votre démo ?

  14. st3ph says:

    Bonjour

    Ce ne sera pas possible, pas liaison entre le questions dans le formulaire.

    La seule astuce est de passer par des pages distinctes.
    si réponse A va vers la page 2
    Si réponse B va vers la page 3

    Mais si il y a beaucoup de questions liées cela va faire beaucoup de page à gérer.

    Stéphane

  15. Aurélien says:

    Super utile, merci beaucoup :)

  16. Anonyme says:
    Ce commentaire a été supprimé par un administrateur du blog.
  17. BenTheOuf says:

    Même probleme que tout les commentaires précédents, ça marche pour une ligne mais pas pour plusieurs :-(

  18. idem une ligne ok plusieurs non ... quelqu'un a une autre solution ?

  19. st3ph says:

    Bonjour

    Il n'y pas d'autre solution simple pour le moment. Les solutions autres sont :
    Soit on duplique cette méthode autant de fois que l'on a besoin de listes déroulante, ce qui peut être long et fastidieux
    Soit il faut passer par un apps script.

    Stéphane

  20. Bonjour,

    Pouvez-vous donner plus de détails pour apps script.

    J'ai déjà effectué la fonction suivante qui ne fonctionne malheureusement pas :(

    Pouvez-vous me filer un coup de patte ?

    PS: La fonction ci-dessous me renvoie l'erreur suivante : Vous n'êtes pas autorisé à appeler setValue.
    PS 2 : Cette fonction est appelée depuis une cellule.

    *-*-*-*-*-*-*-*-*-*-*-*-*
    function dynamiqueList(cellule) {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); // récupérer le classeur sur lequel on travail
    var sheet = ss.getSheetByName("liste deroulante"); // récupérer la feuille liste déroulante
    var nbfiltre2 = 0;
    var filtre2Array=["0"];
    for (var i =21; i <= 42; i++) {
    if (sheet.getRange(i,1).getValue().toString()==cellule) {
    filtre2Array [nbfiltre2++]=sheet.getRange(i,2).getValue().toString();
    }
    }
    for (var i = 0; i < nbfiltre2; i++) {
    sheet.getRange(i+21,5).setValue(filtre2Array[i]);
    }
    return filtre2Array[1];

    }
    *-*-*-*-*-*-*-*-*-*-*-*-*

  21. st3ph says:

    Bonjour

    Il ne faut pas passer par des tableaux mais la classe validation builder.
    Voir ce lien https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder

    En haut de cette page il y a les 4 lignes qui vont permettre de créer et appliquer une liste de validation à une cellule.

    Stéphane

  22. Bonjour,

    tout d'abord, merci de votre réponse rapide.

    Ensuite, voici mon nouveau code, adapté avec setDataValidation :

    ____________

    function dynamiqueList(cellule) {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); // récupérer le classeur sur lequel on travail
    var sheet = ss.getSheetByName("liste deroulante"); // récupérer la feuille "liste déroulante"
    var nbfiltre2 = 0; // Pour compter le nombre de 'filtre 2'
    var filtre2Array=["0"]; // Tableau qui va conntenir les différents sous-filtre

    for (var i =21; i <= 42; i++) { //Boucle sur le tableau des filtres
    if (sheet.getRange(i,1).getValue().toString()==cellule) { //Si le filtre principal correspond à ce qu'on a choisit
    filtre2Array [nbfiltre2++]=sheet.getRange(i,2).getValue().toString(); // On garde le sous-filtre
    }
    }

    var ligne = ss.getActiveSheet().getRange(cellule).getRow();
    var colonne = ss.getActiveSheet().getRange(cellule).getColumn();
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(filtre2Array).build();
    // sheet.getRange(ligne, colonne+1).setDataValidation(rule);

    return sheet.getRange(ligne, colonne+1).setDataValidation(rule);
    }
    ____________

    ERREUR : Vous n'êtes pas autorisé à appeler setDataValidation().

    J'ai bien l'impression qu'il n'est vraiment pas possible de créer une colonne de liste déroulante qui dépend d'une autre colonne de liste...

    Qu'en pensez-vous ?

  23. st3ph says:

    Voici un fichier avec un code qui marche
    en fait requirevalidationinlist prend en entrée un tableau faut faire attention.

    Je n'ai pas regarder ton code mais dans la spreadsheet il y a une feuille data avec deux colonnes pour générer les listes déroulantes.

    https://docs.google.com/spreadsheets/d/1T_HyDd7okhsQ_GTLABQlLRmAW1eNFXtdrBn5YzfRgxs/edit?usp=sharing

    Le code donne :
    function customDataValidation(val) {

    var list = new Array();
    var j=0;
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
    var ranges = sheet.getRange(2,1,sheet.getLastRow(),2).getValues();
    for(var i in ranges){
    var range = ranges[i];
    if(range[0] == val){
    list[j] = range[1] ;
    j++;
    }
    }
    Logger.log(list);
    var cell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
    cell.setDataValidation(rule);

    }

    Stéphane

  24. Bonsoir

    Est ce que en utilisant ce script je pourrais avoir une liste déroulante conditionnelle sur plus qu'une seule cellule?

    Merci

  25. st3ph says:

    Bonjour

    Techniquement oui mais fonctionnellement le script que j'ai fait remplace la fonction par la liste déroulante donc si par la suite la liste change faudra reécrire la fonction dans la cellule pour que ça réimporte les bonnes données.

    Le script marche ça pas de pb mais au niveau usage ce ne sera pas aussi bien que des fonctions.

  26. Merci.
    J'ai essayé mais j'obtiens une liste déroulante sans rien (vide)
    Je me suis trompée quelque part?
    Vous pouvez me donner un accès total au fichier google que vous avez partagé pour voir ce que vous avez fait?
    Merci

  27. Bonjour,

    Juste un petit rafraîchissement de la page pour savoir si quelqu'un aurait trouvé une solution clean pour effectuer l'opération sur plusieurs lignes ?

    Merci !

  28. J'aimerais partagé ma solution malgré la date depuis longtemps dépassée.
    J'ai trouvé une solution plutôt simple au problème de liste déroulante en cascade rencontrée plus haut.

    Je résume un peu, on cherche ici à crée, sur plusieurs ligne, des listes en cascade en utilisant un filtre. Chaque filtre permet de faire une ligne en plus dans notre tableau. De ce fait, c'est très long à faire.

    Ma solution est la suivante: Créer un nouvel onglet "Filtre" et utilisé la formule "=TRANSPOSE(FILTER(plage; condition1; condition2))" pour que le filtre ne soit pas en colone MAIS en ligne, ce qui permet de simplement étiré la case un nombre illimité de fois!

    En espérant pouvoir aidé ceux qui passeront par ici :)

  29. Bonjour.

    Effectivement, avec TRANSPOSE on va plus loin en obtenant un second filtre dépendant de la première saisie et recopiable sur toutes les lignes. Mais comment fait-on pour déclarer la validation des données sur le second choix ?

  30. Unknown says:

    si quelqu'un a trouvé je suis preneuse!

Leave a Reply