Google Sheets, Slides, Apps Script : générer des graphiques sur mesure : la jauge

Google Sheets permet de créer rapidement un grand nombre de graphiques différents et optimise régulièrement les possibilités de mises en forme. Pour autant, il arrive qu’aucune des nombreuses possibilités graphiques ne permette de répondre à un besoin très précis. Voici un article sur la création de graphique sur mesure.

Un client m’a récemment demandé de créer un graphique sous forme de jauge, comprenant cinq secteurs différents. L’aiguille devait indiquer au centième près un pourcentage donné par une feuille de calcul.

Voici ce que Sheets propose :
(voir l’article suivant sur ce type de graphique)

Voici ce que le client souhaite :

La jauge native du tableur ne propose que trois zones, et graphiquement le client souhaitait un compteur personnalisable.

J’ai opté pour une solution de génération d’images à la volée en utilisant Google Slides.

Comment créer une image à partir d’un fichier Slides pour l’intégrer dans Sheets ?

Le principe général : un script, lancé depuis Sheets, appelle une présentation Google Slides, modèle représentant la jauge avec l’aiguille à zéro, modifie la rotation de l’aiguille en fonction de la valeur de Sheets, retourne la diapositive modifiée sous forme d’image et l’insère dans l’onglet Sheets.

Créer la présentation modèle

L’aiguille doit faire partie d’un groupe et comprend une partie transparente, image opposée de la partie visible, afin de déplacer l’axe de rotation de l’objet au niveau de l’axe de rotation de l’aiguille. En effet, le point de rotation de l’objet est toujours situé en son centre. Pour qu’il soit situé au bon endroit, il faut que l’objet soit deux fois plus grand que ce qu’il n’y paraît.

Testez la rotation de l’objet général en utilisant la poignée de rotation.

Récupérez et notez l’ID de la présentation dans son URL :

Créer le Sheets et le script

• Créez un Google Sheets.
• Saisissez un nombre en pourcentage et laissez la cellule active.
• Ouvrez l’éditeur de script. Outils / <> Éditeur de scripts.

Récupérer le score à afficher et vérifier qu’il s’agit bien d’un nombre

const score = SpreadsheetApp.getActiveRange().getValue();
if (!isNaN(score))
{
...
}

le script récupére la valeur de la cellule active.

Ouvrir et modifier la présentation Google Slides

const slidesId = "INSERER L'ID DE VOTRE GOOGLE SLIDES ENTRE LES GUILLEMETS" ;
const slides = SlidesApp.openById(slidesId);
const slideJauge = slides.getSlides()[0];

Ouvrez la présentation avec le service « SlidesApp » (pensez à adapter le script en insérant l’ID de votre présentation Google Slides), et récupérez la première diapositive (l’index 0 de toutes les diapositives).

Récupérer et modifier le groupe d’objets « aiguille »

Attention, pour cet exemple il faut que l’aiguille soit le seul groupe d’objets de toute la diapositive, afin de la repérer facilement parmi ses objets. Chaque secteur est une forme unique, l’aiguille est un groupe de forme.

const aiguille = slideJauge.getGroups()[0];
const rotation = score * 180 ;
aiguille.setRotation(rotation); 

Le script récupère le premier et seul groupe de formes de la diapositive.

La constante « rotation » calcule en degré l’angle de l’aiguille, en multipliant le score par 180 deg (angle plat, révisez votre CM2, ça y est vous savez enfin pourquoi Madame Dérumeau vous cassait les pieds avec ce p***** de rapporteur !).

Fermez la présentation et récupérez le blob

C’est quoi un « blob » ? non Thierry, ce n’est pas un « chlapleau de bleauf qui plotége du sloleil » ! C’est un « Un objet d’échange de données pour les services Apps Script ». Bref, un objet (ici une image) que nous pourrons intégrer dans le Sheets.

Pour récupérer le blob, nous allons faire comme dans l’article exporter une feuille Sheets au format PDF, c’est à dire générer une URL et récupérer son contenu avec le service UrlFetchApp().

slides.saveAndClose();
const url = "https://docs.google.com/presentation/d/" + slidesId + "/export/png";
const token = ScriptApp.getOAuthToken();
const blob = UrlFetchApp.fetch(url, { headers: { 'Authorization': 'Bearer ' + token } });

Tout d’abord, il faut fermer la présentation, pour être sûr que l’URL prendra en compte la modification toute récente de l’aiguille.

Ensuite construire l’URL, en intégrant l’ID de la présentation et en ajoutant « export/png », qui vous l’aurez compris, va exporter le fichier Slides au format PNG. (cliquer ici pour en savoir plus sur les URLs des documents Google) ;

Récupérer une autorisation (Token) de construire le blob.

Enfin, simuler l’accès à l’URL et stocker le contenu dans la constante « blob ».

Insérer l’image dans l’onglet actif

const sheet = SpreadsheetApp.getActiveSheet();
sheet.insertImage(blob, 7 ,1);

Insérer l’image dans la septième colonne et première ligne.

Créer un déclencheur

Les services appelés (Slides et Url) ne sont pas autorisés par un simple onEdit(), il faut créer un déclencheur pour que l’image se génère à chaque modification de la cellule :

Le script dans son intégralité :

Voici toutes les actions dans l’ordre, j’ai ajouté une suppression des images de l’onglet en cours.

/*
*
*
* Script NUMERICOACH - Antoine MARTIN tous droits reservés - 2021
*
*/
function genererJauge() {
  const score = SpreadsheetApp.getActiveRange().getValue();
  const sheet = SpreadsheetApp.getActiveSheet();
  Logger.log(score);
  const slidesId = "REMPLACER PAR L'ID DE VOTRE SLIDE";
  // Si la valeur est un nombre, lancer le script
  if (!isNaN(score)) {
    const images = sheet.getImages();
    images.forEach(function (image) {
      image.remove();
    })
    const slides = SlidesApp.openById(slidesId);
    const slideJauge = slides.getSlides()[0];
    const aiguille = slideJauge.getGroups()[0];
    Logger.log(aiguille.getTitle());
    const rotation = score * 180;
    aiguille.setRotation(rotation);
    slides.saveAndClose();
    const url = "https://docs.google.com/presentation/d/" + slidesId + "/export/png";
    const token = ScriptApp.getOAuthToken();
    const blob = UrlFetchApp.fetch(url, { headers: { 'Authorization': 'Bearer ' + token } });
    sheet.insertImage(blob, 7 ,1);
  }
}

Dans cette démo, la génération de l’image est un peu plus longue que celle du graphique, mais répond à la demande initiale.

En espérant que cet exemple vous permette de résoudre une situation bloquante de création de graphiques sur mesure. L’imagination d’applications n’a plus de limites. Vous pouvez mettre en forme vos données en les interprétant avec Google Slides !

N’hésitez pas à partager vos expériences sur l’utilisation de ce script.