Dynamically integrate data from Sheets into a Web App to feed a Graphic

I'm setting up a web app with graphics. I'm using the anyChart library to achieve this.

I'm having trouble adding data from a Sheets file. When the data is hard-coded in JavaScript, it works fine. However, the data will vary and I'd like the JavaScript to retrieve all the data from my Sheets when the page is loaded.

test.html :

<!DOCTYPE html>
 <html>
   <head>
     <base target="_top">
     <script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-core.min.js"></script>
     <script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-radar.min.js"></script>
     <style>
       html, body, #container {
         width: 100%;
         height: 100%;
         margin: 0;
         padding: 0;
       }
     </style>
     <script>
       anychart.onDocumentReady(function () {
         // create a data set
         var chartData = {
           header: ['#', 'John EDWARD', 'Emma SMITH', 'Tony MARTIN', 'Elijah LUTHER'],
           rows: [
             ['Skill 1', 1, 2, 5, 4],
             ['Skill 2', 4, 5, 2, 1],
             ['Skill 3', 3, 3, 5, 1],
             ['Skill 4', 2, 1, 3, 5],
             ['Skill 5', 4, 2, 5, 5],
             ['Skill 6', 3, 5, 4, 5],
             ['Skill 7', 2, 2, 5, 3],
             ['Skill 8', 1, 4, 3, 5]
           ]
         };
         // create a radar chart
         var chart = anychart.radar();
         // set the series type
         chart.defaultSeriesType('Spline Area');
         // set the chart data
         chart.data(chartData);
         // set the color palette
         chart.palette(['#E5593499', '#9BC53DE6', '#64B5F6BF','#8d64f6']);
         // configure the appearance of the y-axis
         chart.yAxis().stroke('#545f69');
         chart.yAxis().ticks().stroke('#545f69');
         // configure the stroke of the x-grid
         chart.xGrid().stroke({
           color: "#545f69",
           thickness: 0.5,
           dash: "10 5"
         });
         // configure the appearance of the y-grid
         chart.yGrid().palette(['gray 0.05', 'gray 0.025']);
         // begin the y-scale at 0
         chart.yScale().minimum(0);
         chart.yScale().maximum(5);
         // set the y-scale ticks interval
         chart.yScale().ticks().interval(1);
         // set the hover mode
         chart.interactivity().hoverMode('by-x');
         // set the marker type
         chart.markerPalette(['round']);
         // improve the tooltip
         chart.tooltip()
           .displayMode('union')
           .useHtml(true)
           .format(function(e){
             console.log(this);
             return '<span style="color:' + this.series.color() + '">' + 
               this.seriesName + ": " + this.value + "</span>"
           });
         // set chart legend settings
         chart.legend()
           .align('center')
           .position('center-bottom')
           .enabled(true);
         // set the chart title
         chart.title("Title");
         // set container id for the chart
         chart.container('container');
         // initiate chart drawing
         chart.draw();
     });
   </script>
 </head>
 <body>
   <div id="container"></div>
 </body>

code.gs :

function doGet() {
   var htmlOutput = HtmlService.createTemplateFromFile('Test');
   return htmlOutput.evaluate();  
 }

And this is my file.

I'd like to automatically integrate data from the “Sheet1” tab.

In addition, as there are several companies, I'd like a drop-down menu to update the data according to the choice made bearing in mind that skills may vary from company to company.

Thank you in advance for your help.

Solution

Although I'm not sure whether I could correctly understand your expected result, how about the following modification?

Modified script:

Google Apps Script: `code.gs`

function doGet() {
  var htmlOutput = HtmlService.createTemplateFromFile('Test');
  return htmlOutput.evaluate();
}

// I added this function.
function getValues() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const [[, ...head], ...values] = sheet.getDataRange().getValues();
  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;
}

HTML: `Test.html`

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-core.min.js"></script>
    <script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-radar.min.js"></script>
    <style>
      html, body, #container {
        width: 100%;
        height: 100%;
        margin: 0;
        padding: 0;
      }
    </style>
    <script>
    let allData;

    google.script.run.withSuccessHandler(values => {
      allData = values;
      const keys = Object.keys(allData);
      const dropdown = document.getElementById("dropdown");
      keys.forEach(k => {
        const o = document.createElement('option');
        o.value = k;
        o.text = k;
        dropdown.appendChild(o);
      });
      drawChart(allData[keys[0]]);
    })
      .getValues();

    function selected(k) {
      document.getElementById("container").innerHTML = "";
      drawChart(allData[k]);
    }

    function drawChart(chartData) {
      // create a radar chart
      var chart = anychart.radar();

      // set the series type
      chart.defaultSeriesType('Spline Area');

      // set the chart data
      chart.data(chartData);

      // set the color palette
      chart.palette(['#E5593499', '#9BC53DE6', '#64B5F6BF', '#8d64f6']);

      // configure the appearance of the y-axis
      chart.yAxis().stroke('#545f69');
      chart.yAxis().ticks().stroke('#545f69');

      // configure the stroke of the x-grid
      chart.xGrid().stroke({
        color: "#545f69",
        thickness: 0.5,
        dash: "10 5"
      });

      // configure the appearance of the y-grid
      chart.yGrid().palette(['gray 0.05', 'gray 0.025']);

      // begin the y-scale at 0
      chart.yScale().minimum(0);
      chart.yScale().maximum(5);

      // set the y-scale ticks interval
      chart.yScale().ticks().interval(1);

      // set the hover mode
      chart.interactivity().hoverMode('by-x');

      //  set the marker type
      chart.markerPalette(['round']);

      // improve the tooltip
      chart.tooltip()
        .displayMode('union')
        .useHtml(true)
        .format(function (e) {
          console.log(this);
          return '<span style="color:' + this.series.color() + '">' +
            this.seriesName + ": " + this.value + "</span>"
        });


      // set chart legend settings
      chart.legend()
        .align('center')
        .position('center-bottom')
        .enabled(true);

      // set the chart title
      chart.title("Title");

      // set container id for the chart
      chart.container('container');

      // initiate chart drawing
      chart.draw();
    }
    </script>
  </head>
  <body>
    <select id="dropdown" onchange="selected(this.value)"></select>
    <div id="container"></div>
  </body>
</html>

Testing:

When your sample Spreadsheet is used, the following result is obtained.

From:

To:

Note:

In your showing script, it seems that Web Apps is used. When you modify the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the details of this in my report "Redeploying Web Apps without Changing URL of Web Apps (Author: me)".