Buckwich · October 20, 2024 14:04
diff --git a/README.MD b/README.MD
diff --git a/code.gs b/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;
 }
diff --git a/index.html b/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>