Si la ligne contient telle valeur, la copier dans une nouvelle feuille

Bonjour à tous,

J'ai extrêmement besoin de votre aide !

En effet, j'ai créé un google forms pour des inscriptions aux concerts.
Mon problème concerne le traitement des données :

En effet, je souhaiterais créer des feuilles pour chaque concerts (que l'on retrouve à la colonne P de l'onglet '1er tri'), pour pouvoir ensuite retrouver les informations des écoles dans les feuilles de chaque concerts.

J'ai vu qu'une personne avait à peu près le même problème mais je n'arrive pas à utiliser la macro.

Bien à vous,

Sophie

J'utilisais la fonction tableau croisé dynamique, d'où les feuille 'Détail 9 ..." par exemple. Seulement, lorsque je sélectionne 'concert' comme valeur, il ne prend en compte qu'une seul colonne et non pas toutes les colonnes des concerts.
Je ne sais pas si mon explications est très claire.

Sophie

Bonjour Sophie,

je me prends une copie pour ne pas gêner le travail d'autres personnes et préserver les données ... je regarde

Oh c'est super, merci beaucoup ! Si vous avez besoin de jeter un coup d'œil au questionnaire, je peux vous transmettre le lien.
N'hésitez pas si vous avez une question sur le document. Merci beaucoup !!

En fait l'an dernier, les inscriptions se faisait par e-mail, et nous rentrions les données dans un tableau sur google sheet (voici le lien pour vous donner une idée du résultat final souhaité) : https://docs.google.com/spreadsheets/d/1NCXuk74Ug0-DNDumSgpxDtQtguKOOtfX/edit#gid=1978770089

Cette année, nous souhaitons mettre en place un formulaire, pour automatiser cette saisie.
Nous cherchons donc à ce que les réponses soient triés par concert.
Par exemple, si une école s'est inscrite au spectacle de cendrillon, il faudrait que sa ligne se copie dans un onglet "cendrillon".

Sachant que les écoles peuvent demander d'assister à plusieurs concerts.

Dans le fichier des traitements des données du formulaire, j'ai du réaliser un premier tri, car dans le premier onglet, comme il y a plusieurs sections dans le formulaire en fonction du niveau de classe, les données se "séparer".
Je les ai donc toutes rassemblaient dans l'onglet "1er tri".

L'onglet "réponse au formulaire" est automatisé avec notre questionnaire, lorsque quelqu'un réponde, une ligne s'affiche.
Les réponses actuelles sont les miennes, j'ai tester toutes les réponses possibles pour pouvoir tester le traitement de données avant de diffuser le formulaire officiellement.

C'est très bien, belle application formulaire > Google sheets

Je comprends mieux aussi la foultitude d'informations dans une seule et même cellule puisque ce sont des choix multiples possibles.

1- Pourquoi à la fin de la formule il y a 'Réponses au formulaire 1'!BN2:BU2 ? n'est-ce pas plutôt 'Réponses au formulaire 1'!BN2 ?

2- Autre remarque, mieux vaut utiliser

=TEXTJOIN(", ";true;

que

=JOIN(", ";

L'objectif pour moi est de constituer en automatique une base de données qui permettra ensuite de filtrer ou d'assurer la vision par TCD.

edit : j'étais parti sur une formule, mais c'est trop complexe pour la mettre au point d'ici ce soir, et donc trop risqué pour maintenir, je vais donc repartir sur un script assez simple.

1 - Dans quelle cellule s'affiche cette formule ?

J'ai en effet essayé le TCD, mais je n'ai pas réussi, j'ai cru lire quelque part que le TCD ne marchait pas sur les données fractionnées...

D'accord, après si vous pensez que la formule est la meilleure des solutions je vous fait confiance !

Pensez-vous que c'est le questionnaire le problème ?

Le questionnaire est très bien, pas de soucis (pour le moment)

En A9 de l'onglet '1er tri' tu sembles avoir un décalage d'une ligne dans ta formule qui pointe ver la ligne 10 des réponses du formulaire ! Pour résoudre cela, tu peux mettre en A1

={'Réponses au formulaire 1'!A1;arrayformula('Réponses au formulaire 1'!A2:A)}

en effaçant tout ce qui est en dessous : la formule se propagera à toute la colonne.

Tu peux aussi copier/coller cette formule sur les en-têtes des autres colonnes qui ne font appel qu'à une seule donnée des réponses du formulaire

Comment comptes-tu traiter les données d'effectifs car tu cumules plusieurs réponses dans la même case ? comme en H ou J

Autre question pour moi, dans 1er tri, tu as aussi construit la colonne J, faut-il aussi créer une seconde base de données avec cela ?

Pour le moment, reste à résoudre certains points, voici où j'en suis :

- j'ai remplacé

=JOIN(", ";

par

=TEXTJOIN(", ";true;

a minima pour la colonne P

- crée un onglet BdD (comme base de données

- ensuite fais outils > éditeur de script et colle ceci

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('↓ Synthèse ↓')
    .addItem('Établir la base de données', 'etablirBdd')
    .addToUi();
}
function etablirBdd(){
  var doc = SpreadsheetApp.getActiveSpreadsheet()
  var bdd = doc.getSheetByName('BdD')
  bdd.getRange(2,1,getLastDataRow(bdd,'A')-1,bdd.getLastColumn()).clearContent()
  var src = doc.getSheetByName('1er tri')
  var resultat = []
  var data = src.getRange(2,1,getLastDataRow(src,'A')-1,src.getLastColumn()).getValues()
  for (var i=0; i<data.length; i++){
    var liste = data[i][15].split(', ')
    liste.forEach(function(item){
      var prov = []
      for (var j=0; j<15; j++){
        if (j==9){prov.push('')}
        else {prov.push(data[i][j])}
      }
      prov.push(item)
      resultat.push(prov)
    })
  }
  bdd.getRange(2, 1, resultat.length, resultat[0].length).setValues(resultat);
}
function getLastDataRow(sheet,col) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

exécute onOpen(), google demandera d'autoriser ce script (3 étapes), et ensuite retourne sur la feuille, tu as un menu en haut à droite, clique sur ↓ Synthèse ↓ puis sur Établir la base de données

Tu as à ce stade une base de données que l'on pourra ensuite utiliser par TCD ou filtre ou segments.

Faut-il maintenant éclater la nouvelle colonne P par un split en séparant la représentation, la date ...

Ah oui effectivement, j'ai un décalage, lorsque je copie colle la formule en A2 et que je tire ca m'affiche "#REF".

J'avoue que je n'avais pas du tout pensé à l'effectif, peut être que je vais devoir utiliser SPLIT ?

Pour la colonne J, ça sera juste un renseignement pour savoir si ils se sont inscrit. Mais il est vrai qu'il peut être très intéressant de construire une nouvelle base de données à partir de la colonne J. On peut imaginer une copie du document qui nous fournis les informations sur les regards croisés (les regards croisés sont des ateliers pour les enseignants et sont disponibles pour presque tous les spectacles).
Si il suffit d'interposer le script de la colonne P pour la colonne J, alors effectivement je suis intéressée pour construire une nouvelle base de données.

Ok je vais essayer de mettre en place le script (je n'en ai fait qu'une fois sur Excel, donc je ne sais pas si je vais bien y arriver). Je vous dirais si cela fonctionne :) !

Ce message s'affiche lorsque j'appuie sur "synthèse" :

Parce que l'onglet s'appelait Bdd et non BdD ... mais il y avait aussi une correction à apporter.

Je vais maintenant poursuivre sur ce fichier, je vais corriger les formules, reste quand même à voir pour les effectifs !!

que fait-on de la colonne M de "1er tri" ?

je pense que l'onglet "1er tri" ne permet pas de voir les effectifs associés à chaque représentation ! je vais relire le questionnaire ...

edit : selon le questionnaire, il n'y a qu'une seule section de renseignée au final entre les sections 2 et la 15 ... donc pour les effectifs cela devrait être ok

La colonne M du "1er tri" était-t 'elle là au départ ? Ou l'avait vous rajouté ?

Je ne me rappelais pas l'avoir avant.

Non en effet nous ne connaissons pas encore notre jauge pour chaque représentation.

En revanche, concernant l'effectif, le but étant de créer un onglet par spectacle, je pense qu'il sera donc envisageable de créer un total de la colonne effectif, non ?

Au pire des cas, nous enlèverons à la main les virgules en trop dans les cellules de la colonne 'effectif' ?

Non je n'ai pas ajouté la colonne M.

Je m'étais appuyé sur ton "1er tri" très pratique, mais je me demande s'il ne faut pas puiser directement les infos du script dans les réponses au formulaire. Je pensais que les sections étaient toutes de la même structure à partir de la 2 mais ce n'est pas vraiment le cas.

Du coup, je suis quand même perdu dans ton document. En fait où se trouvent les ateliers et spectacles (quelles colonnes) soit dans les réponses, soit dans 1er tri ... mais de préférences dans les réponses. Ce sera plus "logique".

Bonsoir,

désolée, je viens seulement de voir votre message.
Les demandes d'inscriptions au spectacle sont trop désorganisées, c'est pourquoi je les ai rassemblé dans l'onglet "1er tri".

Tous les ateliers et spectacles sont à la colonne P.

Pas mal de chose ont changé dans le document d'origine, j'avoue que ça m'embête un peu, en fait je pense que la colonne M s'est créée lors de l'utilisation de la formule ={'Réponses au formulaire 1'!A1;arrayformula('Réponses au formulaire 1'!A2:A)}

Je vais peut être essayer de retrouver les données d'origine parce que c'était vraiment à partir de l'onglet de base '1er tri' qu'il fallait partir (ma responsable m'avait demandé qu'il apparaisse plusieurs critères, comme par exemple la fonction split où l'on peut voir chaque spectacle choisis par l'école cote à côte).

J'ai contacté Google atelier numérique qui m'ont orienté vers la formule suivante : =FILTER('1er tri'!A2:V200;RegExMatch('1er tri'!P2:P200;I1))

Merci,

Sophie

Je suis navré si j'ai effectivement supprimé des feuilles ou des fonctions. Note aussi que le formulaire dont tu as donné l'accès, l'est en fait pour tout le monde en édition, donc vérifie qu'il n'y a pas de conséquence. Pour la suite, donne seulement le fichier en lecture à tous, de toute façon généralement je travaille sur une copie.

Pour moi il y a 2 voies (complémentaires) possibles :

1- Si tout est dans la colonne P, alors le script suivant suffit

function etablirBdd(){
  var doc = SpreadsheetApp.getActiveSpreadsheet()
  var bdd = doc.getSheetByName('BdD')
  bdd.getRange(2,1,Math.max(3,getLastDataRow(bdd,'A')-1),bdd.getLastColumn()).clearContent()
  var src = doc.getSheetByName('1er tri')
  var resultat = []
  var data = src.getRange(2,1,getLastDataRow(src,'A')-1,src.getLastColumn()).getValues()
  for (var i=0; i<data.length; i++){
    var liste = data[i][15].split(', ')
    liste.forEach(function(item){
      var prov = []
      for (var j=0; j<15; j++){prov.push(data[i][j])}
      prov.push(item)
      resultat.push(prov)
    })
  }
  bdd.getRange(2, 1, resultat.length, resultat[0].length).setValues(resultat);
}
function getLastDataRow(sheet,col) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

2- Je pense qu'il serait judicieux de travailler avec query qui est plus puissant que filter et permet notamment de retrouver un atelier/spectacle dans plusieurs colonnes. J'ai juste fait un exemple pour les Fables de la Fontaine sur un onglet

=query(BdD!A1:P;"select * where P like '%"&A1&"%' ";1)

tu peux remplacer * par les lettres de colonnes à retenir comme par exemple

=query(BdD!A1:P;"select B,C,D,E,F,P where P like '%"&A1&"%' ";1)