1

I'm trying to plot a heatmap chart using plotly with data being pulled from Google sheets (as json) using Tabletop.js. I have so far accomplished to pull data from Googleseet as json and display it in the console. But, getting it to display as a heatmap is proving cumbersome. Any help would be greatly appreciated.

var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1BFPjROVypCGNeLxhk5_PW6PoOb4FDzJsL3DEOEdW_Rc/edit?usp=sharing';

function init() {
  Tabletop.init({
    key: publicSpreadsheetUrl,
    callback: showInfo,
    simpleSheet: true
  })
}

function showInfo(data, tabletop) {
  console.log(data);
}

window.addEventListener('DOMContentLoaded', init)

var data = [{
  graphdata,
  type: 'heatmap'
}];

Plotly.newPlot('myDiv', data1);
31piy
  • 23,323
  • 6
  • 47
  • 67
Ted
  • 15
  • 7

2 Answers2

0

Plotly's heatmap needs a two dimensional array (y being the first index, x the 2nd) for its z-values. tabletop returns a JSON, so it needs to be parsed into an array.

var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1BFPjROVypCGNeLxhk5_PW6PoOb4FDzJsL3DEOEdW_Rc/edit?usp=sharing';

function init() {
  Tabletop.init({
    key: publicSpreadsheetUrl,
    callback: showPlot,
    simpleSheet: true
  })
}

function showPlot(data, tabletop) {
  var xValues = []; //all the values which are shown on the x-axis
  var yValues = []; //all the values which are shown on the y-axis

  //get all possible x and y-values
  for (var i = 0; i < data.length; i++) {
    if (xValues.indexOf(data[i].x) === -1) {
      xValues.push(data[i].x);
    }
    if (yValues.indexOf(data[i].y) === -1) {
      yValues.push(data[i].y);
    }
  }
  
  //create an empty array for all possible z-values based on the dimensions of x and y
  var zValues = new Array(yValues.length).fill(0).map(row => new Array(xValues.length).fill(0));

  var x = 0;
  var y = 0;

  for (i = 0; i < data.length; i++) {
    x = xValues.indexOf(data[i].x);
    y = yValues.indexOf(data[i].y);
    if (x !== -1 && y !== -1) {
      zValues[y][x] = parseFloat(data[i].z);
    }
  }

  //the data which is passed to Plotly
  var plotlyData = [{
    x: xValues,
    y: yValues,
    z: zValues,
    type: 'heatmap'
  }];
  
  //finally draw the plot
  Plotly.plot('myPlot', plotlyData);
}

init()
<script src="https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js"></script>
<script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
<div id="myPlot"></div>
Maximilian Peters
  • 30,348
  • 12
  • 86
  • 99
0

Please see my comments. I've described how you can convert the data to a needed format. I used to used lodash in order to do that.

var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1BFPjROVypCGNeLxhk5_PW6PoOb4FDzJsL3DEOEdW_Rc/edit?usp=sharing';

    function init() {
        Tabletop.init({
            key: publicSpreadsheetUrl,
            callback: showInfo,
            simpleSheet: true
        })
    }

    function showInfo(data, tabletop) {
        if (!data || !data.length) {
            throw new Error('Unable to get any data from the spreadsheet');
        }

        // First we want to parse some values
        var parsed = data.map(val => {
            return {month: val.x, year: parseInt(val.y), z: parseFloat(val.z)};
        });


        // I'm going to hardcode the months here as we wanna keep the order.
        // You might consider using something like moment.js to get month names.
        var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];

        var minYear = _.minBy(parsed, p => p.year);
        var maxYear = _.maxBy(parsed, p => p.year);

        // Create the range of years. This is going to be our Y axis.
        var years = _.range(minYear.year, maxYear.year);

        // Now we are going to iterate over months and years, searching for values.
        // By doing this we're going to end up with the data format accepted by plotly.
        // https://plot.ly/javascript/heatmaps/#heatmap-with-categorical-axis-labels
        var zValues = _.map(months, m => {
            return _.map(years, y => {
                var matchingElement = _.find(parsed, p => p.year === y && p.month === m);
                if (matchingElement) {
                    return matchingElement.z;
                }
                // If we haven't found the value fill it with null.
                return null;
            })
        })

        var chartData = [{
            z: zValues, x: months, y: years,
            type: 'heatmap'
        }];

        Plotly.newPlot('myDiv', chartData);
    }

    window.addEventListener('DOMContentLoaded', init)
<div id="myDiv"></div>

<script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/lodash.js/4.17.4/lodash.js'></script>

<!-- Latest compiled and minified plotly.js JavaScript -->
<script type="text/javascript" src="https://cdn.plot.ly/plotly-latest.min.js"></script>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Antonio Narkevich
  • 4,206
  • 18
  • 28