google-apps-scriptchartsanychart

Create multiple charts in order of tab of my SpreadSheet

I try to insert in order of tabs of SpreadSheet named Company 1 multiple charts in Web App. But the order change for each refresh of my page. Anyone know what can I do to show charts in order of my tabs ? And I would like to show in h1 tag the name of the service (form the nam of tabs) if it's possible.

Result expected : enter image description here This is the link of my Web App and the link of datas who went in charts.

Code.gs :

  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const fBDD = classeur.getSheetByName("BDD");
  const fStaff = classeur.getSheetByName("Staff");

  function doGet() {
    return HtmlService.createTemplateFromFile("Index").evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  }

  function include(filename){
    return HtmlService.createHtmlOutputFromFile(filename).getContent();
  }

  function indicateursSFMC(i) {
    let classeurService = SpreadsheetApp.openById("1hqJ9WvlrWonsxAI5t4IvapL29JO1vBKCvDlfhKmSLIg");
    let feuille = classeurService.getSheets()[i];
    const [[ ,...head], ...values] = feuille.getRange(1,1,feuille.getLastRow(),feuille.getLastColumn()).getValues().filter(d=>d[0] != "");
    const obj = [...Map.groupBy(values, ([a]) => a)].reduce((o, [k, v]) => {
      const [, ...data] = v[0].map((_, col) => v.map((row) => row[col] || null));
      const [header, ...rows] = data.map((r, i) => [(i == 0 ? "#" : head[i]), ...r]);
      o[k] = { header, rows };
      return o;
    }, {});
    return obj;
  }

Index.html :

<!DOCTYPE html>
 <html>
   <head>
     <base target="_top">
     <?!= include('JavaScript'); ?>
     <?!= include('CSS'); ?>
   </head>
   <body>
     <p id="serviceUtilisateurMC" style="display:none"></p>
     <p id="nbServiceUtilisateurMC" style="display:none"></p>
     <p id="idClasseurMC" style="display:none"></p>

     <div id="divGraphiquesMC" class="graphics"></div>
   </body>
   <script>
     document.addEventListener("DOMContentLoaded",loadGraphicsMC);
   </script>
 </html>

JavaScript.html :

<script src="https://cdn.anychart.com/releases/8.13.0/js/anychart-core.min.js"></script>
<script src="https://cdn.anychart.com/releases/8.13.0/js/anychart-radar.min.js"></script>

<script>
  function loadView(options){
    var id = typeof options.id === "undefined" ? "app" : options.id;
    var cb = typeof options.callback === "undefined" ? function(){} : options.callback;
  }
  function loadGraphicsMC(){
    loadView({func: "loadSearchView", callback: showGraphicsMC()});
  }
  function showGraphicsMC(){
    var titre = "";
    for (let i = 0; i < 4; i ++){
      let allData;
      google.script.run.withSuccessHandler(values => {
        allData = values;
        const keys = Object.keys(allData);
        if (isOdd(i) === 1){ titre = "Big Skill 2"; }
        else{ titre = "Big Skill 1"; }
        graphiqueMC(allData[keys[0]],"divGraphiquesMC",titre);
      }).indicateursSFMC(i);
    }
  }

  function isOdd(num) { return num % 2;}

  function graphiqueMC(chartData,divContainer,titre) {
    var chart = anychart.radar();
    chart.defaultSeriesType('line');
    chart.data(chartData);
    chart.palette(['#ffcc99', '#ffcccc', '#ff99cc', '#cc99ff', '#ccccff', '#99ccff', '#baf4f4', '#99ffcc', '#ccffcc', '#ccff99']);
    chart.yAxis().stroke('#545f69');
    chart.yAxis().ticks().stroke('#545f69');
    chart.yGrid().palette(['gray 0.05', 'gray 0.025']);
    chart.yScale().ticks().interval(1);
    chart.yScale().minimum(0);
    chart.yScale().maximum(5);
    chart.xGrid().stroke({
      color: "#545f69",
      thickness: 0.5,
      dash: "10 5"
    });
    chart.interactivity().hoverMode('by-x');
    var background = chart.background();
    background.stroke("3 #0d5973");
    background.cornerType("round");
    background.corners(20);
    chart.markerPalette(['round']);
    chart.tooltip()
      .displayMode('union')
      .useHtml(true)
      .format(function (e) {
        return '<span style="color:' + this.series.color() + '">' +
          this.seriesName + ": " + this.value + "</span>"
      });
    chart.legend()
      .align('center')
      .position('center-bottom')
      .enabled(true);
    chart.title(titre);
    chart.container(divContainer);
    chart.draw();
  }
</script>

Thank you Tanaike for you previous help to insert chart in my html page.

Solution

  • About your current issue But the order change for each refresh of my page., when I saw your showing script, I guessed that the reason for your current issue might be due to that google.script.run is run with the asynchronous process. So, as a simple modification for your showing script, how about the following modification?

    In this modification, your function showGraphicsMC on the Javascript side is modified.

    From:

    function showGraphicsMC(){
  var titre = "";
  for (let i = 0; i < 4; i ++){
    let allData;
    google.script.run.withSuccessHandler(values => {
      allData = values;
      const keys = Object.keys(allData);
      if (isOdd(i) === 1){ titre = "Big Skill 2"; }
      else{ titre = "Big Skill 1"; }
      graphiqueMC(allData[keys[0]],"divGraphiquesMC",titre);
    }).indicateursSFMC(i);
  }
}

    To:

    // I added this function.
/**
 * syncGoogleScriptRun for Javascript library
 * GitHub  https://github.com/tanaikech/syncGoogleScriptRun
 *
 * Run google.script.run with the synchronous process.
 * @param {Object} resource the object for using syncGoogleScriptRun.
 * @return {Object} Returned value from the function of Google Apps Script side.
 */
const syncGoogleScriptRun = resource => new Promise((resolve, reject) =>
  google.script.run.withFailureHandler(e => reject(e)).withSuccessHandler(e => resolve(e))[resource.gasFunction](resource.arguments)
)

// Also, I modified this function.
async function showGraphicsMC() {
  var titre = "";
  let allData;
  for (let i = 0; i < 4; i++) {
    const resource = { gasFunction: "indicateursSFMC", arguments: i };
    allData = await syncGoogleScriptRun(resource).catch(e => { throw new Error(e) });
    const keys = Object.keys(allData);
    if (isOdd(i) === 1) {
      titre = "Big Skill 2";
    } else {
      titre = "Big Skill 1";
    }
    graphiqueMC(allData[keys[0]], "divGraphiquesMC", titre);
  }
}

    Added:

    As another approach, when all values are retrieved from all sheets in the source Spreadsheet, how about the following modification? In this modification, Google Apps Script and Javascript are modified.

    Google Apps Script: Code.gs

    Please add the following function to Code.gs.

    function getValues() {
  const ss = SpreadsheetApp.openById("1hqJ9WvlrWonsxAI5t4IvapL29JO1vBKCvDlfhKmSLIg");
  const sheets = ss.getSheets();
  const ar = sheets.map(feuille => {
    const [[, ...head], ...values] = feuille.getRange(1, 1, feuille.getLastRow(), feuille.getLastColumn()).getValues().filter(d => d[0] != "");
    const obj = [...Map.groupBy(values, ([a]) => a)].reduce((o, [k, v]) => {
      const [, ...data] = v[0].map((_, col) => v.map((row) => row[col] || null));
      const [header, ...rows] = data.map((r, i) => [(i == 0 ? "#" : head[i]), ...r]);
      o[k] = { header, rows };
      return o;
    }, {});
    return obj;
  });
  return ar;
}

    Javascript: JavaScript.html.

    Please modify the function showGraphicsMC of JavaScript.html as follows.

    function showGraphicsMC() {
  google.script.run.withSuccessHandler(ar => {
    ar.forEach((allData, i) => {
      const keys = Object.keys(allData);
      const titre = isOdd(i) === 1 ? "Big Skill 2" : "Big Skill 1";
      graphiqueMC(allData[keys[0]], "divGraphiquesMC", titre);
    });
  })
  .getValues();
}