Script nouvelle feuille automatique Le sujet est résolu

[Petziboy]

Bonjour,

J'ai un classeur Google sheets dans lequel j'ai sur une feuille principale (liée à un Google Forms) intitulée "Base adhérents" (c'est un exemple).
Je souhaiterais, à chaque nouvelle ligne importée dans cette feuille, créer une nouvelle feuille.

Cette nouvelle feuille devra :

- Dupliquer tout le contenu d'une autre feuille du classeur, déjà existante (nommée "Modèle") ;

- Dupliquer la nouvelle ligne créée de la feuille "Base adhérents", dans la ligne 2 de cette nouvelle feuille ;

- Porter un nom (dans son onglet) en fonction de deux cellules de cette nouvelle ligne. Si par exemple ces cellules se trouvent en colonnes F et T de la nouvelle ligne, ma nouvelle feuille devra s'appeler "Association [contenu cellule F...] version [contenu cellule T...]" ;

Il m’est impossible de le faire à la main compte-tenu du nombre de données à traiter.

J'espère avoir été clair dans ma recherche et vous remercie par avance !

[Thierry]

Hello,

S'agit il d'un besoin perso ou professionnel ?
Sais-tu scripter ou manipuler les scripts ?
Quel est le degré d'urgence ?
Besoin que tu partages ici un exemple fictif, voire que tu fasses une vidéo loom explicative.
A réception, je branche l'équipe de champions de Numericoach !

[NC-Ahmet]

Hello Petziboy,

Je pense avoir la réponse à ta question, à savoir un script qui : 

• s'exécute à la réception d'un formulaire associé à ton classeur Google Sheets ;
• qui crée une nouvelle feuille en partant d'un modèle ;
• qui récupère les valeurs insérées par l'utilisateur dans le formulaire pour les insérer dans la ligne 2 de cette nouvelle feuille ;
• qui nomme cette feuille nouvellement créée en : Association ${nomAsso} version ${versionAsso}.

Voici le script qui te permet de réaliser cela :

Code : Tout sélectionner

const creerNouvelleFeuille = (e) => {
  const COL_ASSO = "B";
  const COL_VERSION = "C";
  const valeurs = e.values;
  const sheet = SpreadsheetApp.getActive();
  const modele = sheet.getSheetByName("Modèle");
 
  const titre = {
    nomAsso: valeurs[COL_ASSO.toLowerCase().charCodeAt(0) - 97],
    versionAsso: valeurs[COL_VERSION.toLowerCase().charCodeAt(0) - 97],
  };
  const nouvelleFeuille = sheet.insertSheet(
    `Association ${titre.nomAsso} version ${titre.versionAsso}`,
    { template: modele }
  );
  nouvelleFeuille.getRange(2, 1, 1, valeurs.length).setValues([valeurs]);
};

Attention, tu dois modifier plusieurs éléments. Dans la ligne 2 et la ligne 3 tu as les variables suivantes :

Code : Tout sélectionner

const COL_ASSO = "B";
const COL_VERSION = "C";

Tu devras remplacer "B" par la lettre représentant la colonne où se trouve le nom de l'asso que tu souhaites insérer dans ton nom de feuille Association ${nomAsso}.
Puis, tu devras remplacer "C" par la lettre représentant la colonne où se trouve la version que tu souhaites insérer dans ton nom de feuille version ${versionAsso}.
(Dans ton message, tu as donné comme exemple les colonnes T et F, si tu vois ce que je veux dire ! )

Aussi, il faut que tu mettes en place un déclencheur automatique pour que cette fonction s'exécute lorsque quelqu'un répond à ton formulaire.

Pour se faire, tu vas dans l'interface Google Apps Script, tu cliques sur Déclencheur :


Ensuite, tu pourras créer un nouveau déclencheur qui s'exécute lors de l'envoi d'un formulaire. À toi de voir à quelle fréquence tu veux configurer tes notifications en cas d'échec. Voici la manip en image : 


En principe à partir de là, tout sera fonctionnel. N'hésite pas à tester et reviens vers nous si tu as des questions ou si ça ne convient pas tout à fait.

En espérant que ma réponse soit à hauteur de tes attentes !

A+++

[Petziboy]

Merci !
Je n'y connais rien en script mais j'apprends...
J'essaye ça dans la semaine et j'essaye de vous répondre avant ce week-end.
Encore merci à vous deux d'avoir pris le temps de me répondre !

[Petziboy]

SUPER GÉNIAL NC-Ahmet !!! Merci mille fois !
Ça marche nickel !!!

Seul problème : au bout d'une douzaine de feuilles, le déclencheur ne peut plus générer d'autres feuilles et se met en erreur (alors que je devrais avoir une cinquantaine de feuilles) :
"Exception: Si vous effectuez cette action, vous dépasserez le nombre maximum de 10000000 cellules autorisé dans le classeur. -at creerNouvelleFeuille(Code:12:33)"

Je ne sais pas comment contourner ce problème.
Une idée ?...

[NC-Ahmet]

Hello,

Il semblerait que tu as beaucoup trop de cellules dans ton classeur...

Regarde voir dans ta feuille "Modèle" le nombre de lignes/colonnes que tu possèdes.

Supprime celles qui sont inutiles. Pareil dans tes autres feuilles.

Ça ne sert à rien d'avoir 10000 lignes ou colonnes si tu en utilises que 10.

Bon weekend !
A+

[Petziboy]

En effet, ça va mieux après un brin de ménage !

Une dernière question : ma feuille modèle est protégée en écriture (sauf certaines cellules qui serviront à la saisie). Y a-t-il un moyen pour que les feuilles dupliquées automatiquement soient pareillement protégées ?
Merci encore !
Bonne soirée.

[ChristopherH]

Coucou Petziboy 

J'éspère que tu vas bien.

Pour répondre à ta question, il est possible de gérer, ajouter ou supprimer des protections en Google Apps Script via la classe Protection.

Je t'invite à consulter la documentation officielle présente juste ici

Dans ton cas, il sera donc nécessaire à chaque création de feuille de récupérer cette feuille dans une variable puis d'y attribuer la protection de ton choix.

Voici un exemple : 

var ss = SpreadsheetApp.getActive();
ss.insertSheet().setName("hello");
var newSheet = ss.getSheetByName("hello");
var range = ss.getRange("A:A");
newSheet.protect().setUnprotectedRanges([range]);

Ici, je créé une feuille "hello" que je protège ensuite entièrement à l'exception de la colonne A.

Les méthodes à regarder sont protect() et setUnprotectedRanges().

En espérant que ça règle ton soucis ! N'hésite pas à revenir vers nous si besoin.

Si tu manques trop de temps ou de connaissances, n'hésite pas à prendre un rdv juste ici : nous proposons des formations et développements sur mesure 

Bonne soirée,

Christopher

[Petziboy]

Merci Christopher,

En effet, je suis une bille donc je n'y comprends pas grand chose.
Le script proposé plus haut par NC-Hamet fonctionne parfaitement mais j'aimerais que lors de chaque création automatique d'une nouvelle feuille dans Google Sheets (à chaque formulaire rempli dans le Google Forms lié), cette nouvelle feuille, basée sur ma feuille "Modèle", puisse être automatiquement protégée de la même façon que ma feuille "Modèle". Que dois-je ajouter à son script ?

Je fais ce boulot pour mon association, je n'ai pas trop le temps de me former, hélas !

Merci beaucoup !

Fred



 

[NC-Ahmet]

Coucou Petziboy,

Navré pour le délai de réponse et merci Christopher pour ta contribution !

Je reprends ton script, voici :

Code : Tout sélectionner

const creerNouvelleFeuille = (e) => {
  const COL_ASSO = "B";
  const COL_VERSION = "C";
  const valeurs = e.values;
  const sheet = SpreadsheetApp.getActive();
  const modele = sheet.getSheetByName("Modèle");
  const protection = modele.getProtections(
    SpreadsheetApp.ProtectionType.SHEET
  )[0];
  const titre = {
    nomAsso: valeurs[COL_ASSO.toLowerCase().charCodeAt(0) - 97],
    versionAsso: valeurs[COL_VERSION.toLowerCase().charCodeAt(0) - 97],
  };
  const nouvelleFeuille = sheet.insertSheet(
    `Association ${titre.nomAsso} version ${titre.versionAsso}`,
    { template: modele }
  );
  const nouvelleProtection = nouvelleFeuille.protect();
  nouvelleFeuille.getRange(2, 1, 1, valeurs.length).setValues([valeurs]);
  dupliquerProtection(protection, nouvelleProtection, nouvelleFeuille);
};

const dupliquerProtection = (p, p2, s) => {
  const nouvellesPlages = [];
  p2.setDescription(p.getDescription()).setWarningOnly(p.isWarningOnly());
  if (!p.isWarningOnly()) { p2.removeEditors(p2.getEditors()).addEditors(p.getEditors()) };
  for (plage of p.getUnprotectedRanges()) { nouvellesPlages.push(s.getRange(plage.getA1Notation())) };
  p2.setUnprotectedRanges(nouvellesPlages);
};

Attention encore une fois pense bien à mettre à jour les deux lignes const COL_ASSO = "B" ainsi que const COL_VERSION = "C" avec les colonnes qui vont bien.

Le script a pas mal changé donc fait bien un copier/coller intégral.

A+