Google Chart - Refresh Data from Google Sheet without Page Refresh

Question

New to coding here and I have a WordPress page that displays a Google Chart with the source data coming from a Google Sheet where I'm trying to have the Google Chart redraw/refresh the data automatically without manually refreshing the entire web page.

Below is my current code which works to only refresh the data in the chart when a user manually refreshes the entire website.

I have read about AJAX and it seems this is the key but I haven't been able to tweek the Google Developer example code to solve my issue.

I hit a road block at ...

function drawChart() {
  var jsonData = $.ajax({
      url: "getData.php",
      dataType: "json",
      async: false
      }).responseText;

where I do not know how to get my public google sheet URL to work in place of the "getData.php" part of the code snippet above. I do not know anything about php but saw examples where people reformatted their google sheet URL is lieu of using the php part to something like:

https://spreadsheets.google.com/feeds/list/1gwLrZtvDBSQPdXl_L1cOl_y2318yiVXKwgN2-czdgW0/1/public/basic?alt=json

but that still doesn't work for me. Feels like I'm going down a rabbit hole just trying to get the data using this approach when I already have a way to get the same data (tho without it automatically refreshing without manually reloading the entire website). Any help will be greatly appreciated!

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_1" style="width: 700px; height: 400px;"></div>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawVisualization1);
function drawVisualization1() {   
 var query = new google.visualization.Query( 'https://docs.google.com/spreadsheets/d/1gwLrZtvDBSQPdXl_L1cOl_y2318yiVXKwgN2-czdgW0/gviz/tq?gid=0&headers=1&tq?&range=A1:E13');
  query.send(handleQueryResponse1);
}
function handleQueryResponse1(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
var options = {
      title : 'Sales ($) and Margin (%)',
      titleTextStyle: {
                      fontSize: 16,
                      },
      legend: {position: 'top', maxLines: 2},
      pointSize: 7,
      series: {
           0: {type: 'bars', targetAxisIndex: 0},
           1: {type: 'bars', targetAxisIndex: 0},
           2: {type: 'bars', targetAxisIndex: 0},
           3: {type: 'line', targetAxisIndex: 1},
      },
      vAxes:  {
              0: {title: 'Sales ($ in millions)'},
              1: {title: 'Margin (%)', gridlines: {color: 'transparent'}, format:"#%"}},
      hAxis: {title: 'Fiscal Quarter', slantedText: 'True'},
      isStacked: 'True',
  };
  var data = response.getDataTable();
  var chart = new google.visualization.ComboChart(document.getElementById('chart_1'));
  chart.draw(data, options);
}
</script>

Answer 1

You can use setRefreshInterval(seconds) to call query.send() every few seconds.

query.setRefreshInterval(120);
query.send(handleQueryResponse1);

Answer 2

you don't need ajax, because you're using google.visualization.Query to get the data.

just need to structure you're code in such a way,
that you can refresh the data and chart,
without rebuilding everything.

see following working snippet,
the chart and options are created once.
the data is retrieved initially, and when the button is clicked.
after receiving the data, the chart is drawn.

I added the 'ready' event to the chart, just to prove the data was refreshed and the chart re-drawn.

but since the data doesn't actually change, nothing appears to happen.

google.charts.load('current', {
  packages: ['corechart']
}).then(drawVisualization1);

function drawVisualization1() {
  var chart = new google.visualization.ComboChart(document.getElementById('chart_1'));
  google.visualization.events.addListener(chart, 'ready', function () {
    console.log('drawn');
  });

  var options = {
    title : 'Sales ($) and Margin (%)',
    titleTextStyle: {
      fontSize: 16,
    },
    legend: {position: 'top', maxLines: 2},
    pointSize: 7,
    series: {
      0: {type: 'bars', targetAxisIndex: 0},
      1: {type: 'bars', targetAxisIndex: 0},
      2: {type: 'bars', targetAxisIndex: 0},
      3: {type: 'line', targetAxisIndex: 1},
    },
    vAxes:  {
      0: {title: 'Sales ($ in millions)'},
      1: {title: 'Margin (%)', gridlines: {color: 'transparent'}, format:"#%"}
    },
    hAxis: {title: 'Fiscal Quarter', slantedText: 'True'},
    isStacked: 'True',
  };

  getData();
  document.getElementById('refresh').addEventListener('click', getData);

  function getData() {
    var query = new google.visualization.Query( 'https://docs.google.com/spreadsheets/d/1gwLrZtvDBSQPdXl_L1cOl_y2318yiVXKwgN2-czdgW0/gviz/tq?gid=0&headers=1&tq?&range=A1:E13');
    query.send(handleQueryResponse1);
  }

  function handleQueryResponse1(response) {
    if (response.isError()) {
      alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      return;
    }
    var data = response.getDataTable();
    chart.draw(data, options);
  }
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<input id="refresh" type="button" value="Refresh" />
<div id="chart_1"></div>