Google Sheets, Slides, Apps Script, générer des graphiques sur mesure : l’organigramme

Dans la série génération de graphique, après la jauge, je vous propose une nouvelle déclinaison : créer un organigramme.

Google Sheets permet de créer des graphiques de type organigramme :

Ce type de graphique affiche bien les données hiérarchisées, mais reste assez limité. Rendons à César ce qui lui appartient, l’outil de génération d’organigrammes de la suite Microsoft Office, « Smartart », est très performant : à partir d’une simple liste à puces, Word ou PowerPoint génère des organigrammes sur mesure, dynamiques et mis en forme.

L’organigramme généré par Google Sheets n’est pas modifiable directement, les couleurs, le nombre d’informations et leur mise en forme dans chaque étiquette sont très limitées, bref, une belle promesse, mais décevante.

Ni une, ni deux, ni rien du tout, j’ai pris mes cliques et mes claques et je me suis lancé dans la génération de graphiques sous forme d’organigramme.

À partir d’une table de données, je peux à présent produire un graphique de ce genre :

Voici un exposé de ma recherche et de la démarche que j’ai mise en place.

Qu’est-ce qu’un organigramme ?

Il s’agit d’un ensemble de cartes d’identités personnalisées, reliées entre elles par des connecteurs, réparties par niveau hiérarchique.

À partir de cette définition, j’ai mis en place la procédure suivante :

• Google Slides : créer un modèle d’étiquette avec des champs de fusion

• Google Sheets : saisir les données
• Google Apps Script : lier la feuille Sheets et la présentation Slides
  • Créer une étiquette pour chaque personne.
  • Remplacer les champs de fusion par les informations de chaque personne.
  • Créer les liens connectés entre le parent et l’enfant.
  • Organiser les étiquettes dans la page.

Dans cet exemple, je vous présente la construction d’un organigramme d’une équipe de huit personnes, affichant le prénom, le nom, la fonction, l’adresse e-mail de chaque membre. Chaque membre a un manager identifié par son adresse e-mail. Le manager doit bien évidemment faire partie de l’équipe. Le chef de l’équipe n’a pas de manager.

Préparez vos modèles d’étiquette

• Créez une présentation Google Slides.
• Supprimez toutes les zones de textes de la première diapositive.
• Créez un modèle d’étiquette dans la première diapositive :
  • Commencez impérativement par une ellipse (les points d’ancrage du connecteur sont plus nombreux à exploiter et c’est primordial de la créer en premier pour le script, vous pouvez la rendre transparente, mais l’ellipse doit être présente).
  • Ajoutez une image « user », dimensionnez-la et éventuellement appliquez une découpe selon une forme, ajoutez si vous le voulez une ombre…
  • Ajoutez une ou plusieurs zones de textes en y écrivant « prénom », « nom », « email », « fonction », à mettre en forme à votre convenance.
  • Ajoutez autant de formes, couleurs que vous le souhaitez.
  • Regroupez l’ensemble des formes, zones de textes et images dans un seul groupe.

Préparez vos données

• Créez une feuille de calcul Google Sheets.
• Nommez le premier onglet « Données ».
• Créez les colonnes suivantes en respectant l’ordre : « e-mail », « prénom », « nom », « fonction », « url image », « manager »
• Remplissez autant de lignes que vous le souhaitez avec les membres de votre équipe :
  • pour l’image : copiez-collez le lien de partage de l’image depuis votre Drive ;
  • pour le manager, ajoutez une validation des données en utilisant la colonne « e-mail ».

Script complet pour générer un organigramme avec Google Slides

Je vous propose de copier-coller ce script directement dans votre éditeur de script depuis la feuille de calcul Sheets. N’oubliez pas d’insérer l’ID de votre présentation Google Slides contenant le modèle d’étiquette dans la constante « presentationId ».

/******************
 *
 *
 * NUMERICOACH - AMCMS - Tous droits réservés 2021
 *
 *
 ******************/
const presentationId = "METTRE l'ID DE LA PRESENTATION GOOGLE SLIDES";
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("NUMERICOACH").addItem("Générer l'organigramme", "genArbo").addToUi();
}
function genArbo() {
  // Ouvrir le Slides
  const presentation = SlidesApp.openById(presentationId);
  const presentationWidth = presentation.getPageWidth();
  const presentationHeight = presentation.getPageHeight();
  const presentationMarge = 20 ;
  const firstSlide = presentation.getSlides()[0];
  const groupModele = firstSlide.getGroups()[0];
if (groupModele)
{
  const groupModeleWidth = groupModele.getWidth();
  const groupModeleHeight = groupModele.getHeight();
  const newSlide = presentation.appendSlide();
  newSlide.insertTextBox("Organigramme " + (new Date())).setWidth(700);
  // ouvrir le SpreadSheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetDonnees = ss.getSheetByName("Données");
  // lire les données
  const donnees = sheetDonnees.getDataRange().getValues();
  let userCard = {};
  let arbo = {};
  // créer le card
  donnees.forEach(function (donnee, k) {
    if (k > 0) {
      const email = donnee[0];
      const prenom = donnee[1];
      const nom = donnee[2];
      const image = donnee[3];
      const fonction = donnee[4];
      let emailManager = donnee[5];
      if (emailManager == "") emailManager = 0;
      const newCard = newSlide.insertGroup(groupModele);
      newSlide.replaceAllText("email", email);
      newSlide.replaceAllText("prenom", prenom);
      newSlide.replaceAllText("nom", nom);
      newSlide.replaceAllText("fonction", fonction);
      // Extraire l'ID
      const idImage = image.match(/([\w|\-|_]{33})/)[1];
      const png = DriveApp.getFileById(idImage).getAs("image/png");
      newCard.getChildren().forEach(function (child) {
        if (child.getPageElementType() == "IMAGE") {
          child.asImage().replace(png);
        };
      })
      userCard[email] = newCard.getObjectId();
      if (arbo[emailManager] == undefined) {
        arbo[emailManager] = [];
      }
      arbo[emailManager].push(email);
    }
  })
  // arborescence
  let generations = {};
  function arboCard(parent, generation) {
    if (generations[generation] == undefined) { generations[generation] = []; }
    generations[generation].push(userCard[parent]);
    generation++;
    if (arbo[parent] != undefined) {
      arbo[parent].forEach(function (enfant) {
        if (parent != 0) {
          // Creer le connecteur entre le parent et chaque enfant
          const cardParent = presentation.getPageElementById(userCard[parent]);
          const locationStart = cardParent.asGroup().getChildren()[0].getConnectionSites()[4];
          const cardEnfant = presentation.getPageElementById(userCard[enfant]);
          const locationEnd = cardEnfant.asGroup().getChildren()[0].getConnectionSites()[0];
          const newLine = newSlide.insertLine(SlidesApp.LineCategory.BENT, locationStart, locationEnd);
          newLine.sendToBack;
        }
        arboCard(enfant, generation);
      })
    }
  }
  arboCard(0, 0);
  const nbGenerations = Object.keys(generations).length;
  // organiser les cards dans le slide
  for (const gene in generations) {
    const cards = generations[gene];
    // mettre le 1 à gauche et le dernier à droite
    if (cards != null && cards.length > 0 && cards[0] != null) {
      if (cards.length == 1) {
        newSlide.getPageElementById(cards[0]).alignOnPage(SlidesApp.AlignmentPosition.HORIZONTAL_CENTER);
      }
      else {
        // Répartir les elements dans la largeur
        cards.forEach(function (thisCardId, nCard) {
          Logger.log(gene+" "+thisCardId+" "+cards.length+" "+nCard)
          let decalageH = groupModeleWidth/2;
          let decalageV = groupModeleHeight/2;
          if (nCard == 0) {
            decalageH = -presentationMarge ;
          }
          if (gene == 1)
          {
            decalageV = -presentationMarge ;
          }
          const thisCard = newSlide.getPageElementById(thisCardId);
          thisCard.setLeft( (((presentationWidth-2*presentationMarge) / (cards.length - 1)) * nCard ) - decalageH);
          thisCard.setTop( (((presentationHeight-2*presentationMarge) / (nbGenerations - 2)) * (gene - 1) ) - decalageV);
        })
      }
    }
  }
}else
{
SpreadsheetApp.getUi().alert("Aucun groupe n'a été trouvé dans la présentation. Il faut regrouper les éléments pour en faire un groupe ");
}
}

La génération de l’organigramme se déroule en trois étapes.

Une première boucle sur les données pour :

• créer les étiquettes en elles-mêmes ;
• mémoriser l’ID de chaque étiquette propre à chaque membre ;
• construire dans un objet la généalogie des membres de l’équipe.

Une deuxième boucle pour créer les connecteurs entre chaque étiquette.

Une troisième boucle qui s’appelle elle-même tant qu’un parent a des enfants, pour répartir par génération les étiquettes dans la page.

Si vous suivez bien chaque étape, vous devriez voir apparaître le bouton « génération d’organigramme » dans la feuille de données Google Sheets. Cliquez et rendez-vous dans la présentation Google Slides, une nouvelle diapositive contenant l’organigramme sera générée au bout de quelques secondes.

Le script est encore très statique, il reste beaucoup de choses à améliorer pour le rendre plus souple et plus interactif. Mais les grandes lignes sont là.

Merci pour vos retours sur l’utilisation de ce script dans les commentaires ! Avez-vous d’autres besoins de développements de graphiques sur mesure ?