Validation de données et Query

Question

J'avais dit que je reviendrai et voilà chose faite J'ai découvert les Query et Importrange depuis la dernière fois et c'est absolument fabuleux. Mais je suis confrontée à un problème de validation de données. Sur mon document j'ai une feuille "Import" où je rapatrie des réponses d'un formulaire. Sur cette feuille, en bout d'importation j'ai des colonnes supplémentaires avec des calculs en fonction des colonnes importées (je ne les ai pas créer dans mon fichier test, la fonction Query prenant le résultat de la colonne quelque soit la façon dont ce résultat a été obtenu) Je ne veux pas travailler sur la feuille "Import" car elle me sert pour différentes autres feuilles et donc contient des colonnes qui servent seulement sur certaines d'entre elles. J'ai donc créé plusieurs feuilles dont une "Calcul" où je récupère de la feuille "Import" seulement les colonnes qui m'intéressent. Pour ce faire, j'ai utiliser la fonction Query. Dans cette feuille "Calcul", En bout de ligne, je veux rajouter une colonne "Avancement" construite sur une validation de données. Je voudrais que le choix que j'ai fait dans ma liste déroulante soit attribué à l'ID que j'ai en début de ligne. A l'heure actuelle, si je rajoute une ligne dans ma feuille "Import", ma feuille "Calcul" se modifie en fonction de mon tri dans le Query mais mes cellules "Avancement" ne bougent pas. J'ai construit un petit fichier sachant que j'ai beaucoup plus de lignes et de colonnes mais c'est le principe qui m'intéresse. Au départ, j'ai trois lignes dans ma feuille "Import" J'obtient le résultat voulu sur la feuille "Calcul" et je peux modifie les données de la colonne "Avancement" Si je rajoute une quatrième ligne "manuellement pour l'exemple" dans ma feuille "Import" (en ID, je mets 220413) Vu mon tri, elle va passer tout en haut de ma feuille Calcul (dans mon document réel, le tri se fait sur des dates et non l'ID) Les réponses de la liste déroulante ne sont pas décaler. Je ne sais pas si c'est possible à faire Voici le lien de mon fichier test. Je l'ai laissé dans la première configuration avant le rajout de la nouvelle ligne https://docs.google.com/spreadsheets/d/1DHY43pWz_t_1n9ynrEbHyQzmycAwCw21uPznQA6g3JU/edit?usp=sharing Merci de votre aide Co...

Steelson · Accepted Answer

Une solution plus générale alors

https://docs.google.com/spreadsheets/d/1IgOShrH4101GA3zf7VIMkcQs8KMotu5e2veC6jvvdPI/copy

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  if (sh.getName() != 'Calcul avec BdD') return;
  var editRange = {
    top: 2,
    bottom: sh.getLastRow(),
    left: 4,
    right: 6
  };
  var derColBdD = columnToLetter(editRange.right - editRange.left +2)
  var bdd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BdD')
  var der = bdd.getLastRow()
  var ids = bdd.getRange(1, 1, der, 1).getValues().flat()
  var n = 1
  for (var i = e.range.rowStart; i <= e.range.rowEnd; i++) {
    var id = sh.getRange(i, 1).getValue()
    var row = ids.indexOf(id) + 1
    if (row == 0) {
      row = der + n++
      bdd.getRange(row, 1).setValue(id)
    }
    if (i >= editRange.top && i <= editRange.bottom) {
      for (var j = e.range.columnStart; j <= e.range.columnEnd; j++) {
        if (j >= editRange.left && j <= editRange.right) {
          var c = (j - editRange.left + 2)
          bdd.getRange(row, c).setValue(sh.getRange(i, j).getValue())
          sh.getRange(i, j).setFormula(`iferror(vlookup($A${i};BdD!$A:$${derColBdD};${c};0))`)
        }
      }
    }
  }
}
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

Steelson · Answer

C'est assez classique quand on travaille sur 2 feuilles qui doivent être "coordonnées" l'accès au fichier est refusé, peux-tu partager ? https://www.sheets-pratique.com/fr/cours/partage...

Steelson · Answer

Merci je te fais une proposition demain...

Steelson · Answer

Comme convenu ...

une solution basique
une solution plus élaborée (à adapter s'il y a plusieurs colonnes), j'ai dû modifier la validation de données pour permettre une remise à blanc de la zone Avancement

C'est l'identifiant qui fait le lien entre les 2 onglets.

1-

Dans le premier cas Calcul liste IDs manuelle, il faut lister manuellement les identifiants (en bleu) que l'on souhaite conserver (et surtout SANS formule avec Import sinon cela ne serait pas stable.

On peut y ramener les données de Import comme suit (formule en jaune)

=arrayformula(iferror(vlookup(A1:A;Import!A:C;{2\3};0)))

et ensuite ajouter les infos en colonnes D et suivantes (dans la mesure où la liste en A est mise manuellement, il n'y aura pas de mélange par la suite).

2-

Dans le second cas Calcul avec BdD, les données sont collectées comme tu le faisais

=QUERY(Import!A:C   ;"SELECT A,C,B ORDER BY A DESC")

en colonne D, j'ai la liste de validation (qui DOIT permettre une valeur nulle, c'est pourquoi j'ai changé la liste de validation)

Quand tu saisiras une donnée, elle sera reportée dans un onglet BdD et sera remplacée par une formule fonction de la ligne en question

=iferror(vlookup(A4;BdD!A:B;2;0))

https://docs.google.com/spreadsheets/d/1qVtCYte0CrNSy2CZqj2bVeqaakEwAauuUO2jCf4toX8/copy

Steelson · Answer

Le code permet de modifier plusieurs lignes en même temps par un copier/coller par exemple function onEdit(e) { var sh = e.source.getActiveSheet() if (sh.getName() != 'Calcul avec BdD') return; var editRange = { top: 2, bottom: sh.getLastRow(), left: 4, right: 4 }; var bdd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BdD') var der = bdd.getLastRow() var ids = bdd.getRange(1, 1, der, 1).getValues().flat() var n = 1 for (var i = e.range.rowStart; i &lt;= e.range.rowEnd; i++) { if (i &gt;= editRange.top && i &lt;= editRange.bottom) { for (var j = e.range.columnStart; j &lt;= e.range.columnEnd; j++) { if (j &gt;= editRange.left && j &lt;= editRange.right) { var id = sh.getRange(i, 1).getValue() if (ids.indexOf(id) != -1) { bdd.getRange(ids.indexOf(id) + 1, 1, 1, 2).setValues(&#91;&#91;id, sh.getRange(i, j).getValue()&#93;&#93;) } else { bdd.getRange(der + n++, 1, 1, 2).setValues(&#91;&#91;id, sh.getRange(i, j).getValue()&#93;&#93;) } sh.getRange(i, j) .setFormula(`iferror(vlookup(A${i};BdD!A:B;2;0))`) } } } } }...