As I wrote in my previous blogs (Embedding google charts in a WordPress post, Yet another charting option – Highcharts Cloud ) if  data is being created in spreadsheets, one can create charts and link to them from within an app.  To embed the chart in an application or a web page (so that it can be linked to from an AppSheet app), the chart must be hosted on a server and preferably, it must not require any login or authentication to view it.

In this post, I will describe how to use the same Google Apps Script Service called Execution API that we used before to read the data and use the Google Visualization API and the Plotly.js graphing library to create a chart.

The graph is included below via an iframe – click here to view it in its own window. If you don’t see the graph, a one time authorization is required. Even though the spreadsheet that is serving the data is public, since it is owned by me, you need to click Authorize and enter your Google credentials to display the interactive chart.


Preparing Data for Plolty

There are several examples of charts at https://plot.ly/javascript/ and the type of chart we can use is the bubble chart. The complete reference for configuring and customizing this type of plot is here.

We need to prepare our spreadsheet data in a structure as shown below:

var trace1 = {
  name:'Europe',
  x: [79.84, 78.6, 80.05, 68.6],
  y: [1.36, 1.84, 2, 1.54],
  mode: 'markers',
  marker: {
  color: ['rgb(100,150,50)','rgb(100,150,50)','rgb(100,150,50)','rgb(100,150,50)'],
    size: [81.9, 5.52, 61.8, 141.85],
    sizemin: 4,
  },
  text: ['DEU','DNK','GBR','RUS'],
  hoverinfo:['text'],
  type: 'scatter'
};

To format the data returned by the Google visualization API in this structure, we can retrieve the spreadsheet data as a DataTable by using google.visualization.arrayToDataTable().Since we want to group the countries by Region, we can use getFilteredRows()to loop through the distinct ones in the Region column. Each region can have multiple countries, so, we need two for loops – the outer loop for the distinct regions and the inner loop for each country within that region. The listing for what goes on inside each of the loops is below:

for (var i = 0; i < regions.length; i++) {
  regrows[i] = data.getFilteredRows([{column:regcol,value:regions[i]}]);
  plotdata[i] = [];
  for (var j = 0; j < regrows[i].length; j++) {
    plotdata[i][j] = {le:data.getFormattedValue(regrows[i][j],lecol),
      fert:data.getFormattedValue(regrows[i][j],fertcol),
      pop:data.getFormattedValue(regrows[i][j],popcol)/1000000,
      cntry:data.getFormattedValue(regrows[i][j],cntrycol),
      }
  }
  x = _.range(regrows[i].length).map(function (j) { return plotdata[i][j].le });
  y = _.range(regrows[i].length).map(function (j) { return plotdata[i][j].fert });
  size = _.range(regrows[i].length).map(function (j) { return plotdata[i][j].pop });
  cntry = _.range(regrows[i].length).map(function (j) { return plotdata[i][j].cntry });
  
  trace[i] =  {
    name:data.getFormattedValue(regrows[i][0],regcol),
    x: x,
    y: y,
    mode: 'markers',
    marker: {
      color: _.range(regrows[i].length).map(function () { return colors[i] }),
      size: size,
    },
    text: cntry,
    hoverinfo:['text'],
    type: 'scatter'
  };
}

 

WordPress Login Protected by Clef