Format personnalisé sous condition

Question

C'est pourtant très simple comme demande mais je n'ai pas trouvé (ou alors mal cherché). Colonne A j'ai des cellules avec un menu déroulant (3 options) Colonne B je rentre une valeur numérique Je souhaite que cette valeur s'affiche avec une unité différente selon l'option choisie en A Exemple Choix de l'option 1 en A, je tape 15 en B et ça s'affiche 15 m² Choix de l'option 2 en A, je tape 35 en B et ça s'affiche 35 ml Choix de l'option 3 en A, je tape 20 en B et ça s'affiche 20 kg C'est dommage que "mise en forme conditionnelle" ne fasse pas ce boulot. Avez-vous une solution à me proposer ? Par avance merci...

PierreLepinay · Accepted Answer

Il y a toujours une solution : (modifies juste le nom de la feuille)

const NOMENCLATURE = {
  "Masse": "kg",
  "Liquide": "ml",
  "Surface": "m2"
};

function onEdit(e) {
  const sheet = e.range.getSheet();
  const range = e.range;
  if (sheet.getName() === "Feuille 1" && range.getColumn() === 2) { 
    const unit = NOMENCLATURE[sheet.getRange(range.getRow(), 1).getValue()];
    sheet.getRange(range.getRow(), range.getColumn()).setValue(range.getValue() + " " + unit);
  }
}

Le même script avec la doc (si besoin) :

/**
 * Dictionnaire associant une catégorie à son unité de mesure.
 * @type {Object.<string, string>}
 */
const NOMENCLATURE = {
  "Masse": "kg",
  "Liquide": "ml",
  "Surface": "m2"
};

/**
 * Déclenché automatiquement lors de la modification d'une cellule dans la feuille.
 * Ajoute l'unité de mesure à la valeur de la cellule si elle se trouve dans la colonne 2 de la feuille "Feuille 1".
 * 
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e - Événement de modification de la feuille.
 */
function onEdit(e) {
  const sheet = e.range.getSheet();  // Récupère la feuille où a eu lieu la modif
  const range = e.range; //  Récupère la range de modif
  if (sheet.getName() === "Feuille 1" && range.getColumn() === 2) {  // Vérifie si la modification a eu lieu dans la colonne 2 de la feuille "Feuille 1" A CHANGER.
    const unit = NOMENCLATURE[sheet.getRange(range.getRow(), 1).getValue()];  // Récupère la catégorie depuis la première colonne de la même ligne, la passe dans la nommenclature pour retourner l'unitée.
    sheet.getRange(range.getRow(), range.getColumn()).setValue(range.getValue() + " " + unit); // Ajoute l'unité de mesure à la valeur de la cellule.
  }
}

Si possibilité avec une formule, je l'ignore.

PierreLepinay · Accepted Answer

Si tu as des erreurs, utilise la version "complexe" : /** * Dictionnaire associant une catégorie à son unité de mesure. * @type {Object.&lt;string, string&gt;} */ const NOMENCLATURE = { "1.place de ponton": "ml", "2.terre-plein": "m²", "3.hangar": "m²" }; /** * Déclenché automatiquement lors de la modification d'une cellule dans la feuille. * Ajoute l'unité de mesure à la valeur de la cellule si elle se trouve dans la colonne 2 de la feuille "Feuille 1". * * @param {GoogleAppsScript.Events.SheetsOnEdit} e - Événement de modification de la feuille. */ function onEdit(e) { const sheet = e.range.getSheet(); // Récupère la feuille où a eu lieu la modif const range = e.range; // Récupère la range de modif const value = range.getValue(); // Récupère la valeur de la cellule modifiée if (sheet.getName() === "LOCATIONS" && range.getColumn() === 11 && value !=="") { if (typeof value !== "number") { console.error("L'entrée " + value + " située en " + range.getA1Notation() + " n'est pas un nombre"); Browser.msgBox("Valeur incorrecte en " + range.getA1Notation()); return; // Arrête l'exécution de la fonction si la valeur n'est pas un nombre } const unit = NOMENCLATURE&#91;sheet.getRange(range.getRow(), 10).getValue()&#93;; if (unit) { sheet.getRange(range.getRow(), range.getColumn()).setValue(value + " " + unit); } else { console.error("Unité introuvable pour la mesure : " + sheet.getRange(range.getRow(), 1).getValue() + " veuillez vérifier la nomenclature."); Browser.msgBox("Erreur nomenclature pour la mesure : " + sheet.getRange(range.getRow(), 1).getValue() + " veuillez consulter les logs."); } } }...

PierreLepinay · Answer

Il y a sans doute plus optimale mais assez simplement, si tu n'as que 3 catégories, une imbrication de SI ? Ex : =SI(A2="Surface";B2&" m²";SI(A2="Liquide";B2&" ml";SI(A2="Masse";B2&" kg";""))) EDIT : pour plus de robustesse, je ferais un tableau de correspondance dans une feuille "paramétrage" avec les unités et leurs terminaisons =B2&" "&RECHERCHEX(A2;'Parametrage'!A:A;'Parametrage'!B:B;"")...

saboh12617 · Answer

Alternativement, la manière "correcte" de procéder dans votre cas n'est pas trop une MFC mais une table de référence. Vous entrez dans la 1e colonne vos valeurs recherchées, et dans la 2e les correspondance. Puis vous utilisez Fonction RECHERCHEX - Aide Éditeurs Google Docs pour trouver le résultat. Par exemple Liquide mL Masse Kg Surface m2 Ensuite vous utilisez le même procédé que Pierre. Alternativement, vous pouvez aussi l'utiliser en MFC en définissant 1 MFC par type et en vérifiant la valeur de la cellule A de la ligne correspondante. Par exemple : =$A1="Liquide" Sans MFC vous perdez le type des données, qui passe de nombre à texte, avec MFC vous le gardez mais c'est beaucoup plus de travail surtout si vous avez beaucoup d'unités. De plus les MFC peuvent vite alourdir un classeur. EDIT : je viens de voir l'edit de Pierre, qui va dans le meme sens que moi...

saboh12617 · Answer

Joli script Pierre, peut-etre rajouter un cas par défaut (affectation d'une string nulle en amont?) pour gérer le cas ou la recherche échoue (valeur non tabulée, problèmes de majuscules et d'accents…)...

PierreLepinay · Answer

EDIT : réponse à Saboh

Quand je propose un script, c'est en réponse à un énoncé donné, je n'inclus pas de gestion des erreurs, logs, try...catch ou autre, car j'ignore les tenants / aboutissants.

Mais si dans cet exemple, on voulait rendre + fiable, il nécessiterait plusieurs points de vérifications :

e = une donnée

e = un nombre

unit = définit

Voici comment nous pourrions faire avec à la fois des logs et info user avec message :

/**
 * Dictionnaire associant une catégorie à son unité de mesure.
 * @type {Object.<string, string>}
 */
const NOMENCLATURE = {
  "Masse": "kg",
  "Liquide": "ml",
  "Surface": "m2"
};

/**
 * Déclenché automatiquement lors de la modification d'une cellule dans la feuille.
 * Ajoute l'unité de mesure à la valeur de la cellule si elle se trouve dans la colonne 2 de la feuille "Feuille 1".
 * 
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e - Événement de modification de la feuille.
 */
function onEdit(e) {
  const sheet = e.range.getSheet();  // Récupère la feuille où a eu lieu la modif
  const range = e.range; //  Récupère la range de modif
  const value = range.getValue(); // Récupère la valeur de la cellule modifiée

  if (sheet.getName() === "Feuille 1" && range.getColumn() === 2 && value !=="") { 
    if (typeof value !== "number") {
      console.error("L'entrée " + value + " située en " + range.getA1Notation() + " n'est pas un nombre");
      Browser.msgBox("Valeur incorrecte en " + range.getA1Notation());
      return; // Arrête l'exécution de la fonction si la valeur n'est pas un nombre
    }

    const unit = NOMENCLATURE[sheet.getRange(range.getRow(), 1).getValue()];  

    if (unit) {
      sheet.getRange(range.getRow(), range.getColumn()).setValue(value + " " + unit); 
    } else {
      console.error("Unité introuvable pour la mesure : " + sheet.getRange(range.getRow(), 1).getValue() + " veuillez vérifier la nomenclature.");
      Browser.msgBox("Erreur nomenclature pour la mesure : " + sheet.getRange(range.getRow(), 1).getValue() + " veuillez consulter les logs.");
    }
  }
}

Après, il est toujours possible d'aller plus loin, mettre en rouge la donnée erronée par exemple, en gras, la supprimer, etc.

@numerus ici :

const unit = NOMENCLATURE[sheet.getRange(range.getRow(), 1).getValue()];

on va chercher l'élément de la liste déroulante dans la 1ere colonne, est-ce que les listes sont bien en 1ere colonne ?

PierreLepinay · Answer

Dans ce cas remplace : const unit = NOMENCLATURE&#91;sheet.getRange(range.getRow(), 1).getValue()&#93;; par : const unit = NOMENCLATURE&#91;sheet.getRange(range.getRow(), 10).getValue()&#93;; Concernant la réponse à Saboh en effet, de base je ne prévois pas de vérification, car partant du postulat que les données sont fiables (liste déroulante obligatoire par ex), surtout que + de vérification = script plus lent et plus long, pour des personnes ne connaissant pas forcément bien app script, "less is better" . Mais il est tout à fait possible d'enrichir en vérifications, protections et parades diverses afin de pallier de mauvais usages, bug, etc... tout dépend de l'usage voulu, un formulaire pour une banque n'aura pas les mêmes contraintes qu'une inscription pour une asso...

PierreLepinay · Answer

Tu dois avoir des formules ? Car j'ai testé avec le script initial, pas d'erreurs :

Ajoute une vérification savoir si unit existe, remplace

sheet.getRange(range.getRow(), range.getColumn()).setValue(range.getValue() + " " + unit); // Ajoute l'unité de mesure à la valeur de la cellule.

par :

if(unit){sheet.getRange(range.getRow(), range.getColumn()).setValue(range.getValue() + " " + unit);}// Ajoute l'unité de mesure à la valeur de la cellule.