njoerd114/README.MD

## README.MD

      
    Raw
  

              README.MD
            
          
Sankey Diagrams within Google Spreadsheets


This Gist is there to help you creating a Sankey Diagram from your Google Spreadsheets.

Installation


Open a spreadsheet
Click "Tools" -> "Scripts"
create the two files code.gs and index.html
insert the document ID from the URL on line 10, code.gs
select which sheet is the base for the sankey diagram
the layout of the table should be as shown in the Example Spreadsheet

Usage


Open the same document
Click "Zusatzfunktionen" -> "SankeyDiagram"

Example Spreadsheet

https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/

  
## code.gs
function onOpen() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
        .createMenu('Zusatzfunktionen')
        .addItem('SankeyDiagram', 'openDialog')
        .addToUi();
}

function getSpreadsheetData() {
    // ID of your Document, take from URL
    var ssID = "",
        // which Sheet? [0] is the first and so on...
        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');
}

## 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('main'));
             chart.draw(data, {width: 900, sankey: {iterations: 64}});
           }
      </script>
   </body>
</html>
	function onOpen() {
	SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
	.createMenu('Zusatzfunktionen')
	.addItem('SankeyDiagram', 'openDialog')
	.addToUi();
	}

	function getSpreadsheetData() {
	// ID of your Document, take from URL
	var ssID = "",
	// which Sheet? [0] is the first and so on...
	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');
	}


	<!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('main'));
	chart.draw(data, {width: 900, sankey: {iterations: 64}});
	}
	</script>
	</body>
	</html>