Customising Google Bar Chart Using Google App Script

Question

I am currently having some issues configuring a simple graph using Google App Scripts. I seem to be unable to find the correct documentation in order to progress any further!

I have everything hooked up pulling data from a couple of spreadsheets, so that aspect is fine!

I see that there are various ways in order to customise the looks of a chart and there are tools available for example:

http://imagecharteditor.appspot.com/

http://code.google.com/apis/ajax/playground/?type=visualization

I wish to add colours to my bar charts like in this example

http://code.google.com/apis/ajax/playground/?type=visualization#image_multicolor_bar_chart

Additionally in the first link there are options to create sections using the range marker tool. I was hoping that with these tools I could copy the code across to use in my App Script Chart.

The only way I can see this working is using .setOption(string, object)

I've tried this...

    var data = Charts.newDataTable()
        .addColumn(Charts.ColumnType.STRING, 'Month')      
        .addColumn(Charts.ColumnType.NUMBER, 'Mark Achieved')  
    for(var x=0; x < ChartData.length;x++){
        data.addRow(ChartData[x]);        
    }     
    data.build();

  var chart = Charts.newColumnChart()
      .setDataTable(data)     
      .setDimensions(1000, 600)      
      .setRange(0, 100)
      .setTitle('Test Scores')
      .setLegendPosition(Charts.Position.BOTTOM)  
      .setOption('options',{cht: 'bvs', chco: 'A2C180,3D7930', max: 100})      
      .build();          
  app.add(chart);

any help would be much appreciated!

EDIT

My Graph example

asgallant asgallant · Accepted Answer · 2013-07-24T17:03:08

The options you are trying to use are applicable to the static image charts (which are now deprecated), and won't work with ColumnCharts. ColumnCharts color the bars by series, not by data point, so if you want multi-colored bars, you have to separate them out into different data series. I wrote a hack that does this (see on jsfiddle for the standard javascript version). My reading of the AppsScript implementation of the Visualization API seems to preclude using calculated columns in the DataViews, but it is possible that the documentation is incomplete here. Try creating a view like this:

// add one calculated column for each month
var dataViewDefinition = Charts.newDataViewDefinition().setColumns([0, {
    type: Charts.ColumnType.NUMBER,
    label: 'Mark Achieved',
    calc: function (dt, row) {
        if (dt.getValue(row, 0) == 'January') ? dt.getValue(row, 1) : null;
    }
}, {
    type: Charts.ColumnType.NUMBER,
    label: 'Mark Achieved',
    calc: function (dt, row) {
        if (dt.getValue(row, 0) == 'February') ? dt.getValue(row, 1) : null;
    }
}/*...*/]);

It is probable that this needs to be tweaked, and possible that it won't work at all, in which case you would have to either change the query of the spreadsheet or rearrange the structure of the spreadsheet.

As far as adding the ranges to the chart, can you elaborate more on what you would like those to look like?

Customising Google Bar Chart Using Google App Script

1 Answers