Picklists dépendantes les unes des autres

Le 26/01/2021 à 19:11

Bonsoir à tous,

Dans Excel, il est assez facile de faire des menus déroulants en définissant la plage de cellule de la seconde liste en fonction du choix de la première liste, avec des formules matricielle. Je n'arrive pas à reproduire simplement le même schéma dans Googlesheet; j'ai essayé de contacter le support pour qu'ils m'aident mais sans réel résultat. J'ai trouvé des pistes mais assez compliquées qui nécessitent d'utiliser un bouton et une définition manuelle des plages directement dans le code...

Voici le code que j'ai trouvé et qui me semble être une piste intéressante, mais trop compliquée à maintenir :

/**
* Updates dropdown lists based on a value on the same rows.
*
* To take this script into use:
* 
*  - take a backup of your spreadsheet through File > Make a copy
*  - select all the text in this script, starting at the first "/**" line above, and ending at the last "}"
*  - copy the script to the clipboard with Control+C (on a Mac, ⌘C)
*  - open the spreadsheet where you want to use the function
*  - choose Tools > Script editor > Blank (this opens a new tab in the browser)
*  - if you see just the 'function myFunction() {}' placeholder, press Control+A (on a Mac, ⌘A)
*    followed by Control+V (⌘V) to paste the script in
*  - otherwise, choose File > New > Script file, then press Control+A (on a Mac, ⌘A) followed by
*    Control+V (⌘V) to paste the script in
*  - if you have an existing onOpen(e) function, add the following line as the first line after the 
*    initial '{' in that onOpen(e) function:
*      createMenu_updateDropdownLists(e);
*    ...and then delete the onOpen(e) function below
*  - modify the ranges under "START modifiable parameters" as necessary
*  - press Control+S (⌘S) to save the script
*  - when prompted, name the file and project 'Update dropdown lists'
*  - choose Run > Run function > createMenu_updateDropdownLists
*  - review the permissions and grant the script the rights it needs
*  - close the script editor tab and go back to the spreadsheet tab
*  - the script will run when you click the menu bar and choose Update dropdown lists now
*
* 
* @OnlyCurrentDoc
*/

/**
* Simple trigger that runs each time the user opens the spreadsheet.
*
* @param {Object} e The onOpen() event object.
*/
function onOpen(e) {
  createMenu_updateDropdownLists(e);
}

/**
* Adds a menu and a menu item to the menu bar.
*/
function createMenu_updateDropdownLists(e) {
  SpreadsheetApp.getUi()
  .createMenu('Update dropdown lists')
  .addItem('Update dropdown lists now', 'updateDropdownLists')
  .addToUi();
}

/**
* Updates dropdown lists based on a value on the same rows.
*/
function updateDropdownLists() {
  // version 1.0, written by --Hyde, 25 January 2020
  //  
  var ss = SpreadsheetApp.getActive();
  var toastTitle = 'Update dropdown lists script';
  ss.toast('Updating lists...', toastTitle, /*timeoutSeconds*/ -1);
  try {

    ////////////////////////////////
    // [START modifiable parameters]
    var dropdownContentsRange = ss.getRange('SOURCE DONNEE - Ligne 1 = transpose des valeurs de la picklist 1 et chaque colonne = valeur source pour Picklist 2');
    var dropdownTypesRange = ss.getRange('PICKLIST 1');
    var dropdownListRange = ss.getRange('PICKLIST 2');
    // [END modifiable parameters]
    ////////////////////////////////

    var dropdownListNames = dropdownContentsRange.getValues()[0];
    var maxDropdownListValues = dropdownContentsRange.getLastRow();
    var dropdownTypes = dropdownTypesRange.getValues();
    for (var row = 0, numRows = dropdownTypes.length; row < numRows; row++) {
      var cellToValidate = dropdownListRange.offset(/*rowOffset*/ row, /*columnOffset*/ 0, /*numRows*/ 1, /*numColumns*/ 1);
      var type = dropdownTypes[row][0];
      var listIndex = dropdownListNames.indexOf(type);
      if (!type || listIndex === -1) {
        cellToValidate.setDataValidation(null); // remove the dropdown list when column A is blank or its value does not match any of the list names
        continue;
      }
      var validationRange = dropdownContentsRange.offset(/*rowOffset*/ 1, /*columnOffset*/ listIndex, /*numRows*/ maxDropdownListValues, /*numColumns*/ 1);
      var dropdownListRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      cellToValidate.setDataValidation(dropdownListRule);
    }
  } catch (error) {
    ss.toast(error.message, toastTitle, /*timeoutSeconds*/ 30);
    throw error;
  }
  ss.toast('Done.', toastTitle, /*timeoutSeconds*/ 5);
}

Idéalement, le déclencheur de la mise à jour de picklist 2 est le choix dans picklist 1 et on peut gérer plusieurs picklists dépendantes les unes des autres sans limitation sur plusieurs onglets...

Par avance, merci et à dispo pour mieux expliciter si besoin! :)

Le 26/01/2021 à 20:10

Bonjour,

Edouardo a écrit :

Bonsoir à tous,

Dans Excel, il est assez facile de faire des menus déroulants en définissant la plage de cellule de la seconde liste en fonction du choix de la première liste, avec des formules matricielle.

C'est tout aussi simple avec GSheets et surtout sans macro.

Un des exemples que Gilbert nous a "offert" https://forum.excel-pratique.com/sheets/reproduire-la-meme-cellule-deroulante-en-enlevant-le-choix-d...

S'il passe par ici il pourra t'expliquer plus en détail. Il y a des formules en A1 et B1 de l'onglet listes. On peut du reste utiliser aussi les manipulations puissantes avec query. Une fois qu'on a compris comment lister les éléments en fonction du premier choix, c'est immédiat (pas d déclencheur), simple, même plus simple à mon sens qu'avec excel.

Si tu souhaites une aide plus adaptée, mets un lien vers une copie significative de ton projet.

Le 26/01/2021 à 23:43

Merci Steelson; j'ai regardé la solution proposée mais le problème est un peu différent chez moi car je voudrais avoir une paire liste principale > liste fille par ligne, et ou les valeurs sont différentes dans la mère et dans la fille. par exemple :

Mere>ArtMathsLittératureSportFille>ModerneAlgebrePhilosophieCollectifAntiqueGeometrieContemporainsIndividuelBauhausesportClassique

Quand je choisi Maths, je voudrais que la picklist d'après me donne le choix antre Algebre et geometrie, ainsi de suite...

J'ai une validation classique pour la première (je prends la ligne 1 avec les en tete art, maths, etc)

Puis pour la liste fille, je tente de recomposer les coordonnées de la colonne de valeur correspondante de la sorte :

=indirect("Source!"&left(substitute(address(1;match($A3;Source!$1:$1;0));"$";"");len(substitute(address(1;match($A3;Source!$1:$1;0));"$";""))-1)&":"&left(substitute(address(1;match($A3;Source!$1:$1;0));"$";"");len(substitute(address(1;match($A3;Source!$1:$1;0));"$";""))-1))

Mais Googlesheet ne reconnait pas que c'est une plage de validation picklist, alors que ça marche sous xls...

Le 27/01/2021 à 02:11
Edouardo a écrit :

Merci Steelson; j'ai regardé la solution proposée mais le problème est un peu différent chez moi car je voudrais avoir une paire liste principale > liste fille par ligne, et ou les valeurs sont différentes dans la mère et dans la fille. par exemple :

Mere>ArtMathsLittératureSportFille>ModerneAlgebrePhilosophieCollectifAntiqueGeometrieContemporainsIndividuelBauhausesportClassique

Mais Googlesheet ne reconnait pas que c'est une plage de validation picklist, alors que ça marche sous xls...

IL ne faut pas raisonner avec la formule excel. C'est plus simple avec GSheets.

Mais as-tu la liste dans un fichier, car là ce n'est pas très clair ! Ou mieux, mets un lien vers une copie de ton projet. Ou donne une copie du fichier excel je me débrouillerai pour transposer sur GSheets.

Le 27/01/2021 à 10:22

Merci Steelson! j'ai tenté de rassembler dans le fichier ci-joint (gsheet converti)

6list-test.xlsx (20.82 Ko)
Le 27/01/2021 à 13:44

En effet, dans ton cas, si tu veux cette liste dépendante dans un tableau et non "one shot", tu as 2 solutions

  1. il te faut un script pour juste mettre à jour le critère de la formule =INDIRECT(___) : il faut que je le fasse
  2. mais si ton tableau n'est pas trop long et a priori figé, tu fais la validation à la main de la colonne 2 cellule par cellule comme l'exemple ci-dessous

https://docs.google.com/spreadsheets/d/1IDifehV68cEGlI1pqpvNLEnReZRLnyiZf-4ipFl-E3s/edit?usp=sharing

Le 27/01/2021 à 14:57

pour le point 1, avec les plages nommées

https://docs.google.com/spreadsheets/d/1rOi_eRf2_qhRLGys0BJdOvvaT4wqqaBq-6JU-g_2pSc/edit?usp=sharing

function onEdit(event){
  var f = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if ((r.getColumn() == 1) && (f.getName().toString() == 'Choix')){
    if (r.getValue() == ''){
      r.offset(0,1).clearDataValidations();
      r.offset(0,1).setValue('');
    }
    else{
      var listes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Listes');
      var matiere = r.getValue();
      r.offset(0,1).setDataValidation(SpreadsheetApp.newDataValidation()
        .setAllowInvalid(true)
        .requireValueInRange(listes.getRange(matiere), true)
        .build()); 
    } 
  }
}

c'est plus court que le charabia plus haut, et tu n'as pas besoin de déclencheur !

Le 27/01/2021 à 17:02

Merci Steelson! j'ai testé sur une copie de ton fichier ça fonctionne et est impressionnant! Mais quand je colle le code chez moi et lance la formule depuis l'éditeur j'ai TypeError: Cannot read property 'source' of undefined (ligne 2, fichier "Code") ou ça dans un autre ErrorTypeError: Cannot read property 'source' of undefinedonEdit@ Code.gs:2 ...vois tu pourquoi?

Le 27/01/2021 à 18:45

Le mieux est que tu me mettes un lien vers une copie de ton projet (en mp si nécessaire)

Le 27/01/2021 à 21:24

Regarde bien le code d'une part, et notamment le nom des feuilles, à changer si nécessaire

Ensuite regarde bien les zones nommées : tous les choix "1" doivent correspondre à une plage qui a été nommée (dans l'exemple, il y a une zone 'Art', une zone 'Maths' etc.)

Le 02/02/2021 à 18:00

Hello, est-il possible de faire référence à une cellule qui affiche le nom de l'onglet plutot que d'aller dans le script changer le nom de l'onglet qui contient les picklists? Ici dans le script on avait : ((columnToLetter(r.getColumn()) == 'E') && (f.getName().toString() == 'Budget UK & GER New'))...est ce que cela peut etre remplacé par une cellule donnée d'un onglet qui est prévue pour renvoyer le nom d'un onglet, car rentrer dans le script est compliqué. Si j'ai une cellule sur mon onglet synthèse, en C3 par exemple, qui renvoie Budget UK & GER New, puis je faire référence dans le script?

Merci bcp pour votre aide

Le 02/02/2021 à 20:02

Crée une variable comme suit

  var synth = doc.getSheetByName('synthèse');
  var param = synth.getRange('C3').getValue();
...........
if ((columnToLetter(r.getColumn()) == 'E') && (f.getName().toString() == param))
Rechercher des sujets similaires à "picklists dependantes"