如何使用 Google Charts 使用 Google Sheet 中的数据绘制甘特图? [英] How can I use Google Charts to draw a Gantt chart using data from a Google Sheet?

问题描述

所以我有点了解如何使用 Google Charts Google Chart 通过手动输入数据，如示例所示，但我无法弄清楚如何从 Google 表格中提取数据.最令人困惑的部分是我可以和不能从示例代码中删除的部分，例如:

 data.addRows([['研究', '寻找来源', null,new Date(2015, 0, 1), new Date(2015, 0, 5), null, 100, null],['写'，'写论文'，'写'，null, new Date(2015, 0, 9), daysToMilliseconds(3), 25, 'Research,Outline'],['引用', '创建参考书目', '写',null, new Date(2015, 0, 7), daysToMilliseconds(1), 20, 'Research'],['完成'，'提交论文'，'完成'，null, new Date(2015, 0, 10), daysToMilliseconds(1), 0, 'Cite,Write'],['大纲'，'大纲纸'，'写'，null, new Date(2015, 0, 6), daysToMilliseconds(1), 100, 'Research']]);

和

 var data = new google.visualization.DataTable();data.addColumn('string', '任务 ID');data.addColumn('string', '任务名称');data.addColumn('string', '资源');data.addColumn('日期', '开始日期');data.addColumn('日期', '结束日期');data.addColumn('数字', '持续时间');data.addColumn('number', '完成百分比');data.addColumn('string', 'Dependencies');

以及如何正确地对我的工作表进行查询，然后绘制图表.我想，这基本上意味着我不知道蹲下并且我的眼睛在流血，试图弄明白这个......

这是我的数据:Google 表格>

如您所见，我显然是个 JS 文盲.任何帮助非常感谢.下面是我糟糕的 c&p 弗兰肯代码:

google.charts.load('current', {'packages':['gantt']});google.charts.setOnLoadCallback(drawChart);函数 daysToMilliseconds(days) {返回天数 * 24 * 60 * 60 * 1000；}函数 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');查询.发送(功能(响应){如果(响应.isError()){alert('查询出错:' + response.getMessage() + ' ' + response.getDetailedMessage());返回;};var data = new google.visualization.DataTable();data.addColumn('string', '任务 ID');data.addColumn('string', '任务名称');data.addColumn('string', '资源');data.addColumn('日期', '开始日期');data.addColumn('日期', '结束日期');data.addColumn('数字', '持续时间');data.addColumn('number', '完成百分比');data.addColumn('string', 'Dependencies');var data = response.getDataTable();变量选项 = {高度:275};var chart = new google.visualization.Gantt(document.getElementById('chart_div'));图表绘制(数据，选项)；}

解决方案

如果电子表格数据与 图表的数据格式，
不需要数据操作.
这似乎是这里的情况......

<小时>

请参阅以下工作片段，
'Outline' 对 'Write' 的依赖似乎有点偏离了路径......

google.charts.load('current', {回调:drawChart，包:['甘特图']});函数 drawChart() {var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1M3wQgKg3JBF6_hzv1xWONP7HWVYoOvJ1jPbB27IUg94/gviz/tq?gid=0&headers=1');查询.发送(功能(响应){如果(响应.isError()){console.log('查询错误:' + response.getMessage() + ' ' + response.getDetailedMessage());返回;};变量选项 = {高度: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>

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);
}

解决方案

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>

这篇关于如何使用 Google Charts 使用 Google Sheet 中的数据绘制甘特图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！