Buckwich · October 20, 2024 14:04 · Feb 4, 2023 · Feb 4, 2023 · Feb 4, 2023 · Feb 4, 2023
diff --git a/README.MD b/README.MD
@@ -7,8 +7,8 @@
 Changes: 
 - update usage instruction for new Apps Script IDE
 - does not need document Id (@steren)
-- supports named sheet (not default, see cusomization in `code.gs`)
-- shows total values (default, see cusomization in `code.gs` to disable)
+- 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
 
 

diff --git a/README.MD b/README.MD
@@ -21,12 +21,13 @@ Changes:
 - 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](https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/)
-- reload the sheet and click "Zusatzfunktionen" -> "SankeyDiagram"
+- 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 "Zusatzfunktionen" -> "SankeyDiagram" (it caan take a view seconds to load the new menu and the diagram)
+- Click "Custom Scripts" -> "SankeyDiagram" (it can take a view seconds to load the new menu and the diagram)
 
-## Example Spreadsheet
+## Example Spreadsheet 
+only for format reference, does not show diagram
 https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/
diff --git a/README.MD b/README.MD
@@ -2,7 +2,15 @@
 
 > This Gist is there to help you creating a Sankey Diagram from your Google Spreadsheets.
 
-> Fork of @njoerd114 to update instructions
+## Fork of @njoerd114
+
+Changes: 
+- update usage instruction for new Apps Script IDE
+- does not need document Id (@steren)
+- supports named sheet (not default, see cusomization in `code.gs`)
+- shows total values (default, see cusomization in `code.gs` to disable)
+- added some comments in `code.gs` to highlight customization options
+
 
 ## Installation
 

diff --git a/code.gs b/code.gs
@@ -1,23 +1,51 @@
 function onOpen() {
-    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
-        .createMenu('Zusatzfunktionen')
-        .addItem('SankeyDiagram', 'openDialog')
-        .addToUi();
+  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
+    .createMenu("Custom Scripts") // Customization: Menu Name
+    .addItem("SankeyDiagram", "openDialog") // Customization: Item Name
+    .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;
+  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() {
-    var html = HtmlService.createHtmlOutputFromFile('index')
-        .setHeight(300)
-        .setWidth(1000);
-    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
-        .showModalDialog(html, 'Sankey Diagram');
-}
+  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/README.MD b/README.MD
@@ -1,10 +1,8 @@
-<!-- [![njoerd114](https://avatars1.githubusercontent.com/u/3825200?v=3&s=200)](http://niklasbeinghaus.com) -->
-
 # Sankey Diagrams within Google Spreadsheets  
 
 > This Gist is there to help you creating a Sankey Diagram from your Google Spreadsheets.
 
-> Fork of [![njoerd114](https://avatars1.githubusercontent.com/u/3825200?v=3&s=200)](http://niklasbeinghaus.com) to update instructions
+> Fork of @njoerd114 to update instructions
 
 ## Installation
 

diff --git a/README.MD b/README.MD
@@ -4,18 +4,23 @@
 
 > This Gist is there to help you creating a Sankey Diagram from your Google Spreadsheets.
 
+> Fork of [![njoerd114](https://avatars1.githubusercontent.com/u/3825200?v=3&s=200)](http://niklasbeinghaus.com) to update instructions
+
 ## Installation
 
 - Open a spreadsheet
-- Click "Tools" -> "Scripts"
-- create the two files `code.gs` and `index.html`
+- 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](https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/)
+- reload the sheet and click "Zusatzfunktionen" -> "SankeyDiagram"
+- 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 "Zusatzfunktionen" -> "SankeyDiagram"
+- Open the same document 
+- Click "Zusatzfunktionen" -> "SankeyDiagram" (it caan take a view seconds to load the new menu and the diagram)
 
 ## Example Spreadsheet
 https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/
diff --git a/README.MD b/README.MD
@@ -11,7 +11,7 @@
 - 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
+- the layout of the table should be as shown in the [Example Spreadsheet](https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/)
 
 ## Usage
 - Open the same document

diff --git a/README.MD b/README.MD
@@ -18,4 +18,4 @@
 - Click "Zusatzfunktionen" -> "SankeyDiagram"
 
 ## Example Spreadsheet
-https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/edit?usp=sharing
+https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/
diff --git a/README.MD b/README.MD
@@ -17,4 +17,5 @@
 - Open the same document
 - Click "Zusatzfunktionen" -> "SankeyDiagram"
 
-  source | destination | value
+## Example Spreadsheet
+https://docs.google.com/spreadsheets/d/1IUmvhYuuBvfYEu8OfDczmW6LQW-xaeh5c6rklOg3x-o/edit?usp=sharing
diff --git a/README.MD b/README.MD
@@ -12,4 +12,9 @@
 - 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
+
+## Usage
+- Open the same document
+- Click "Zusatzfunktionen" -> "SankeyDiagram"
+
   source | destination | value
diff --git a/README.MD b/README.MD
@@ -0,0 +1,15 @@
+<!-- [![njoerd114](https://avatars1.githubusercontent.com/u/3825200?v=3&s=200)](http://niklasbeinghaus.com) -->
+
+# 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
+  source | destination | value
diff --git a/code.gs b/code.gs
@@ -0,0 +1,23 @@
+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');
+}
diff --git a/index.html b/index.html
@@ -0,0 +1,28 @@
+
+
+<!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>