Change column bar colors on chart

[Justawebbie]

I know this is just me getting myself confused as I read all the different ways everyone is doing this. I am trying to color each column a different color and use tooltips all coming from a google spreadsheet. I have tried doing isStacked with 0's in the other columns which worked but my tooltips stopped working for the 2 of the 3 columns.  I then went back to the drawing board so now I am stuck.  I think I just have this a bit off in coding or I am way off base on it.  Thanks in advance for any help you give to this newby.

    var visualization;

    function drawVisualization() {
        var query = new google.visualization.Query(
            'http://spreadsheets.google.com/tq?key=0AjlSK7_zXoNHdHJ3ZXY2VHAyUWhXcVVkdGUwdXNCeHc&pub=1');
   
        // Apply query language statement.
       
       
        // Send the query with a callback function.
        query.send(handleQueryResponse);
      }
   
      function handleQueryResponse(response) {
        if (response.isError()) {
          alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
          return;
        }
 var view = new google.visualization.DataView(data);
    view.setColumns([0, {
        type: 'number',
        label: 'Value',
        calc: function (dt, row) {
            return (dt.getValue(row, 1) > 10) ? dt.getValue(row, 1) : null;
        }
    }, {
        type: 'number',
        label: 'Value',
        calc: function (dt, row) {
            return (dt.getValue(row, 1) < 10 && dt.getValue(row, 1) > 30) ? dt.getValue(row, 1) : null;
        }
    }, {
        type: 'number',
        label: 'Value',
        calc: function (dt, row) {
            return (dt.getValue(row, 1) < 30 && dt.getValue(row, 1) < 50) ? dt.getValue(row, 1) : null;
        }
    }]);
        var data = response.getDataTable();
         // set the 3rd column to the "tooltip" role
        data.setColumnProperty(2, 'role', 'tooltip');
        visualization = new google.visualization.ColumnChart(document.getElementById('visualization'));
        visualization.draw(data, view, {backgroundColor: '#ffffff',legend: 'none', colors:['#336699'],is3D:'True', isStacked:'true'});
       
       
      }
     
     
    google.setOnLoadCallback(drawVisualization);

[asgallant]

To get the tooltips, you have to include the tooltip column after every data column in the view.  Also, if you are looking for different colors for each bar, you might be better off assigning data to columns by name rather than by value (example here: http://jsfiddle.net/asgallant/zwPuU/).  Here's an example using your code as the base: http://jsfiddle.net/asgallant/JENzE/

[Justawebbie]

Thank you so much Asgallant, that really helped me to see what I did wrong and it is nice.

Take care and again thank you for your help.

[Justawebbie]

I just noticed the custom tooltips from the google spreadsheet do not work.  How can I get them to work with what you were able to show me to do with custom colors for each bar?

Thanks for the help beforehand Asgallant!

[asgallant]

Oops, I left out the tooltip column in the link I posted.  Is this what you're looking for: http://jsfiddle.net/asgallant/JENzE/3/?

[Justawebbie]

Oh yes thank you so much Asgallant!  Have a great week!

Take care, justawebbie

[asgallant]

You're welcome.

[Justawebbie]

I hope I do not drive you nuts Asgallant or anyone else who helps me out!

I tried to implement your column bar color change to a bar chart.  I got to work the way I wanted but I can not use isStacked:'true' because I do not want them to stack but to go side by side like it is now.  The problem I am having is the bars are way to small for the size I need it to be in width and height.  Did I do something wrong?

here is the code I am using:

   google.load('visualization', '1', {packages: ['corechart']});

function drawVisualization() {
  var query = new google.visualization.Query('http://spreadsheets.google.com/tq?key=0AjlSK7_zXoNHdFZ6NmJTaU1vNjNxWFZMQ3AxcWVHRVE&pub=1');

  // Apply query language statement.
  // Send the query with a callback function.
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
 

  var data = response.getDataTable();
  // set the 3rd column to the "tooltip" role

  data.setColumnProperty(3, 'role', 'tooltip');
  data.setColumnProperty(4, 'role', 'tooltip');

 
  var view = new google.visualization.DataView(data);
  view.setColumns([0, {
    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      console.log(dt.getValue(row, 0));
      return (dt.getValue(row, 0) == 'NOx') ? dt.getValue(row, 1) : null;
    }
  }, 3, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'NOx') ? dt.getValue(row, 2) : null;
    }
  }, 4, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'CO') ? dt.getValue(row, 1) : null;
    }
  }, 3, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'CO') ? dt.getValue(row, 2) : null;
    }
  }, 4, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'PM') ? dt.getValue(row, 1) : null;
    }
  }, 3, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'PM') ? dt.getValue(row, 2) : null;
    }
  }, 4, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'PM10') ? dt.getValue(row, 1) : null;
    }
  }, 3, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'PM10') ? dt.getValue(row, 2) : null;
    }
  }, 4,{

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'PM2.5') ? dt.getValue(row, 1) : null;
    }
  }, 3,{

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'PM2.5') ? dt.getValue(row, 2) : null;
    }
  }, 4, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'SO2') ? dt.getValue(row, 1) : null;
    }
  }, 3, {

    type: 'number',
    label: 'Value',
    calc: function (dt, row) {

      return (dt.getValue(row, 0) == 'SO2') ? dt.getValue(row, 2) : null;
    }
  }, 4]);
 
  var visualization = new google.visualization.BarChart(document.getElementById('visualization'));
  visualization.draw(view, {
    backgroundColor: '#F7F7F7',
    legend: 'none',
    //colors: ['#336699'], // with only one entry here, you will never get more than 1 color
    is3D: 'True',
  });
}

google.setOnLoadCallback(drawVisualization);


Thanks for the help in advance.

justawebbie in learning

[asgallant]

No, you didn't do anything wrong.  That chart is drawing exactly the way it is supposed to.  Each of your rows of data has 12 columns being drawn, but most of them are null.  They still occupy space along the axis, though, which is why the columns seem so narrow.  This is in fact the exact reason why setting isStacked to true is necessary to make the hack work right.

[Sherrie]

ok just means seeing how I can manipulate data to show the way I want in the spreadsheet then.  I was wondering how do I customize the color for each column bar now? I tried adding in the colors in options but they did not work.

thanks again for all your  help on this to.

justawebbie in learning

[asgallant]

If you entered an array of HTML-style colors (eg, "red", "#0066A7", etc), then it should color each data series in order using the colors in the array (looping back to the start of the array if there aren't enough entries for all series).

[SSH]

Hello, asagallant,

In the view portion of the code, is there a more generic way of coding this than specifying 'Germany', etc.?  I would like to make the code more flexible so that I can vary the # of bars sent to the JavaScript function & have it work automatically. 

Thanks,
Scott

[asgallant]

This is a method that handles it generically, regardless of the number of labels: http://jsfiddle.net/asgallant/JENzE/5/

[SSH]

Hello, asgallant,

Thanks!  Would you mind editing your code from this example (earlier in the thread):  http://jsfiddle.net/asgallant/zwPuU/ to achieve the same effect?

Also, could you direct me to some Google documentation on the view part of the code?  The basic documentation for Google really does not have enough examples.

Thanks,
Scott

[asgallant]

Certainly - this has actually been on my to-do list for a while now, as I've been unhappy with the fixed nature of the base code: http://jsfiddle.net/asgallant/zwPuU/

The ChartWrapper is defined here, and the DataView (which is what the "view" parameter of the ChartWrapper specifies) is defined here.  The documentation for the API needs a lot of work, as it is particular impenetrable for new users.  I've been using the API for over 2 years now, and even I get frustrated when trying to look up information to make sure that I am answering a question correctly.  The examples on the Visualization API Playground (DataView example) are a bit better, though many of them are outdated.

[SSH]

Andrew,

Thanks!  I'm glad you are willing to share code in Google Groups, and that you are active in helping other programmers!

Scott

[asgallant]

You're welcome.

[SSH]

Hi, Andrew,

Would you explain how the code works in the section called "build the columns for the view", especially the "columns.push" part?  This looks complicated.

Thanks,
Scott

On Monday, April 29, 2013 6:19:06 PM UTC-4, asgallant wrote:

[asgallant]

Sure.  In order to give each label a unique color, we have to separate each one out into its own data series, but since we are building this list of series dynamically, we have to parse the data for a list of all unique label, which is what this does:

// get a list of all the labels in column 0

var group = google.visualization.data.group(data, [0], []);

which groups "data" by column 0 into a new DataTable "group".  Now that we have a list of unique labels, we can start creating data series for them by building an array of columns for a DataView.  The columns in a DataView can be either integer values pointing to the indices of columns in the DataTable that the view is based on, or objects describing calculated columns (which create dynamic content based on the original DataTable).   We start with column 0, as this contains the labels:

var columns = [0];

and then we iterate over the group DataTable to add a calculated column for each label:

for (var i = 0; i < group.getNumberOfRows(); i++) {

    //...

}

Inside the loop, we get the label from the current row in group:

var label = group.getValue(i, 0);

and then add a column for the new series to the list of columns:

columns.push(/*...*/);

The structure of the calculated column is an object with up to 6 properties: "calc" is a function which accepts two parameters (a DataTable and a row index) and returns a value of the appropriate data type of the calculated column, "column" references a column from the base DataTable (useful when you are using the view to add properties or data roles to an existing column without modifying the contents of the column), "label" gives the column a label (what shows up in the legend and tooltips of the chart, "properties" takes an object of column properties to apply to the column, "role" gives the column a data role, and "type" tells the view what the data type of the column is (string, number, date, etc).  The object must contain either the "column" property or both the "calc" and "type" properties; all others are optional.  In our case, we need "type", "label", and "calc".  Our data type is "number" and we want the legend and tooltips to show the country name, so we assign the "label" property the label variable.  The "calc" property is a bit more complicated.  We want to create a data series that only contains values where the label is equal to the label for this series, which gives us a starting function to work from:

function (dt, row) {

    return (dt.getValue(row, 0) == label) ? dt.getValue(row, 1) : null;

}

The view passes a DataTable reference (which will be the data DataTable) and the row to populate into this function.  The function checks the value of column 0 in this row for the label.  If that value is the same as the label, then it returns the value in column 1, otherwise it returns null.

The problem with this function is that the value of "label" changes when we iterate over the group DataTable, so by the time we get to drawing the chart, all of the columns' "calc" functions will be referencing the same label, which is not at all what we want.  To fix this, we use a closure that maps the current value of label to another value inside the closure:

(function (name) {

    //...

})(label)

This maps the value of label to the "name" variable inside the closure.  Since closures execute immediately, and we want "calc" to have a function, we need to return a function inside the closure

return function (dt, row) {

    return (dt.getValue(row, 0) == name) ? dt.getValue(row, 1) : null;

};

Note that we changed the label variable to name so that it references the locked value that we pass to the closure.

*phew*

That explanation might not be exactly clear, so if please feel free to ask for clarification on any point.

[SSH]

Hi, Andrew,

Wow - thanks!  I'll read this carefully & get back with Q's.

One more thing that would make the bar charts helpful is to add the data labels either inside or outside the bars.  I know that I can do this with an Image chart (now deprecated), but would like to do it with this type of chart as well.  I've looked at some of the documentation, and it does not appear that we can add this feature yet.  Do you know of a way?

thanks,
Scott

[asgallant]

I have a hack that adds labels to ColumnCharts, but it only works in limited circumstances: http://jsfiddle.net/asgallant/QjQNX/

[SSH]

Andrew,

Thanks.  In your example:  http://jsfiddle.net/asgallant/zwPuU/  I am using an array of colors that are assigned alphabetically, but I would rather have the color assigned by the order of the data array.  I'm trying to think of what to change in the options, or does the code need a change?

Thanks,
Scott

[asgallant]

The group is returning the labels in alphabetic order, which then makes the data series built in alphabetic order.

There are alternative ways to handle this that can preserve the original order, but they either depend on the data having no duplicate labels or using additional layers of data abstraction to preserve the original order.  If your data is guaranteed to not have any duplicate labels (or you don't care that duplicates get assigned different colors), you can skip the grouping altogether and just pull the labels directly from the base data set: http://jsfiddle.net/asgallant/zwPuU/7/

[SSH]

Thanks, Andrew!!

The bars I'm plotting are mutually exclusive.  I definitely want to order the bar color based on the order of the data.  Your code helps to do this!

Thanks,
Scott

[asgallant]

How would you like to change it?

On Tuesday, April 30, 2013 5:03:29 PM UTC-4, SSH wrote:

Hi, Andrew,

How can I change the code to customize the label that appears when the user hovers?

Thanks,
Scott

[SSH]

Hi, Andrew,

Right now, it appears as:  "Germany" and then a second line underneath that with "Germany:  700".  I would like to change the 2nd line to be, for example, "Value:  700" (adding the word "Value" to it).  In other instances, I might want to add other information into the tool tip.  In my case, I have percentages represented - I might also want to display the # of patients from a hospital setting, or other descriptive information.

Thanks,
Scott

[asgallant]

Ok, the way the API populates tooltips, you won't be able to get what you want using the built-in tooltips, unless you change the series label (in the calculated column) to "Value", which would break the legend (you would have a bunch of data series called "Value" in the legend instead of the country names).  What you are going to need to do is use custom HTML tooltips, which are as flexible as you need them to be.  Any content that you can present in HTML you can put in the tooltip.  My suggestion would be to come up with a template for a tooltip, and then we can work on populating it the way you want it.

[SSH]

Hi, Andrew,

I'll use HTML tooltips.  Do you have an example?  I've seen some other threads on tooltips.

For the API tooltip, though, it would be nice to have the 2nd line of it, in my case, say something like "[Category Name]:  30%" and to make sure that I can add the '%' into the text.  If there's an easy way to modify the code for this, that would be great as well.

Thanks,
Scott

[asgallant]

Value formatting can be handled with the API's formatters.  The tooltip template should focus on structure, something like:

<div class="tooltip">

    <span class="title">{x-axis value}</span><br />

    <span class="category_name">{category name}:</span> <span class="value">{y-axis value}</span>

</div>

When you have a structure that you like, we can work on implementing it in the chart.

[SSH]

Hi, Andrew,

I've attached an example.  I modified the original example with Germany.  This compares 6 hospitals on a certain measure given as a percent.  The tooltip has "Hosp A:  30" and I would like it to be, instead, "Hosp A:  30%".  How can I do this?

Thanks,
Scott

[asgallant]

You can format the numbers as percents with this:

var formatter = new google.visualization.NumberFormat({pattern: "#'%"});

formatter.format(data, 1);

I took your example and implemented custom tooltips in it, so you can see how it works (see attached).

[SSH]

Wow!  THANK YOU!!  This is phenomenal!

This next request is more challenging:  A 3rd line in the tooltip would be great to add, for example:  "For more information on Hosp A, click here." where here would be a separate web page on the site.

I now see some documentation on tooltips at Google (https://developers.google.com/chart/interactive/docs/customizing_tooltip_content), and I'll read it more, but the code you wrote is fabulous.

Thanks,
Scott

[asgallant]

Add a column with URLs for the hospitals, and then reference it in the tooltips (see attached).

[SSH]

Hi, Andrew,

Looks good.  The tooltip needs to hover there for a few seconds, though, since it is sometimes disjointed from the bar, and it's hard for the user to get to the URL inside the box.  I'm trying to figure that out right now.

Scott

[asgallant]

Unfortunately, the timing of the removal of tooltips is not customizable.   You can use click events on the bars to open a link, if you want to go that route.

[SSH]

Hi, Andrew,

 

Thanks a million for all your help.  I have it working quite well thanks to you.  I'll look into more of the documentation & keep working with this.

 

Thanks!

Scott

[WebGeorge]

Hi all,

I'm a real newbie here just trying to do a freebie for my kids primary school so really appreciate any assistance.

I just want to have a Google Spreadsheet to maintain the School House Points whih then updates a basic column chart on our website (hosted in Weebly).  We have 4 houses, Red, Blue, Green & Yellow so I'd like the columns to reflect those colors.  I can create the exact chart I want but don't seem to be able to get the columns to change colors.  I can get them ALL to change to a new (same) color, but not each to a different color.  I feel like I'm so close!

I found this thread and http://jsfiddle.net/asgallant/JENzE/ does almost exactly what I'm trying to achieve but I can't for the life of me figure out how to get it into Weebly.  (I'm not a programmer - just someone who knows enough to make a mess and ask for help ;-)  )

If I could just get this example working in Weebly I could figure out the rest (I think) as then it's just a few chart options I'd like to change.

Any advice on how to get it into Weebly?

Thanks

[WebGeorge]

Just further to my post below...

This is how I'd like it to look on my Weebly website:

http://jsfiddle.net/WebGeorge/VSZnD/1/

This is as close as I've made it with the data coming from a Google Spreadsheet - what am I missing? 

http://jsfiddle.net/WebGeorge/uhq4x/1/

Why can't I get the color options to work on the columns to make it look like the one above where the data is entered in the code rather than a datasoure?  Can I do this with just HTML (much easier to manage in Weebly?)

Many thanks.

[WebGeorge]

OK - persistence sometimes pays. 

 

I've got it all working, in case anyone else stumbles along here and wonders what I did you can check it out here:  http://jsfiddle.net/WebGeorge/3Dmmz/

Basically I've got a very simple Google Spreadsheet with 3 columns

Description   Value   Color

The code then creates column chart using the color column to actually create the color of the column.  I also have several chart options set-up that would be easy for someone else to change.

So if you like it - all you really need to do is create your own Google Spreadsheet and then replace it the reference to my spreadsheet with your own.

- oh and it works just simply pasting the code into the embedded HTML container in Weebly - the other code I found wasn't working when I did that.

Cheers