How can I use Google Charts to draw a Gantt chart

问题:

So I kind of understand how to use the Google Charts Google Chart by manually entering data as shown in the example, but I just can't figure out how to ingest data from Google Sheets. The most confusing parts are what I can and can't remove from the example's code, like:

  data.addRows([
    ['Research', 'Find sources', null,
     new Date(2015, 0, 1), new Date(2015, 0, 5), null,  100,  null],
    ['Write', 'Write paper', 'write',
     null, new Date(2015, 0, 9), daysToMilliseconds(3), 25, 'Research,Outline'],
    ['Cite', 'Create bibliography', 'write',
     null, new Date(2015, 0, 7), daysToMilliseconds(1), 20, 'Research'],
    ['Complete', 'Hand in paper', 'complete',
     null, new Date(2015, 0, 10), daysToMilliseconds(1), 0, 'Cite,Write'],
    ['Outline', 'Outline paper', 'write',
     null, new Date(2015, 0, 6), daysToMilliseconds(1), 100, 'Research']
  ]);

and

  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Task ID');
  data.addColumn('string', 'Task Name');
  data.addColumn('string', 'Resource');
  data.addColumn('date', 'Start Date');
  data.addColumn('date', 'End Date');
  data.addColumn('number', 'Duration');
  data.addColumn('number', 'Percent Complete');
  data.addColumn('string', 'Dependencies');

As well how I can properly make the query to my sheet and then draw the chart. Which, I guess, basically means I don't know squat and am bleeding from my eyes trying to make sense of this...

Here is my data: Google Sheets

As you can see, I'm clearly a JS illiterate. Any help is greatly appreciated. Below is my awful c&p frankencode:

google.charts.load('current', {'packages':['gantt']});
google.charts.setOnLoadCallback(drawChart);

function daysToMilliseconds(days) {
  return days * 24 * 60 * 60 * 1000;
}

function drawChart() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1M3wQgKg3JBF6_hzv1xWONP7HWVYoOvJ1jPbB27IUg94/gviz/tq?gid=0&headers=1');

  query.setQuery('SELECT A, B, C, D, E, F, G, H');
  query.send(function (response) {
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
    };

  var data = new google.visualization.DataTable();
   data.addColumn('string', 'Task ID');
   data.addColumn('string', 'Task Name');
   data.addColumn('string', 'Resource');
   data.addColumn('date', 'Start Date');
   data.addColumn('date', 'End Date');
   data.addColumn('number', 'Duration');
   data.addColumn('number', 'Percent Complete');
   data.addColumn('string', 'Dependencies');

  var data = response.getDataTable();

  var options = {
    height: 275
  };

  var chart = new google.visualization.Gantt(document.getElementById('chart_div'));

  chart.draw(data, options);
}

回答1:

if the spreadsheet data matches the data format for the chart,
no data manipulation is required.
which appears to be the case here...

---

see following working snippet,
the 'Outline' dependency on 'Write' seems to throw off the path a little...

google.charts.load('current', {
  callback: drawChart,
  packages: ['gantt']
});

function drawChart() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1M3wQgKg3JBF6_hzv1xWONP7HWVYoOvJ1jPbB27IUg94/gviz/tq?gid=0&headers=1');
  query.send(function (response) {
    if (response.isError()) {
      console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      return;
    };

    var options = {
      height: 275
    };

    var chart = new google.visualization.Gantt(document.getElementById('chart_div'));
    chart.draw(response.getDataTable(), options);
  });
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>