Skip to content

Instantly share code, notes, and snippets.

@maximeborges

maximeborges/Sankey diagram.md

Last active July 9, 2025 23:01
Show Gist options
  • Save maximeborges/31b7bfe5e3d4d8e31163702ebb802631 to your computer and use it in GitHub Desktop.
Save maximeborges/31b7bfe5e3d4d8e31163702ebb802631 to your computer and use it in GitHub Desktop.
Download ZIP
Sankey Diagram with Google Spreadsheets
Raw
code.gs
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Diagrams')
.addItem('Sankey', 'openDialog')
.addToUi();
}
function getSpreadsheetData() {
var sheet = SpreadsheetApp.getActive().getActiveSheet();
return sheet.getDataRange().getValues();
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index')
.setHeight(800)
.setWidth(1000);
SpreadsheetApp.getUi().showModalDialog(html, 'Sankey Diagram');
}
Raw
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">
var height = 800;
var margin = 10;
google.load('visualization', '1', {
packages: ['corechart', 'sankey']
});
google.setOnLoadCallback(initialize);
function initialize() {
google.script.run.withSuccessHandler(drawChart).getSpreadsheetData();
}
function drawChart(rows) {
var i = 0;
var rows_cleaned = []
for (; i < rows.length; i++) {
if(!rows[i][0] && !rows[i][1] && !rows[i][2]) {
continue;
}
rows_cleaned.push(rows[i].slice(0, 3));
}
console.log(rows)
var data = google.visualization.arrayToDataTable(rows_cleaned);
var chart = new google.visualization.Sankey(document.getElementById('main'));
google.visualization.events.addListener(chart, 'ready', onReady);
chart.draw(data, {width: 1000 - 2*margin, height: height/2});
}
function onReady(e) {
let frame = document.querySelector("#main > div:nth-child(1) > div:nth-child(1)");
let svg = frame.querySelector("div:nth-child(1) > svg:nth-child(1)");
frame.setAttribute("style", "position: relative; width: 100%; height: 100%;");
svg.setAttribute("style", "padding-top: " + 3*margin + "px;");
svg.setAttribute("height", height - (2+3)*margin);
svgToBitmap(svg, (canvas) => {
var cpy_btn = document.createElement("button");
cpy_btn.innerHTML = 'Copy image';
cpy_btn.setAttribute("style", "position: absolute;");
cpy_btn.onclick = () => {
try {
canvas.toBlob(function(blob) {
const item = new ClipboardItem({ "image/png": blob });
navigator.clipboard.write([item]);
});
} catch (error) {
console.error(error);
}
};
frame.appendChild(cpy_btn);
});
}
function svgToBitmap(svg, cb) {
var canvas = document.createElement('canvas');
var ctx = canvas.getContext('2d');
var data = (new XMLSerializer()).serializeToString(svg);
var img = new Image();
var svgBlob = new Blob([data], {type: 'image/svg+xml;charset=utf-8'});
var DOMURL = window.URL || window.webkitURL || window;
var url = DOMURL.createObjectURL(svgBlob);
// load svg image into canvas
var image = new Image();
image.onload = function() {
canvas.width = image.width;
canvas.height = image.height;
var context = canvas.getContext('2d');
context.drawImage(image, 0, 0);
cb(canvas);
}
image.src = url;
}
</script>
</body>
</html>
Raw
Sankey diagram.md

image

Sankey Diagrams within Google Spreadsheets

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

Demo

Easy way

Go to my template Spreadsheet, go to File -> Create a copy.

This will copy the AppScript in a new project that you can start modifying.

The long way

Enabling AppScripts

If you already have the "Extensions" menu in the top toolbar, proceed to the next section.

Otherwise, it looks like Google Spreadsheets "Extensions" menu is not enabled by default. You might have to go to https://script.google.com/home and create a new project from there to make this menu appear.

Setup

  • Open a Spreadsheet
  • Click "Extensions" -> "Apps Scripts"
  • You should have a Code.gs file open, paste the corresponding code
  • Click on the + button on the left side to create a new file, choose HTML; name it ìndex (extension is added automatically
  • Paste the index.html code from below in there
  • Switch back to the Spreadsheet, reload the page to load the script

You should now have a "Diagrams" menu in the top toolbar.

Usage

  • Open a document for which you installed the script
  • Click "Diagrams" -> "Sankey"

This can take a few seconds, then you will see the diagram in a dialog window.

You can click on "Copy image" to get a PNG version of the diagram that you can paste directly in your spreadsheet.

Example Spreadsheet

https://docs.google.com/spreadsheets/d/1Qn5JXeWBFCneHvwyJjfHhZ0TB-ee1Lm9xpJ_9gAsDbY/edit?usp=sharing

@alistair0adams
Copy link

I changed your code too much while figuring out this problem so this is the easiest way.
Here's a function you need to add:

    function cloneSVG(svg) {
      // Cloning solves for cases where the fill property of path is a gradient 
      // and that gradient it referenced via a url. The url generated by the
      // sankey package needs to be made local.

      const clone = svg.cloneNode(true);

      // Inline computed styles
      const cloneElements  = clone.querySelectorAll("*");
      const originalElements = svg.querySelectorAll("*");

      originalElements.forEach((el, i) => {
        const computed = getComputedStyle(el);

        // Just want the "#_ABSTRACT_RENDERER_ID_1" part of the url of this:
        // url(https://n-jwqt444pb5szz6nnsf64j6vfrmf2fof6sk6cfni-0lu-script.googleusercontent.com/userCodeAppPanel#_ABSTRACT_RENDERER_ID_1)
        const re = /#[_A-Z0-9]*/

        for (let prop of computed) {
          if (prop === "fill") {
            var computedFillProp = computed.getPropertyValue(prop);
            const found = computedFillProp.match(re);
            if (found !== null) {
              computedFillProp = `url(${found[0]})`;
              try {
                cloneElements[i].style.setProperty(prop, computedFillProp, computed.getPropertyPriority(prop));
              } catch (e) {
                console.warn(`Could not copy style property: ${prop}`, e);
              }
            }
          }
        }
      });

Then where the serializer is called, change the parameter to call cloneSVG() like below

      const data = (new XMLSerializer()).serializeToString(cloneSVG(svg));

To add gradients, add the following just before the call to chart.draw(data, options);

      var colors = ['#a6cee3', '#b2df8a', '#fb9a99', // '#fdbf6f',
              '#cab2d6', '#ffff99', '#1f78b4', '#33a02c']
      var options = {
        width: 300 - 2*margin, 
        height: height - 100, 
        sankey: {
          iterations: 8,
          node: {
            // colors: colors,
          },
          link: {
            colorMode: 'gradient',
            colors: colors,
          }
        }
      }

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment