Dynamic chart in Google Sheets sidebar-CodePudding

I would like to create a column chart that can be displayed and dynamically updated in the Google Sheets sidebar (without reloading the sidebar) from data in a spreadsheet. I understand how to call the sidebar and load a chart using Google Charts and JavaScript, but I running into the following issues:

How do I pull the data from the sheet to populate the DataTable needed for the chart? Could I do this with scriptlets or is there a better way?
How do I update the columns in the chart automatically to reflect when data is changed without the use of a button? I think that this can be done with a timeout function that calls the data from the sheet at regular intervals, but I am not sure how to update the columns.

The solution would preferably only use pure JS and Apps Script. Here is my current code:

<!DOCTYPE html>
<html>
<head>
<base target="_top">
  <script src="https://www.gstatic.com/charts/loader.js"></script>
</head>

<body>
  <div id="genderChart" style="margin: 5px 5px 5px 5px"></div>

<script>
  google.charts.load('current', {'packages':['corechart']});
  google.charts.setOnLoadCallback(drawChart);

  var genderMale = Math.random() * 100;
  var genderFemale = Math.random() * 100;
  var genderNB = Math.random() * 100;

  function drawChart() {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Gender');
    data.addColumn('number', 'Students');
    data.addColumn({role: 'style'});

    data.addRows([
      ['Male', genderMale, '#9fc5e8'],
      ['Female', genderFemale, '#d5a6bd'],
      ['Non-binary', genderNB, '#b7b7b7']
    ]);

    var options = {
      legend: {position: "none"},
      animation: {
        duration: 1000,
        easing: 'in'
      }
    };

    var chart = new google.visualization.ColumnChart(document.getElementById('genderChart'));
    chart.draw(data, options);

    updateData();

    function updateData() {
      var genderMale = Math.random() * 100;
      var genderFemale = Math.random() * 100;
      var genderNB = Math.random() * 100;
      chart.draw(data, options);
      setTimeout(updateData, 100);
    }
  }

</script>
</body>
</html>

CodePudding user response：

As mentioned in the comments, you can use google.script.run to communicate with the Apps Script server-side code, which will get the values from the spreadsheet. In your case since you're requesting data from the server you'll have to use

Sources: