Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets et Apps Script : créer un sommaire interactif des feuilles de calcul

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. […]

personnes ont consulté cet article

4 minutes

Rédigé par Antoine MARTIN - il y a 1 an

Ce que vous allez découvrir

  • Comment lister les feuilles d'un classeur Google Sheets ?
  • Comment changer le titre de la feuille et la masquer depuis le sommaire ?

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.

Ajoutez le script suivant qui se déclenche à chaque modification de valeur dans une cellule.
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.

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour
guest
7 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Dreadaxe

Super !!
Dommage de ne pas avoir les sommaires avec les noms des classeurs en hyperlink

Cet avis vous a été utile ?

Antoine MARTIN
Répondre à  Dreadaxe
1 année il y a

Bonjour Dreadaxe. pour ajouter le lien sur le nom il suffit de mettre en D1 :
=ArrayFormula(SI(A:A » »;SI(A:A=A1; »LIEN »;LIEN_HYPERTEXTE(B:B;A:A));))
bonne journée

Cet avis vous a été utile ?

Benoît
Répondre à  Antoine MARTIN
1 année il y a

Certes, mais ça disparait à chaque mise à jour ^^

Cet avis vous a été utile ?

Benoît
Répondre à  Antoine MARTIN
11 mois il y a

Excellent !

Cet avis vous a été utile ?

Benoît

Pouvoir changer l’ordre des feuilles ou la couleur depuis le sommaire serait top

Cet avis vous a été utile ?