英文:
Google Visualization.Sankey -- GoogleScript Dropping Node Label
问题
以下是您要翻译的内容：
"So I am able to make a Sankey plot from my Google Sheet data using Google Chart. The issue is that the plot seems to drop off the label for one of the nodes.
My data looks as follow:
Source Type Count
External A 12
External B 7
External C 1
External D 0
Internal A 26
Internal B 23
Internal C 15
Internal D 0
Other A 0
Other B 1
Other C 24
Other D 0
The plot looks like
As you can see the last Node name "C" (in green is dropped)
My GoogleScript code was as follow:
code.gs
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.createMenu('CustomScripts')
.addItem('SankeyDiagram', 'openDialog')
.addToUi();
}
function getSpreadsheetData() {
// ID of your Document, take from URL
var ssID = "sheetID",
sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
data = sheet.getDataRange().getValues();
return data;
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index')
.setHeight(300)
.setWidth(1000);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.showModalDialog(html, 'Sankey Diagram');
}
And the corresponding HTML was:
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://www.google.com/jsapi"></script>
</head>
<body>
<div id="main"></div>
<script type="text/javascript">
google.load('visualization', '1', {
packages: ['corechart', 'sankey']
}); google.setOnLoadCallback(initialize);
function initialize() {
google.script.run.withSuccessHandler(drawChart).getSpreadsheetData();
}
function drawChart(rows) {
console.log(rows);
var data = google.visualization.arrayToDataTable(rows);
var chart = new google.visualization.Sankey(document.getElementById('sankey_basic'));
chart.draw(data, {'title':'Detection Method by Severity',width: 900,height: 250, sankey: {}});
google.script.run.withSuccessHandler().newChart(chart);
}
</script>
</body>
</head>
<body>
<div id="sankey_basic" style="width: 900px; height: 300px;"></div>
</body>
</html>
What am I overlooking that is causing the "C" category not to appear?
Separate but not critical question -- is there a way to embed the image as an image/chart on the active sheet
Also for the code reference... special thanks to
njoerd114 for creating the framework for drawing Sankey plots... https://gist.github.com/njoerd114/839b9a5298843ea4cf9fd241e39ebbf6"
英文:
So I am able to make a Sankey plot from my Google Sheet data using Google Chart. The issue is that the plot seems to drop off the label for one of the nodes.
My data looks as follow:
Source Type Count
External A 12
External B 7
External C 1
External D 0
Internal A 26
Internal B 23
Internal C 15
Internal D 0
Other A 0
Other B 1
Other C 24
Other D 0
The plot looks like
As you can see the last Node name "C" (in green is dropped)
My GoogleScript code was as follow:
code.gs
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.createMenu('CustomScripts')
.addItem('SankeyDiagram', 'openDialog')
.addToUi();
}
function getSpreadsheetData() {
// ID of your Document, take from URL
var ssID = "sheetID",
sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
data = sheet.getDataRange().getValues();
return data;
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index')
.setHeight(300)
.setWidth(1000);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.showModalDialog(html, 'Sankey Diagram');
}
And the corresponding HTML was:
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://www.google.com/jsapi"></script>
</head>
<body>
<div id="main"></div>
<script type="text/javascript">
google.load('visualization', '1', {
packages: ['corechart', 'sankey']
}); google.setOnLoadCallback(initialize);
function initialize() {
google.script.run.withSuccessHandler(drawChart).getSpreadsheetData();
}
function drawChart(rows) {
console.log(rows);
var data = google.visualization.arrayToDataTable(rows);
var chart = new google.visualization.Sankey(document.getElementById('sankey_basic'));
chart.draw(data, {'title':'Detection Method by Severity',width: 900,height: 250, sankey: {}});
google.script.run.withSuccessHandler().newChart(chart);
}
</script>
</body>
</head>
<body>
<div id="sankey_basic" style="width: 900px; height: 300px;"></div>
</body>
</html>
What am I overlooking that is causing the "C" category not to appear?
Separate but not critical question -- is there a way to embed the image as an image/chart on the active sheet
Also for the code reference... special thanks to
njoerd114 for creating the framework for drawing Sankey plots... https://gist.github.com/njoerd114/839b9a5298843ea4cf9fd241e39ebbf6
答案1
得分: 1
您之所以获取桑基图的切割图像是因为在“D”中存在“零”值。我在GitHub上找到了一个类似的问题：桑基图中的链接值为零时节点位置不起作用
我建议您首先通过过滤数组来删除“零”值：
修改后的代码:
function onOpen() {
SpreadsheetApp.getUi() // 或者 DocumentApp 或 FormApp.
.createMenu('CustomScripts')
.addItem('SankeyDiagram', 'openDialog')
.addToUi();
}
function getSpreadsheetData() {
// 您的文档ID，从URL中获取
var ssID = "1KtvAFFQV13zSwktXKtUXZLQF9hmb9VaxbC4OM3hGEVw",
// 选择哪个工作表？[0] 是第一个工作表，依此类推...
sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
data = sheet.getDataRange().getValues(),
// 通过移除零值来筛选数据
header = sheet.getRange(1, 1, 1, 3).getValues()[0],
filteredData = data.filter(r => r[2] > 0);
filteredData.unshift(header);
return filteredData;
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index')
.setHeight(300)
.setWidth(1000);
SpreadsheetApp.getUi() // 或者 DocumentApp 或 FormApp.
.showModalDialog(html, 'Sankey Diagram');
}
结果:
英文:
Suggestion:
The reason why you are getting the cut image of the sankey graph is because of the zero values you have in "D".
I have found a similar concern like this in git hub: Node positions do not work with link value zero in Sankey
I suggest that you remove zeros first by filtering the array:
Modified code:
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.createMenu('CustomScripts')
.addItem('SankeyDiagram', 'openDialog')
.addToUi();
}
function getSpreadsheetData() {
// ID of your Document, take from URL
var ssID = "1KtvAFFQV13zSwktXKtUXZLQF9hmb9VaxbC4OM3hGEVw",
// which Sheet? [0] is the first and so on...
sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
data = sheet.getDataRange().getValues(),
///Filtered the data by removing zeros
header = sheet.getRange(1,1,1,3).getValues()[0],
filteredData = data.filter(r => r[2] > 0);
filteredData.unshift(header)
return filteredData
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index')
.setHeight(300)
.setWidth(1000);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.showModalDialog(html, 'Sankey Diagram');
}
Result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论