Buckwich/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.

Fork of @njoerd114

Changes:

update usage instruction for new Apps Script IDE
does not need document Id (@steren)
supports named sheet (not default, see customization in code.gs)
shows total values (default, see customization in code.gs to disable)
added some comments in code.gs to highlight customization options

Installation


Open a spreadsheet
Click "Extensions" -> "App Script"
create the two files code.gs and index.html and paste to code of this gist
insert the document ID from the URL on line 10, code.gs
select which sheet is the base for the sankey diagram
save the project (no need to deploy)
the layout of the table should be as shown in the Example Spreadsheet
reload the sheet and click "Custom Scripts" -> "SankeyDiagram" (it can take a view seconds to load the new menu and the diagram)
in case of a private sheet you have to authorize the script (probably marked as unsave developer, but it should be your own google account)

Usage


Open the same document
Click "Custom Scripts" -> "SankeyDiagram" (it can take a view seconds to load the new menu and the diagram)

Example Spreadsheet

only for format reference, does not show diagram
https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/

  
## code.gs
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
    .createMenu("Custom Scripts") // Customization: Menu Name
    .addItem("SankeyDiagram", "openDialog") // Customization: Item Name
    .addToUi();
}

function getSpreadsheetData() {
  sheet = SpreadsheetApp.getActive().getSheets()[0]; // Customization: Select Sheet (0 is first sheet)
  // Alternatively use a named sheet
  // sheet = SpreadsheetApp.getActive().getSheetByName("Sankey") // Customization: Sheet Name
  data = sheet.getDataRange().getValues();

  // Customization: Choose between with/without values
  // return data; // uncomment this line if you *do not* want to see the values directly
  return addTotalLabels(data); // uncomment this line if you want to see the values directly
}

function openDialog() {
  let html = HtmlService.createHtmlOutputFromFile("index")
    .setHeight(300)
    .setWidth(1000);
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
    .showModalDialog(html, "Sankey Diagram"); // Customization: Diagram Title
}

function addTotalLabels(rows) {
  let srcTotals = sumValuesByKey(rows, 0, 2);
  let dstTotals = sumValuesByKey(rows, 1, 2);

  rows.forEach((row) => {
    row[0] = `${row[0]} (${srcTotals[row[0]]})`;
    row[1] = `${row[1]} (${dstTotals[row[1]]})`;
  });

  return rows;
}

function sumValuesByKey(array, keyIndex, valueIndex) {
  let result = {};
  for (let i = 0; i < array.length; i++) {
    let key = array[i][keyIndex];
    let value = array[i][valueIndex];
    if (result[key]) {
      result[key] += value;
    } else {
      result[key] = value;
    }
  }
  return result;
}

## 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("Custom Scripts") // Customization: Menu Name
	.addItem("SankeyDiagram", "openDialog") // Customization: Item Name
	.addToUi();
	}

	function getSpreadsheetData() {
	sheet = SpreadsheetApp.getActive().getSheets()[0]; // Customization: Select Sheet (0 is first sheet)
	// Alternatively use a named sheet
	// sheet = SpreadsheetApp.getActive().getSheetByName("Sankey") // Customization: Sheet Name
	data = sheet.getDataRange().getValues();

	// Customization: Choose between with/without values
	// return data; // uncomment this line if you do not want to see the values directly
	return addTotalLabels(data); // uncomment this line if you want to see the values directly
	}

	function openDialog() {
	let html = HtmlService.createHtmlOutputFromFile("index")
	.setHeight(300)
	.setWidth(1000);
	SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
	.showModalDialog(html, "Sankey Diagram"); // Customization: Diagram Title
	}

	function addTotalLabels(rows) {
	let srcTotals = sumValuesByKey(rows, 0, 2);
	let dstTotals = sumValuesByKey(rows, 1, 2);

	rows.forEach((row) => {
	row[0] = `${row[0]} (${srcTotals[row[0]]})`;
	row[1] = `${row[1]} (${dstTotals[row[1]]})`;
	});

	return rows;
	}

	function sumValuesByKey(array, keyIndex, valueIndex) {
	let result = {};
	for (let i = 0; i < array.length; i++) {
	let key = array[i][keyIndex];
	let value = array[i][valueIndex];
	if (result[key]) {
	result[key] += value;
	} else {
	result[key] = value;
	}
	}
	return result;
	}


	<!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>