Macro: créer de nouveaux onglets selon un template nommés selon une liste

Le 21/06/2022 à 04:51

Bonjour à tous,

Je maîtrise les bases (voir un peu plus) d'Excel. Je me suis mise à travailler avec Sheets depuis bientôt un an, et en général je trouve toujours sur le web les réponses à mes questions. Mais là! il est 04h30 du matin et j'ai littéralement passé la nuit à essayer de trouver une solution qui ne doit pas être bien compliquée mais pas moyen! En même temps je suis totalement débutante en matière de Macros.

J'espère pouvoir trouver auprès de vous mon bonheur.

J'ai une feuille de calcul dans laquelle j'encode tous les détails de voyages de mon équipe "travelsheet"

Chaque voyage possède un numéro de séquence propre à un aller/retour (c'est un numero de "TMO", p. ex: 01.002 ...03.001) et ces numéros sont dans ma colonne A.

Je voudrais trouver une macro (dans Sheets) pour pouvoir établir par "TMO" un Nouvel onglet qui serait nommé avec le numero TMO correspondant et qui reprendrait un fichier template (petite précision: dans ma colonne A, il y a parfois des cases vides).

Déjà si j'arrive à ça, je saute de joie!

Et puis, cerise sur le gateau, si j'arrive à intégrer les informations de voyages liées au TMO correspondant dans les cases de mon template, je serai vraiment très contente. Ces informations (nom, prénom, n° de vol, dates, destinations,..) se trouvent sur la ligne face au "TMO" correspondant.

Un grand merci d'avance,

s

Le 21/06/2022 à 05:40

Bonjour,

j'espère pouvoir résoudre cette énigme !

ds07 a écrit :

qui reprendrait un fichier template

est-ce un fichier séparé ou une feuille de ton fichier ?

y a t'il des protections à gérer ?

Le 21/06/2022 à 05:45

As-tu une maquette de ce que tu souhaites ?

Voici une première ébauche simplissime

function duplicateTemplate() {
  let template = SpreadsheetApp.getActive().getSheetByName('template')
  let tmo = SpreadsheetApp.getActive().getSheetByName('data').getRange("A2:A").getDisplayValues().flat() 

  tmo.forEach(t => {
    template.getRange("A1").activate()
    let onglet = SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet()
    onglet.setName(t)
  })
};
Le 21/06/2022 à 10:53

Bonjour Steelson,

Merci pour ta réponse rapide. J'ai essayé de coller la macro que tu as créée mais elle coince.

Donc j'ai créer une ébauche de ce que je voudrais, en espérant que tu puisses ouvrir ce lien:

https://docs.google.com/spreadsheets/d/1QoMjeZAvne0_vgziTCSvcvvdOvBWd9KzVm9QRL2Zink/edit#gid=2124375...

Merci

Le 21/06/2022 à 11:19

Il faudrait que tu partages a minima en lecture (ou bien répondre à ma demande d'accès)

Le 21/06/2022 à 12:06
ds07 a écrit :

Merci pour ta réponse rapide. J'ai essayé de coller la macro que tu as créée mais elle coince.

je me doutais bien qu'il pouvait y avoir des cellules vides dans A et des feuilles déjà crées.

le point le plus névralgique est que je suppose qu'il faut modifier les formules dans les feuilles

Le 21/06/2022 à 12:09

Pourquoi dans le template on trouve des formules faisant référence à des lignes différentes ? notamment 22 et 82 ?

[ [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '=TRAVELSHEET!A4', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '=TRAVELSHEET!C4', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '=TODAY()', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '=TRAVELSHEET!J4', '', '', '=TRAVELSHEET!L4' ],
  [ '', '', '', '=TRAVELSHEET!I4', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '=TRAVELSHEET!D4', '', '', '' ],
  [ '', '=TRAVELSHEET!E4', '', '=TRAVELSHEET!E4', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '=TRAVELSHEET!G4', '', '' ],
  [ '', '', '=TRAVELSHEET!K4', '=TRAVELSHEET!J4', '' ],
  [ '', '', '=TRAVELSHEET!M4', '=TRAVELSHEET!L4', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '=TRAVELSHEET!N4' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '=TRAVELSHEET!O4', '' ],
  [ '', '=TRAVELSHEET!J82', '', '', '=TRAVELSHEET!L82' ],
  [ '', '', '', '=TRAVELSHEET!I82', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '=TRAVELSHEET!H82', '', '', '' ],
  [ '', '=TRAVELSHEET!D82', '', '=TRAVELSHEET!E22', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '=TRAVELSHEET!G82', '', '' ],
  [ '', '', '=TRAVELSHEET!K82', '=TRAVELSHEET!J22', '' ],
  [ '', '', '=TRAVELSHEET!M82', '=TRAVELSHEET!L22', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '=TRAVELSHEET!N22' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '', '' ],
  [ '', '', '', '=TRAVELSHEET!O22', '' ],
  [ '', '', '', '', '' ] ]
Le 21/06/2022 à 13:03

Effectivement c'est une coquille j'ai été trop vite.

Toutes les infos sont regroupées par lignes,

néanmoins j'ai une ligne qui correspond à un "aller"

et une ligne différente pour le "retour" qui sont regroupées dans un même numéro "TMO"

(c'est pour matcher avec la chronologie du calendrier) .

Merci,

Le 21/06/2022 à 13:28

Est-ce que les infos aller et retour sont sur la même ligne ? car je ne vois pas de lignes doublées.

Tu peux essayer ceci sur une copie (à condition que le template ne fasse appel qu'aux informations de la ligne 4

function duplicateTemplate() {
  let template = SpreadsheetApp.getActive().getSheetByName('TMO')
  let tmo = SpreadsheetApp.getActive().getSheetByName('TRAVELSHEET').getRange("A4:A").getDisplayValues().flat()
  tmo.forEach((t, i) => {
    if (t != '' && i > 0) {
      if (!isSheetExist('TMO ' + t)) {
        template.getRange("A1").activate()
        let onglet = SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet()
        onglet.setName('TMO ' + t)
        update(onglet, +i + 1)
      }
    }
  })
  SpreadsheetApp.getActive().toast("Fin de recopies et mises à jour !");
};
function isSheetExist(sheetNameToCheck) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetNameToCheck);
  return (sheet == null ? false : true)
}
function update(sheet, row) {
  const ranges = sheet
    .createTextFinder('4')
    .matchFormulaText(true)
    .findAll();
  ranges.forEach(r => {
    if (r.getFormula() != '') {
      r.setFormula(r.getFormula().replace('4', row))
    }
  });
}
Le 21/06/2022 à 15:44

C'est pas encore tout à fait ça, mais pour moi c'est déjà phénoménal!

La barre d'onglet crée effectivement les onglets

Idéalement il faudrait que ça suive la chronologie de gauche à droite (le 1er TMO serait 01.00. serait juste à côté de "TMO" (en 3eme place), et ainsi de suite...)

Je retrouve bien mon template "TMO" dans chaque nouvel onglet qui combine bien 2 sources de données différentes dans ma "TRAVELSHEET"

Bon après , les informations dans chaque cases ne sont pas les bonnes, et notamment le titre dans le document qui doit être le même que celui de l'onglet ne l'est pas toujours (ex: tab "TMO 09.001" , son titre écrit en début de c-document doit être MO 09.001)

Mais c'est vraiment un mini souci pcq ça je dois pouvoir le résoudre dans mon template de départ avec un "IF" probablement. (Genre si le tab est MO 09.001, trouver les info dans la ligne correspondante).

Ce serait peut-être plus simple si je distinguais le TMO 0x.00XA (aller) du TMO 0x.00XR (retour) dans ma Travelsheet?

Déjà un tout grand merci! pari gagné, je suis heureuse!

S

image
Le 21/06/2022 à 17:10

Pour la chronologie, il suffit de faire reverse()

  tmo.reverse().forEach((t, i) => {
    if (t != '' && i > 0) {
      if (!isSheetExist('TMO ' + t)) {
        template.getRange("A1").activate()
        let onglet = SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet()
        onglet.setName('TMO ' + t)
        update(onglet, +i + 1)
      }
    }
  })

Et pour le reste, je suis d'accord, il faut rester simple et standard. La seule chose que fait la macro (après duplication bien sûr), c'est de modifier sur toutes les fonctions et uniquement les fonctions le chiffre 4 avec le n° de la ligne.

Où se situe le titre ? le mieux serait de mettre dans le template

="MO "&TRAVELSHEET!A4

Par contre je viens de me rendre compte / comprendre en effet que des n° de la colonne A sont doublés et correspondent à un aller et un retour. Donc si tu peux mettre A et R c'est parfait ! Dans ce cas, je remplacerai le chiffre 82 par la ligne du retour et le chiffre 4 du template par l'aller.

Ce qui me chiffonne ici, c'est bâtir un template sur des lignes qui pourraient bouger, par insertion ou suppression de ligne car le 4 (comme le 82) sont en "dur" dans le code. Il faudrait pouvoir figer complètement ces 2 lignes qui pourraient être fictives.

Il faudra aussi préciser s'il y a toujours un retour ou pas ! là cela devient plus complexe !

Rechercher des sujets similaires à "macro creer nouveaux onglets template nommes liste"