How to make charts in Google Apps Script

The Charts Service

Google Apps Script offers a charts service. Using this you can build a dataTable as input to a chart, as well as the chart itself. Elsewhere on this site I covered how to get Excel data into Google Visualization Charts, and how to create Google Data Tables in such projects as Using Google Visualization charts and tables, Using Google Visualization DataViews, Embedding Google Motion Charts in Excel and various other articles. Now it turns out that Google Apps Script has something like these capabilities, but  rather less feature rich versions. As in What to use in place of Excel Shapes we use a UI panel to contain the chart object. The Excel versions of How to use the Excel Roadmapper shows how to create a 'cost of ownership chart' appended to a roadmap, and looks like this.


This section is about how to (try) to do the same thing in Google Apps Script. You can find the code (its a work in progress) and test data in the VBA to Google Apps Script Roadmapper project download


The cChartContainer class

Just as in the Excel version, we have a new class to encapsulate chart related stuff. The Google Script version ends up looking like this, although I still have to figure out how do some tweaking around the format of the chart.


Note in this case I've shown a stack column chart instead of a shale chart. This is selectable from the parameters sheet just like in Excel.



The chart container is invoked like this
cShapeContainer.prototype.makeChart = function () {
  this.xChartContainer = new cChartContainer(this.root());
  this.xChartContainer.makeChart();
  return this;
};

and does this.
cChartContainer.prototype.makeChart = function () {

  var rt = this.root();
  var shp = rt.shape();

 
  if (rt.chartStyle() != SCHARTTYPES.ctNone) {
    // the size of the chart will be a proportion of the roadmap size in parameter sheet
    var chtHeight = shp.height() * rt.chartProportion();
    // add a panel under the roadamap to contain the chart
    this.xChart = rt.addShape(SHAPETYPES.stPanel, shp.left(), shp.top() + shp.height(),
        shp.width(), chtHeight );
    // create the google table
    this.xBuilder =
      DebugAssert( Charts.newDataTable() , 'failed to create a data table builder');

    // add the roadmap data
    this.chartArray = [];
    this.makeAxes()
        .makeSeries();
       
    // transpose if necesay
    var chartArray = arrayTranspose(this.chartArray);
    // build a table
    this.xBuilder.addColumn(Charts.ColumnType.STRING,chartArray[0][0]) ;
    for (var i=0 ; i < chartArray[0].length ;i++)
      this.xBuilder.addColumn(Charts.ColumnType.NUMBER, chartArray[0][i+1]) ;
   
    // do the rows
    for (var i=1 ; i < chartArray.length ;i++)  {
      this.xBuilder.addRow( chartArray[i]) ;
    }

    // now build it
    this.xDataTable = DebugAssert( this.xBuilder.build() , 'failed to build a data table');
    // create the appropriate type of chart

    this.xChartBuilder = rt.chartStyle() == SCHARTTYPES.ctShale ?
                               DebugAssert( Charts.newAreaChart(), 'failed to build area chart') :
                               rt.chartStyle() == SCHARTTYPES.ctColumnStacked ?
                                 DebugAssert( Charts.newColumnChart(), 'failed to build column chart') :
                                 rt.chartStyle() == SCHARTTYPES.ctLine ?
                                   DebugAssert( Charts.newLineChart(), 'failed to build Line chart') :
                                     DebugAssert (false,'unknown chart type ' + rt.chartStyle());
    // tweak it
    this.xChartObject = this.xChartBuilder
                          .setDataTable(this.xDataTable)
                          .setStacked()
                          .setTitle(rt.text())
                          .setDimensions(shp.width(), chtHeight)
                          .setLegendPosition(Charts.Position.BOTTOM)
                          .build();
    // add it to the panel and commit to the api
    this.xChart.box().add(this.xChartObject);
    this.xChart.commit();
  }
  return this;
};

Note that we add a panel using the cShape class, in exactly the same way as we do for the roadmap shapes, and this panel is used to contain the created chart.  The sequence of events is
  • Figure out the dimensions of the chart-  it needs to be same width as the roadmap frame, and some proportion, as specified in the parameter sheet, of its height
  • Create a panel of that dimensions using the cShape class
  • Create a new charts.newdataTable(). This in fact a data Table Builder object, not a data Table in itself.
  • Create an array of the data from the roadmap and transpose it (see note below)
  • Add the data to the datatable builder and finish building the the table
  • Create a chart of the appropriate type. Again this is chartBuilder object, not an actual chart.
  • Set various formatting options, assign the data and build the chart.
  • Add it to the panel created earlier, and commit the shape.
It's worth noting that the xValues and yValues seem to be reversed from the way they are in Excel, so after making the array of data to be  plotted in makeAxes and makeSeries, I needed to transpose that array before adding it to the chartBuilder, since neither is there a switch/row column type property. You'll find the arrayTranspose() function in the hacks section and it looks like this.
function arrayTranspose(a) {
  //swap rows and columns- assumes this is a square array
  //first prepare the new shape - no of rows = input no of columns
  var v = new Array(a[0].length);
  // no of columns  input number of rows
  for(var i =0 ; i < v.length ; i++) v[i] = new Array(a.length);
  //now move the values in
  for (var i=0; i < a.length ;i++){
    for (var j=0; j < v.length ;j++)v[j][i] = a[i][j];
  }
  return v;
}

Take a look at how the From VBA to Google Apps Script to follow along with the Roadmap Generation migration project. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. You can find the code and test data in the VBA to Google Apps Script Roadmapper project download.

Code


Take a look at  From VBA to Google Apps Script for more like this.. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.  

Transitioning is covered more comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.






Comments