Display Google spreadsheet data using Highcharts library

Question

I have two columns (Date, Distance) data in a Google spreadsheet. Now I want to display distance column using pie chart such that different slice of pie chart shows the sum of distances covered on different days (Sunday, ..., Saturday) of a week using Highcharts library.

Being a noob in web development, I have following queries:

Do I need only jQuery to plot the required data?
Although I figured out that I need to provide the reference of this spreadsheet in Highcharts library, but I do not know how to process this data. Do I need to process both of these columns and figure out which date corresponds to which day of the week and then compute sum? If yes, where should I write this logic?

It would be great if you can give me a dummy example to proceed further.

Are you using Highcharts could? You can plot the data through that if so, see highcharts.com/cloud/import-data/… and then embed it in your site as an iframe. There's also the option to build your own, like you said, see this pen for an example codepen.io/fleeting/pen/AtcrE — Valentin

Cooper Cooper · Accepted Answer · 2017-08-01T19:04:34

Here's a function I wrote for making a pie chart from two columns. Perhaps you can adapt it to your needs. They name of the sheet is 'PieChart' right now.

function makePie(dataColName,labelColName) 
{
  var dataColName = (typeof(dataColName)!='undefined')?dataColName:'Data';
  var labelColName = (typeof(labelColName)!='undefined')?labelColName:'Labels';
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sht=ss.getSheetByName('PieChart');
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  if(rngA[0].indexOf(dataColName)>-1 && rngA[0].indexOf(labelColName)>-1)
  {
    var datcol=rngA[0].indexOf(dataColName)+1;
    var lblcol=rngA[0].indexOf(labelColName)+1;
    var datrng=sht.getRange(2,datcol,rng.getHeight());
    var lblrng=sht.getRange(2,lblcol,rng.getHeight());
    var charts=sht.getCharts();
    if(charts)
    {
      for(var i=0;i<charts.length;i++)
      {
        if(charts[i].getOptions().get('title') == sht.getName())
        {
          sht.removeChart(charts[i]);
        }
      }
    }
    var chart = sht.newChart()
                .setChartType(Charts.ChartType.PIE)
                .addRange(lblrng)
                .addRange(datrng)
                .setPosition(2, rng.getLastColumn()+1, 0, 0)
                .setOption("pieHole",".3")
                .setOption("is3D", "true")
                .setOption('title',sht.getName())
                .build();
    sht.insertChart(chart);
   }
}

Display Google spreadsheet data using Highcharts library

2 Answers