Google App Script : Sommaire interactif

Le 26/04/2022 à 09:29

Bonjour à tous,

J'ai actuellement deux problèmes non résolus sur un sommaire et une Web app, si l'un d'entre vous à la solution je serais preneur !

Voici mon premier problème :

J'ai mis en place un sommaire interactif, trouvé sur internet.

Le formulaire est fonctionnel et me permet de mettre à jour la feuille à chaque modification en indiquant le nom des feuilles présentes dans le Sheets ainsi que le lien de ces dernières.

Actuellement, j'ai donc plusieurs colonne à savoir : Nom, URL, Masquée ?, Couleur, Ordre, Lien. Je n'ai rien modifié au script de base pour le moment.

Je souhaiterais ajouter des informations supplémentaires pour chaque feuilles en venant chercher des valeurs indiquées dans les feuilles concernées :

par exemple, dans le SOMMAIRE j'ai la feuille "Feuille 1", avec son URL, son ordre, son lien etc...

Je souhaiterais qu'en colonne G de la feuille "SOMMAIRE", la cellule A2 de la "Feuille 1" soit retournée.

Avez vous un moyen de permettre cela ?

Voici le code du sommaire interactif (en laissant évidemment les crédits de l'auteur original :) )

/***
 * 
 * Numericoach 2022 - tous droits reservés.
 * Auteur : Antoine Martin 
 * 
 * ni Numericoach ni l'auteur de ce script ne pourront être tenu responsable des conséquences d'une utilisation de ce script.
 * 
 * 
 */ 

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Sommaire interactif")
    .addItem("Mettre à jour le sommaire", "resumeSheets")
    .addItem("Mettre les feuilles dans l'ordre", "sortSheets")
    .addToUi();
}

function resumeSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let resumeValues = [];
  let resumeColors = [];
  const ssUrl = ss.getUrl();
  sheets.forEach(function (sheet, nSheet) {
    resumeValues.push([sheet.getName(), ssUrl + "#gid=" + sheet.getSheetId(), sheet.isSheetHidden(), sheet.getTabColor(), (nSheet + 1)]); 
    resumeColors.push(sheet.getTabColor());
  })

  let sheetSommaire = ss.getSheetByName("Sommaire");
  if (sheetSommaire == null) {
    sheetSommaire = ss.insertSheet("Sommaire", 0);
    sheetSommaire.appendRow(["Nom","URL","Onboarding terminé ?","Couleur","Ordre"]);
    sheetSommaire.getRange(1,6).setFormula('=ArrayFormula(IF(A:A<>"";IF(A:A=A1;"LIEN";HYPERLINK(B:B;A:A));))');
    sheetSommaire.setFrozenRows(1);
    sheetSommaire.deleteColumns(7,sheetSommaire.getMaxColumns()-7);
  }
  sheetSommaire
    .getRange(2, 1, sheetSommaire.getMaxRows() - 1, sheetSommaire.getMaxColumns()).clearContent();
  sheetSommaire
    .getRange(2, 1, resumeValues.length, resumeValues[0].length)
    .setValues(resumeValues);
  sheetSommaire.getRange(2, 3, resumeValues.length, 1).insertCheckboxes();
  resumeColors.forEach(function (color, ncolor) {
    sheetSommaire.getRange(ncolor + 2, 4).setBackground(color);
  })

}

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);
    }

   else if (rangeCol == 3) {

      // MASQUER / AFFICHER la feuille
      const sheetName = sheetSommaire.getRange(rangeRow, 1).getValue();

      const sheetCible = ss.getSheetByName(sheetName);
      if (rangeValue) {
        sheetCible.hideSheet();
        ss.toast("La feuille " + sheetName + " a été masquée.");
      }
      else {
        sheetCible.showSheet();
        ss.toast("La feuille " + sheetName + " a été affichée.");
      }
    }
   else if (rangeCol == 4) {
      const sheetName = sheetSommaire.getRange(rangeRow, 1).getValue();
      const sheetCible = ss.getSheetByName(sheetName);
      // changer la couleur de la feuille
      Logger.log("value ='"+rangeValue+"'");
      if (rangeValue == "") {
        ss.toast("Couleur de " + sheetName + " modifiée.")
        sheetCible.setTabColor(null);
        range.setBackground(null);
      }
      if (!rangeValue.match(/#[0-9a-fA-F]{6}/)) return;

      sheetCible.setTabColor(rangeValue);
      range.setBackground(rangeValue);
      ss.toast("Couleur de " + sheetName + " modifiée.")
    }
  }
}

function sortSheets() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetSommaire = ss.getSheetByName("Sommaire");
  sheetSommaire.sort(5);
  sheetSommaire.getDataRange().getValues().forEach(function (row, n) {

    Logger.log(row[0])
    const sheet = ss.getSheetByName(row[0]);
    if (sheet != null) {
      sheet.activate();
      ss.moveActiveSheet(n);
    }
  })
  sheetSommaire.activate();
  resumeSheets();

}

Si vous avez une réponse à cette première problématique ce serait déjà super !

Deuxième problème :

J'ai mis en place une Web APP avec une Data base, qui permet, entre autres, de suivre en temps réel l'évolution du sommaire.

Dans cette Web APP, les données texte d'un sheets en particulier sont retournées. Jusque là, tout est parfait.

Je souhaiterais néanmoins rajouter un lien cliquable dans cette table : Le lien est indiqué dans une case Google Sheets, j'aimerais, dans la mesure du possible rendre le lien cliquable directement depuis la Web App, sans avoir à copier/coller le lien.

Voici le code JS de la Table :

<script>

  var data = [
            [1,"Jack",55],
            [2,"Ana",76],
            [3,"Linda",44]

  ];

  document.addEventListener("DOMContentLoaded",function(){

    google.script.run.withSuccessHandler(generateTable).getTableData();

  });

  function generateTable(dataArray){

     var tbody = document.getElementById("table-body");

     dataArray.forEach(function(r){

      var row = document.createElement("tr");
      var col1 = document.createElement("td");
      col1.textContent = r[0];
      var col2 = document.createElement("td");
      col2.textContent = r[1];
      var col3 = document.createElement("td");
      col3.textContent = r[2];
      row.appendChild(col1);
      row.appendChild(col2);
      row.appendChild(col3);
      tbody.appendChild(row);

    });

  }

Et voici la fonction associée :

function getTableData(){

  var url = "URL SHEETS"

  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Table");
  var data = ws.getRange(2, 1, ws.getLastRow()-1,3).getValues();
  return data;

Et voici à quoi ressemble la table :

capture d e cran 2022 04 26 a 09 25 37

Merci à tous d'avoir pris le temps de m'avoir lu

Bonne journée à tous

Adrien

Le 26/04/2022 à 10:17

Bonjour,

as-tu demandé à l'auteur ?

voici une proposition

function resumeSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let resumeValues = [];
  let resumeColors = [];
  const ssUrl = ss.getUrl();
  sheets.forEach(function (sheet, nSheet) {
    resumeValues.push([sheet.getName(), ssUrl + "#gid=" + sheet.getSheetId(), sheet.isSheetHidden(), sheet.getTabColor(), (nSheet + 1), '', sheet.getRange('A2').getValue()]);
    resumeColors.push(sheet.getTabColor());
  })

  let sheetSommaire = ss.getSheetByName("Sommaire");
  if (sheetSommaire == null) {
    sheetSommaire = ss.insertSheet("Sommaire", 0);
    sheetSommaire.appendRow(["Nom", "URL", "Onboarding terminé ?", "Couleur", "Ordre", , "Donnée"]);
    sheetSommaire.getRange(1, 6).setFormula('=ArrayFormula(IF(A:A<>"";IF(A:A=A1;"LIEN";HYPERLINK(B:B;A:A));))');
    sheetSommaire.setFrozenRows(1);
    sheetSommaire.deleteColumns(7, sheetSommaire.getMaxColumns() - 7);
  }
  sheetSommaire
    .getRange(2, 1, sheetSommaire.getMaxRows() - 1, sheetSommaire.getMaxColumns()).clearContent();
  sheetSommaire
    .getRange(2, 1, resumeValues.length, resumeValues[0].length)
    .setValues(resumeValues);
  sheetSommaire.getRange(2, 3, resumeValues.length, 1).insertCheckboxes();
  resumeColors.forEach(function (color, ncolor) {
    sheetSommaire.getRange(ncolor + 2, 4).setBackground(color);
  })

}
Le 26/04/2022 à 10:34

Bonjour Steelson,

Oui, j'ai demandé à l'auteur malheureusement je n'ai pas eu de réponse de son côté.

Bien vu pour le code, je n'avais pas du tout pensé à le formuler ainsi, merci beaucoup cela fonctionne !

Mon premier problème est maintenant résolu ! Encore merci

Adrien

Le 26/04/2022 à 11:30

Pour le second sujet, que vient faire

  var data = [
            [1,"Jack",55],
            [2,"Ana",76],
            [3,"Linda",44]

  ];

??

et qu'est-ce qu'il y a dans r[1] ici

col2.textContent = r[1];

??

Le 26/04/2022 à 12:00

@Steelson

Effectivement, la première fonction

var data = [
            [1,"Jack",55],
            [2,"Ana",76],
            [3,"Linda",44]

  ];

N'est pas à prendre en compte, c'était une première formule test que j'ai supprimé ! Sorry

pour les r1,2,3, c'est parce que le code a été réalisé en partie avec materializecss

Le code HTML devrait peut être t'aider à y voir plus clair ?

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta name="viewport" content="width=device-width, initial-scale=1.0"/>

    <?!= include("table-css"); ?>

  </head>
  <body>

    <div class="container">

      <h1> Data base </h1>

    <div class="row">
        <div class="col s12">
          <table>
        <thead>
          <tr>
              <th>Name</th>
              <th>Item Name</th>
              <th>Item Price</th>
          </tr>
        </thead>

        <tbody id="table-body">

        </tbody>
      </table>

        </div>
      </div> <!--CLOSE ROW -->

        </div> <!--CLOSE CONTAINER -->

    <script src="//ADRESSE URL"></script>
    <?!= include("table-js"); ?>

  </body>
</html>
Le 26/04/2022 à 12:50

si ta source contient ce lien en colonne D

col4.textContent = `<td><a href="${r[3]}">cliquer ici</a></td>`

pas testé car je n'ai pas de fichier pour le faire

Le 26/04/2022 à 21:53

Hello Merci pour la proposition de script :)

Malheureusement, le code entier s'affiche en texte dans le rendu de la table

Adrien

Le 27/04/2022 à 01:54

même comme ceci ?

      var col4 = document.createElement("td");
      col4.textContent = `<a href="${r[3]}">cliquer ici</a>`;

il faut que dans la colonne D j'ai un lien en http...

si cela ne fonctionne pas, peux-tu poster un bout de fichier pour essais ?

Rechercher des sujets similaires à "google app script sommaire interactif"