Google Sheets et Apps Script : créer un sommaire interactif des feuilles de calcul

Votre classeur Sheets contient de nombreux onglets, vous souhaitez en afficher la liste complète dans une seule feuille et les contrôler depuis ce sommaire ? Voici un script qui permet de répondre à ce besoin.

Comment lister les feuilles d’un classeur Google Sheets ?

Le besoin : Afficher dans un onglet « Sommaire » toutes les feuilles du classeur avec pour chacune : son titre, son URL et son statut de masquage (Case cochée si masquée).

• Ouvrez un classeur Google Sheets contenant déjà plusieurs feuilles.

Avant tout ajout de script les utilisateurs sont alertés des risques encourus. Les auteurs du blog ne pourront être tenus responsables en cas de mauvaise utilisation de ce script. https://thierryvanoffe.com/mentions-legales/

• Ouvrez l’éditeur de scripts :

• Ecrivez ou recopiez ce code qui permet d’afficher le menu dans votre classeur :

function onOpen()
{
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Sommaire interactif").addItem("Mettre à jour le sommaire","resumeSheets").addToUi();
}
function resumeSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let resumeValues = [["Nom", "URL", "Masquée ?"]];
  const ssUrl = ss.getUrl();
  sheets.forEach(function (sheet) {
    resumeValues.push([sheet.getName(), ssUrl + "#gid=" + sheet.getSheetId(), sheet.isSheetHidden()]);
  })
  let sheetSommaire = ss.getSheetByName("Sommaire");
  if (sheetSommaire == null) {
    sheetSommaire = ss.insertSheet("Sommaire", 0);
  }
  sheetSommaire
    .clearContents()
    .getRange(1, 1, resumeValues.length, resumeValues[0].length)
    .setValues(resumeValues);
  sheetSommaire.getRange(2, 3, resumeValues.length - 1, 1).insertCheckboxes();
}

La première fonction onOpen() crée un nouvel onglet dans la barre de menus à l’ouverture du classeur :

La deuxième fonction déclenche la création du sommaire des onglets :

• const ss = SpreadsheetApp.getActiveSpreadsheet();
• const ssUrl = ss.getUrl();
  • la constante « ss » désigne le classeur actif, et ssUrl son url (nous en aurons besoin plus tard pour construire un lien vers la feuille)
• const sheets = ss.getSheets();
  • la constante sheets désigne l’ensemble des feuilles du classeur.
• let resumeValues = [[« Nom », « URL », « Masquée ? »]];
  • le tableau de tableaux resumeValues intégre une première ligne de titre du sommaire.
• sheets.forEach(function (sheet) {
  • nous listons toutes les feuilles
• resumeValues.push([sheet.getName(), ssUrl + « #gid= » + sheet.getSheetId(), sheet.isSheetHidden()]);
  • et nous ajoutons dans le tableau resumeValues, un sous-tableau contenant toutes les valeurs de chaque ligne :
    • sheet.getName() : le titre de la feuille ;
    • ssUrl + « #gid= » + sheet.getSheetId() : la concaténation de l’URL du classeur et de l’argument « #gid= » contenant l’ID de la feuille.
    • sheet.isSheetHidden() : la valeur booléenne True ou False du masquage de la feuille.
• })
• let sheetSommaire = ss.getSheetByName(« Sommaire »);
  • la variable SheetSommaire recherche la feuille appelée « Sommaire » dans le classeur.
• if (sheetSommaire == null) {
  • si la feuille « Sommaire » n’existe pas…
• sheetSommaire = ss.insertSheet(« Sommaire », 0);
  • nous la créons et nous la plaçons en tout début du classeur, avec l’index « 0 » ;
• }
• sheetSommaire
• .clearContents()
  • supprimons toutes les anciennes valeurs de la feuille ;
• .getRange(1, 1, resumeValues.length, resumeValues[0].length)
  • nous récupérons la plage qui va être remplie par notre tableau de tableaux de valeurs ;
• .setValues(resumeValues);
  • nous ajoutons les valeurs
• sheetSommaire.getRange(2, 3, resumeValues.length – 1, 1).insertCheckboxes();
  • nous transformons la troisième colonne en cases à cocher, plus sexy que les simples « True » ou « False »;
• }

Sauvegardez le script, lancez le script « onOpen » depuis la console ou en rafraichissant la page affichant le classeur ;

Lancez la création du sommaire depuis le classeur :

Comment changer le titre de la feuille et la masquer depuis le sommaire ?

Nous allons à présent rendre ce sommaire interactif.

Besoin : Quand je change le nom d’une feuille dans le sommaire, la feuille doit également être renommée, quand je coche la case de la colonne Masquée, la feuille se masque et quand je décoche elle réapparaît.

function onEdit(e) {
  const range = e.range;
  const rangeSheetId = range.getSheet().getSheetId();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetSommaire = ss.getSheetByName("Sommaire");
  const sheetSommaireId = sheetSommaire.getSheetId();
  if (rangeSheetId == sheetSommaireId) {
    const rangeRow = range.getRow();
    const rangeCol = range.getColumn();
    const rangeValue = range.getValue();
    if (rangeCol == 1) {
      // CHANGER LE NOM
      const sheetOldName = e.oldValue;
      ss.toast(sheetOldName);
      Logger.log(sheetOldName);
      ss.getSheetByName(sheetOldName).setName(rangeValue);
      ss.toast("La feuille " + sheetOldName + " a été renommée en " + rangeValue);
    }
    if (rangeCol == 3) {
      // MASQUER / AFFICHER la feuille
      const sheetNom = sheetSommaire.getRange(rangeRow, 1).getValue();
      const sheetCible = ss.getSheetByName(sheetNom);
      if (rangeValue) {
        sheetCible.hideSheet();
        ss.toast("La feuille " + sheetNom + " a été masquée.");
      }
      else {
        sheetCible.showSheet();
        ss.toast("La feuille " + sheetNom + " a été affichée.");
      }
    }
  }
}

• const range = e.range;
  • récupérons la plage qui est modifiée ;
• const rangeSheetId = range.getSheet().getSheetId();
  • récupérons l’ID de la feuille modifiée ;
• const ss = SpreadsheetApp.getActiveSpreadsheet();
• const sheetSommaire = ss.getSheetByName(« Sommaire »);
• const sheetSommaireId = sheetSommaire.getSheetId();
  • l’ID de la feuille « Sommaire » ;
• if (sourceSheetId == sheetSommaireId) {
  • si c’est bien la feuille Sommaire qui a été modifiée ;
• const rangeRow = range.getRow();
  const rangeCol = range.getColumn();
  const rangeValue = range.getValue();
  • récupérons la ligne, la colonne et la valeur modifiée ;
• if (rangeCol == 1) {
  • si c’est la première colonne qui est modifiée :
• const sheetOldName = e.oldValue;
  • nous récupérons l’ancienne valeur de la cellule avec la valeur oldValue de l’objet « e » récupéré dans le onEdit();
• ss.getSheetByName(sheetOldName).setName(rangeValue);
  • nous renommons la feuille qui avait l’ancienne valeur de la cellule comme nom, avec la nouvelle valeur;
• ss.toast(« La feuille  » + sheetOldName +  » a été renommée en  » + rangeValue);
  • un message qui surgit comme une tartine d’un toaster alerte discrètement l’utilisateur de l’action qui vient d’être effectuée.
    }
• if (rangeCol == 3) {
  • si c’est la troisième colonne qui est modifiée :
• const sheetNom = sheetSommaire.getRange(rangeRow, 1).getValue();
  • récupérons le nom de la feuille à masquer (la valeur qui est dans la feuille sommaire, même ligne que la cellule modifiée, première colonne) ;
• const sheetCible = ss.getSheetByName(sheetNom);
  • récupérons la feuille à masquer ;
• if (rangeValue) {
  • si la case est cochée :
• sheetCible.hideSheet();
  • masquer la feuille;
• ss.toast(« La feuille  » + sheetNom +  » a été masquée. »);
  }
• else {
  • si la case est décochée :
• sheetCible.showSheet();
  • afficher la feuille.
• ss.toast(« La feuille  » + sheetNom +  » a été affichée. »);
• }
• }
• }
• }

Ce script pose les bases d’un système de contrôle de l’ensemble de vos feuilles depuis un seul sommaire. A vous d’imaginer les utilisations possibles : modifier la couleur des onglets, leur protection, leur ordre… découvrez les nouvelles fonctionnalités dans cet article !

Une autre utilisation serait de mettre en place le contrôle à distance de plusieurs classeurs depuis un seul classeur. Vous pourriez ainsi visualiser les feuilles des classeurs de vos collaborateurs afin de les modifier (les classeurs, pas les collaborateurs… 🙂 Google n’a pas la main sur les personnes… pour l’instant) depuis un seul endroit !

N’hésitez pas à nous contacter si vous avez besoin d’accompagnement sur des projets de ce type.