Créer un sommaire dynamique dans Google Sheets avec Google Apps Script
Dans cet article, nous allons explorer un script Google Apps Script qui permet de générer automatiquement une feuille “Info” dans votre fichier Google Sheets. Cette feuille sert de sommaire et recense l’ensemble des feuilles du classeur en y ajoutant des liens hypertextes pour faciliter la navigation. Décortiquons ensemble le code et ses fonctionnalités.
Présentation générale du script
Le script a pour objectif de :
- Ajouter un menu personnalisé dans Google Sheets pour lancer la création de la feuille “Info”.
- Créer dynamiquement la feuille “Info” si elle n’existe pas, ou la nettoyer si elle est déjà présente.
- Positionner l’onglet “Info” en première position.
- Afficher des titres et sous-titres formatés (comme “Résumé”, “Contenu” et “Informations”).
- Lister toutes les feuilles du classeur en ajoutant des liens hypertextes vers chacune d’elles.
- Mettre en forme certains éléments (colonnes, bordures, couleurs de police) pour une meilleure lisibilité.
Les fonctions clés du script
1. L’ajout d’un menu personnalisé
function onOpen(e) {
const ui = SpreadsheetApp.getUi();
ui.createAddonMenu()
.addItem('Générer une feuille Info', 'creerFeuilleInfo')
.addToUi();
}
La fonction onOpen est déclenchée à l’ouverture du document. Elle utilise l’interface utilisateur de Google Sheets pour créer un menu personnalisé (ici, via createAddonMenu()) et y ajoute l’option Générer une feuille Info qui exécute la fonction creerFeuilleInfo lorsque l’utilisateur clique dessus.
Pour que le menu apparaisse également lors de l’installation de l’add-on, la fonction onInstall appelle tout simplement onOpen :
function onInstall(e) {
onOpen(e);
}
2. La création et la configuration de la feuille “Info”
La fonction principale du script est creerFeuilleInfo. Voici comment elle fonctionne, étape par étape :
a. Récupération du classeur et vérification de l’existence de la feuille
const tableurActif = SpreadsheetApp.getActiveSpreadsheet();
const nomFeuilleInfo = 'Info';
const proprietaire = tableurActif.getOwner();
// Vérifie si la feuille existe déjà
let feuilleInfo = tableurActif.getSheetByName(nomFeuilleInfo);
if (!feuilleInfo) {
feuilleInfo = tableurActif.insertSheet(nomFeuilleInfo);
} else {
feuilleInfo.clear(); // Nettoie la feuille si elle existe déjà
}
Ici, on récupère le classeur actif et on vérifie si une feuille nommée “Info” existe déjà. Si ce n’est pas le cas, la feuille est créée. Sinon, elle est simplement nettoyée pour être réutilisée.
b. Ajustements de la mise en forme
Le script configure ensuite certains paramètres de la feuille “Info” :
• Largeur de la colonne B : La largeur de la colonne B est fixée à 10 pixels pour un alignement optimal.
feuilleInfo.setColumnWidth(2, 10);
• Suppression des colonnes superflues : Si la feuille contient plus de colonnes que nécessaire (au-delà de la colonne K, soit la 11e colonne), les colonnes supplémentaires sont supprimées.
const dernierIndexColonne = 11; // K correspond à la colonne 11
const dernierIndexFeuille = feuilleInfo.getMaxColumns();
if (dernierIndexFeuille > dernierIndexColonne) {
feuilleInfo.deleteColumns(dernierIndexColonne + 1, dernierIndexFeuille - dernierIndexColonne);
}
• Positionnement de l’onglet : La feuille “Info” est déplacée en première position du classeur afin d’être immédiatement accessible.
tableurActif.moveActiveSheet(0);
c. Création des titres et sous-titres
Plusieurs plages de cellules sont fusionnées et formatées pour afficher un titre et différents sous-titres :
• Titre principal : La plage C3:J3 est fusionnée, reçoit le nom du classeur et est stylisée avec une police “Open Sans”, taille 26, en gras, avec un fond vert (#34a853) et un texte blanc.
const plageTitre = feuilleInfo.getRange('C3:J3');
plageTitre.merge();
plageTitre.setValue(tableurActif.getName());
plageTitre.setFontFamily('Open Sans').setFontSize(26).setFontWeight('bold').setHorizontalAlignment('left');
plageTitre.setBackground('#34a853').setFontColor('#FFFFFF');
• Sous-titres : De la même manière, les plages pour “Résumé” (C6:J6), “Contenu” (C13:J13) et “Informations” (C21:J21) sont créées et formatées.
d. Génération du sommaire avec liens hypertextes
Le script dresse ensuite la liste des feuilles du classeur et y insère des liens hypertextes :
1. Création de la liste des feuilles
On récupère toutes les feuilles et on construit un tableau en plaçant “Info” en première position, suivi des autres feuilles (en excluant la feuille “Info” déjà listée).
const feuilles = tableurActif.getSheets(); const nomsFeuilles = [nomFeuilleInfo, ...feuilles.map(feuille => feuille.getName()).filter(nom => nom !== nomFeuilleInfo)];
2. Insertion des noms dans la feuille
À partir de la ligne 15 (définie par ligneDebut), le script insère la liste des noms de feuilles dans la colonne C.
const ligneDebut = 15;
if (nomsFeuilles.length > 0) {
const plageNomsFeuilles = feuilleInfo.getRange(ligneDebut, 3, nomsFeuilles.length, 1);
plageNomsFeuilles.setValues(nomsFeuilles.map(nom => [nom]));
}
3. Création des liens hypertextes
Pour chaque feuille (à partir de la deuxième, puisque la première est la feuille “Info” elle-même), le script génère une formule HYPERLINK qui, une fois cliquée, amène l’utilisateur directement à la feuille correspondante.
const formulesHyperliens = nomsFeuilles.slice(1).map(nom => {
const idFeuille = tableurActif.getSheetByName(nom).getSheetId();
return [`=HYPERLINK("#gid=${idFeuille}&range=A1"; "${nom}")`];
});
const plageHyperliens = feuilleInfo.getRange(ligneDebut + 1, 3, nomsFeuilles.length - 1, 1);
plageHyperliens.setFormulas(formulesHyperliens);
plageHyperliens.setFontColors(Array(nomsFeuilles.length - 1).fill(['#1155cc']));
plageHyperliens.setFontLines(Array(nomsFeuilles.length - 1).fill(['underline']));
Ainsi, chaque lien redirige vers la cellule A1 de la feuille ciblée en utilisant l’ID unique de celle-ci.
4. Ajout d’informations complémentaires
En plus de la liste, le script ajoute dans la cellule C23 une mention du propriétaire du classeur, pour indiquer qui a généré cette feuille.
feuilleInfo.getRange('C23').setValue(`Construit par ${proprietaire}`).setFontStyle('italic');
e. Mise en forme graphique : les bordures
Pour renforcer l’aspect visuel, le script ajoute une bordure verte épaisse sur certaines colonnes et plages de cellules (notamment sur la colonne B et autour des sections “Résumé” et “Informations”).
// Bordure sur la colonne B pour la liste des feuilles const plageBordure = feuilleInfo.getRange(ligneDebut, 2, nomsFeuilles.length, 1); plageBordure.setBorder(null, true, null, null, null, null, '#0f9e59', SpreadsheetApp.BorderStyle.SOLID_THICK); // Bordure pour la section "Résumé" const plageBordureResume = feuilleInfo.getRange(8, 2, 4, 1); plageBordureResume.setBorder(null, true, null, null, null, null, '#0f9e59', SpreadsheetApp.BorderStyle.SOLID_THICK); // Bordure pour la section "Informations" const plageBordureInformations = feuilleInfo.getRange(ligneDebut + nomsFeuilles.length + 4, 2, 1, 1); plageBordureInformations.setBorder(null, true, null, null, null, null, '#0f9e59', SpreadsheetApp.BorderStyle.SOLID_THICK);
Ces bordures apportent une touche visuelle qui aide à délimiter et structurer les différentes parties de la feuille.
Conclusion
Ce script est un excellent exemple d’automatisation dans Google Sheets. En quelques lignes de code, il permet de :
• Créer un sommaire dynamique qui liste toutes les feuilles du classeur.
• Ajouter des liens hypertextes pour naviguer rapidement entre les onglets.
• Mettre en forme le document pour qu’il soit à la fois esthétique et fonctionnel.
En adaptant et en personnalisant ce code (modification des couleurs, des plages, etc.), vous pouvez l’intégrer à vos propres projets et améliorer considérablement la navigation et la gestion de vos fichiers Google Sheets.
N’hésitez pas à laisser vos commentaires ou questions ci-dessous si vous souhaitez approfondir certains aspects ou partager vos propres adaptations du script !
J’espère que cet article vous aidera à mieux comprendre et exploiter la puissance de Google Apps Script pour automatiser vos tâches dans Google Sheets. Bonne personnalisation et à bientôt pour de nouvelles astuces !