Re: Creating Pie charts in Dashboard using Google Spreadsheets

asgallant

unread,

Oct 18, 2012, 7:15:29 PM10/18/12

to google-visua...@googlegroups.com

Making the pie chart work on the Percentage of Total Area column is fairly simple. Set the pie chart's view.columns option to an array of the column indices you want to use (in this case, 0 and 3). With your filter, though, that would make a pie with a single value, which generally isn't very useful. Here's a working example using your code: http://jsfiddle.net/asgallant/ttDGz/

On Thursday, October 18, 2012 5:45:40 PM UTC-4, Qasim Rasouli wrote:

Hi everyone,

I'm trying to create my first dashboard using my google spreadsheets. I'm not too much familiar with google visualization API:
I create this:
<html><head>
<title>Renewable Energy Projects</title>


<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

// Load the Visualization API and the controls package.
// Packages for all the other charts you need will be loaded
// automatically by the system.
google.load('visualization', '1.1', {'packages':['controls','linechart']});

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(initialize);

function initialize() {
// Replace the data source URL on next line with your data source URL.
var query = new google.visualization.Query('http://spreadsheets.google.com/pub?key=0AvGcVM8k_RwUdExOclVzaEVDSlVvSTlEU2UxdTJOdFE&gid=11');


// Send the query with a callback function.
query.send(drawDashboard);
}

function drawDashboard(response) {
var data = response.getDataTable();
// Everything is loaded. Assemble your dashboard...
var namePicker = new google.visualization.ControlWrapper({
'controlType': 'CategoryFilter',
'containerId': 'filter_div',
'options': {
'filterColumnLabel': 'Province',
'ui': {
'labelStacking': 'horizontal',
'allowTyping': false,
'allowMultiple': false
}
}
});

var pieChart = new google.visualization.ChartWrapper({
'chartType': 'PieChart',
'containerId': 'chart_div',
'options': {
'width': 300,
'height': 300,
'pieSliceText': 'value',
'legend': 'right'
}
});

var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div')).
bind(namePicker, pieChart).
draw(data)

}
</script>

<style type="text/css">
.warpper {
width: 600px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
margin:0px auto;
padding: 10px;
background-color: #EEE;
}
.header {
height: 150px;
}
#filter_div {
height: 20px;
border-bottom: 1px #FFFFFF solid;
border-top: 1px #FFFFFF solid;
margin-bottom: 10px;
padding: 10px 5px 5px 5px;
}
#chart_div {
width: 190px;
}
</style>
</head>
<body>
<div class="warpper">
<div class="header">
<div class="qasim">
</div>
</div>

<div id="dashboard_div">

<div id="filter_div"></div>
<div id="chart_div"></div>
</div>
</div>
<body>
</html>

I have a filter that filtering using Province column in my sheet (filter is working). So, I need to make a pie chart from Percentage of Total Area (%) column that to show a percentage of each province area. So I need a pie chart to show the percentage of each province and total percentage that is the sum of the column.
Can anyone help me?
Thanks in advance.

Qasim Rasouli

unread,

Oct 18, 2012, 10:26:54 PM10/18/12

to google-visua...@googlegroups.com

Thanks so much. I tried myself a lot but didn't find this. :D

Two more questions:

1- How can I have another value to complete my province value to become 100%. I mean having two values for my pie chart. One is the province value and another for showing total remaining percentage?

2- Can I use several pie charts (for example 5) inside one dashboard?

Thanks agin for your help.

asgallant

unread,

Oct 19, 2012, 1:19:30 AM10/19/12

to google-visua...@googlegroups.com

The answer to your first question is rather complicated, so I'll start with the second: yes, you can have multiple pie charts. You just need to create chart objects for each one and have a separate container div for each one.

To the first question, this is possible, but requires a bit of hackery to make it work. Basically, you have to set things up so that you get the value from the control, and then use that to group the data from the DataTable, then draw the pie based on the grouped data. This doesn't really work in the context of a Dashboard; the control can be a part of one, controlling other charts, but the pie cannot be bound to the same dashboard (the pie could be part of a second dashboard driven by the grouped data, but that's a bit beyond this discussion - for now, just assume the pie cannot be bound to a dashboard). Here's an example doing it without the dashboard: http://jsfiddle.net/asgallant/ttDGz/5/

Qasim Rasouli

unread,

Oct 19, 2012, 12:47:00 PM10/19/12

to google-visua...@googlegroups.com

Thanks. I think it's better to remove PieChart and instead of that I used ColumnChart.

Now I want to use several ColumnCharts and also some tables in my dashboard but only CloumnChart rendering and there's no Table? Here's my Code:

<title>Renewable Energy Projects</title>

// Load the Visualization API and the controls package.

// Packages for all the other charts you need will be loaded

// automatically by the system.

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

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(initialize);

function initialize() {

// Replace the data source URL on next line with your data source URL.

var query = new google.visualization.Query('http://spreadsheets.google.com/pub?key=0AvGcVM8k_RwUdExOclVzaEVDSlVvSTlEU2UxdTJOdFE&gid=11');

// Send the query with a callback function.

query.send(drawDashboard);

}

function drawDashboard(response) {

var data = response.getDataTable();

// Everything is loaded. Assemble your dashboard...

var namePicker = new google.visualization.ControlWrapper({

'controlType': 'CategoryFilter',

'containerId': 'filter_div',

'options': {

'filterColumnLabel': 'Province',

'ui': {

'labelStacking': 'horizontal',

'allowTyping': false,

'allowMultiple': false

}

},

'state': {'selectedValues': ['Kabul']}

});

var table1 = new google.visualization.ChartWrapper({

chartType: 'Table',

containerId: 'table1',

option: {

width: '920px',

height: 53

},

view: {columns: [5, 6, 7, 8, 9, 10] }

});

var myChart = new google.visualization.ChartWrapper({

chartType: 'ColumnChart',

containerId: 'chart_div',

options: {

colors:['#29abe2','#fcee21', '#8cc63f'],

legend: {position: 'bottom', textStyle: {color: '#666', fontSize: 10}},

chartArea:{left:80,top:10,width:"100%",height:"50%"},

},

view: {columns: [0, 5, 7, 9]},

});

new google.visualization.Dashboard(document.getElementById('dashboard')).

bind(namePicker, myChart, table1).

draw(data)

}

</script>

.warpper {

width: 600px;

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

margin:0px auto;

padding: 10px;

background-color: #FFF;

}

.header {

height: 150px;

}

#filter_div {

height: 20px;

border-bottom: 1px #EEE solid;

border-top: 1px #EEE solid;

margin-bottom: 10px;

padding: 10px 5px 5px 5px;

}

#chart_div {

width: 600px;

}

</style>

</head>

<body>

</div>

<h4>Project Completed (Potential [kW])</h4>

</div>

</div>

<body>

</html>

Can you please help me again?

asgallant

unread,

Oct 19, 2012, 1:08:11 PM10/19/12

to google-visua...@googlegroups.com

There are two problems:

1) you use "table1" for the containerId in the table, but the div has the id "table2"

2) you need to pass an array to the bind method when binding multiple charts:

new google.visualization.Dashboard(document.getElementById('dashboard')).
bind([namePicker], [myChart, table1]).

draw(data);

Qasim Rasouli

unread,

Oct 19, 2012, 1:58:37 PM10/19/12

to google-visua...@googlegroups.com

Thanks. I can successfully make another columnchart but the table show this error: [object HTMLDivElement] does not fit either the Control or Visualization specification.

I remove the wrapper and also reduce the columns but still not showing the table?!

asgallant

unread,

Oct 19, 2012, 4:51:50 PM10/19/12

to google-visua...@googlegroups.com

That's a new one. Post your code and I'll take a look.

Qasim Rasouli

unread,

Oct 19, 2012, 5:07:21 PM10/19/12

to google-visua...@googlegroups.com

here's the code:

var table = new google.visualization.ChartWrapper({

'chartType': 'Table',

'containerId': 'table1',

'options': {

'width': '500px',

},

'view': {'columns': [0, 5] ,'rows': [0] }

});

var myChart = new google.visualization.ChartWrapper({

chartType: 'ColumnChart',

containerId: 'chart_div',

options: {

colors:['#29abe2','#fcee21', '#8cc63f'],

legend: {position: 'bottom', textStyle: {color: '#666', fontSize: 10}},

chartArea:{left:60,top:40,width:"70%",height:"50%"},

},

view: {columns: [0, 5, 7, 9]},

});

var mysecondChart = new google.visualization.ChartWrapper({

chartType: 'ColumnChart',

containerId: 'chartsecond_div',

options: {

colors:['#29abe2','#fcee21', '#8cc63f'],

legend: {position: 'bottom', textStyle: {color: '#666', fontSize: 10}},

chartArea:{left:60,top:40,width:"70%",height:"50%"},

},

view: {columns: [0, 11, 13, 15]},

});

new google.visualization.Dashboard(document.getElementById('dashboard')).

bind([namePicker], [myChart, table1, mysecondChart]).

draw(data)

}

</script>

.warpper {

width: 600px;

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

margin:0px auto;

padding: 10px;

background-color: #FFF;

}

.header {

height: 150px;

}

#firstrow {

width: 600px;

display: table;

background-color: #FAFAFA;

padding: 5px;

}

#secondrow {

width: 600px;

display: table;

background-color: #FAFAFA;

padding: 5px;

margin-top: 10px;

}

#filter_div {

height: 20px;

border-bottom: 1px #EEE solid;

border-top: 1px #EEE solid;

margin-bottom: 10px;

padding: 10px 5px 5px 5px;

}

#chart_div {

background-color: #FAFAFA;

width: 290px;

border: 1px #ccc solid;

float: left;

position: relative;

}

#table1 {

background-color: #eeeeee;

width: 290px;

border: 1px #ccc solid;

float: right;

margin-left: 10px;

}

#chartsecond_div {

width: 290px;

border: 1px #ccc solid;

float: left;

position: relative;

}

#table2 {

background-color: #eeeeee;

width: 290px;

border: 1px #ccc solid;

float: right;

margin-left: 10px;

}

h1 {

font-size: 26px;

font-weight: bold;

color: #999;

margin-bottom: 20px;

text-shadow: 2px 2px 2px #ccc;

}

h2 {

font-size: 20px;

}

h3 {

font-size: 18px;

}

h4 {

font-size: 14px;

font-weight: bold;

}

h5 {

font-size: 14px;

font-weight: normal;

}

h6 {

font-size: 12px;

}

</style>

</head>

<body>

<h1>Afghanistan Renewable Energy Projects<h1>

</div>

<h4>Project Completed (Potential [kW])</h4>

</div>

<h4>Projects Under Construction (Potential [kW])</h4>

</div>

</body>

<body>

</html>

asgallant

unread,

Oct 19, 2012, 6:40:26 PM10/19/12

to google-visua...@googlegroups.com

It's just a typo; you pass "table1" to the bind call, but your table is in the variable "table", so this:

bind([namePicker], [myChart, table1, mysecondChart]).

should be this:

bind([namePicker], [myChart, table, mysecondChart]).

Qasim Rasouli

unread,

Oct 20, 2012, 5:12:29 AM10/20/12

to google-visua...@googlegroups.com

Wow. I just read whole codes more than 50 times and can't understand that. Thanks millions times. You are really helpful.

asgallant

unread,

Oct 20, 2012, 10:28:55 AM10/20/12

to google-visua...@googlegroups.com

You're welcome.

Qasim Rasouli

unread,

Oct 22, 2012, 10:02:34 PM10/22/12

to google-visua...@googlegroups.com

I almost finished my project but there's small problem. I'm using three pie chart and I need each to query specific column from my sheet but they all just query from my first and second column in that sheet. here's my code:

// Load the Visualization API and the controls package.

// Packages for all the other charts you need will be loaded

// automatically by the system.

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

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(initialize);

function initialize() {

// Replace the data source URL on next line with your data source URL.

var query = new google.visualization.Query('https://docs.google.com/spreadsheet/tq?range=A%3AX&key=0AvGcVM8k_RwUdExOclVzaEVDSlVvSTlEU2UxdTJOdFE&gid=12&headers=-1');

// Send the query with a callback function.

query.send(drawDashboard);

}

function drawDashboard(response) {

var data = response.getDataTable();

// Everything is loaded. Assemble your dashboard...

var pie1 = new google.visualization.PieChart(document.getElementById('pie1'));

pie1.draw(data, {

backgroundColor: 'whiteSmoke',

width: 280,

height: 200,

legend: 'none',

colors:['#29abe2','#81d5f2','#d3e9f2'],

chartArea:{left:20,top:10,width:"90%",height:"90%"},

view: {

columns: [4, 5]

}

});

var pie2 = new google.visualization.PieChart(document.getElementById('pie2'));

pie2.draw(data, {

backgroundColor: 'whiteSmoke',

width: 280,

height: 200,

legend: 'bottom',

colors:['#f9e208','#f2e77c','#f7f6eb'],

chartArea:{left:20,top:10,width:"90%",height:"90%"},

view: {

columns: [8, 9]

}

});

var pie3 = new google.visualization.PieChart(document.getElementById('pie3'));

pie3.draw(data, {

backgroundColor: 'whiteSmoke',

width: 280,

height: 200,

legend: 'none',

colors:['#8cc63f','#caef8f','#eff9e1'],

chartArea:{left:20,top:10,width:"90%",height:"90%"},

view: {

columns: [12, 13]

}

});

var element = document.getElementById('foo');

element.innerHTML = data.getValue(0, 1);

var element = document.getElementById('foo1');

element.innerHTML = data.getValue(1, 1);

var element = document.getElementById('foo2');

element.innerHTML = data.getValue(2, 1);

var element = document.getElementById('foo3');

element.innerHTML = data.getValue(0, 3);

var element = document.getElementById('foo4');

element.innerHTML = data.getValue(1, 3);

var element = document.getElementById('foo5');

element.innerHTML = data.getValue(2, 3);

}

</script>

asgallant

unread,

Oct 23, 2012, 12:56:56 AM10/23/12

to google-visua...@googlegroups.com

There are two ways to handle this:

1) create 3 separate queries, one for each chart. This is generally inefficient but sometimes necessary.

2) if you can query all relevant columns in a single query, then use DataViews to separate out the columns needed for each pie, eg:

var view1 = new google.visualization.DataView(data);

//use columns 0 and 1 for the first pie chart

view1.setColumns([0, 1]);

pie1.draw(view1, {/*options*/});

Qasim Rasouli

unread,

Oct 23, 2012, 7:53:19 PM10/23/12

to google-visua...@googlegroups.com

Works like a charm. Thanks.

asgallant

unread,

Oct 24, 2012, 1:17:57 AM10/24/12

to google-visua...@googlegroups.com

You're welcome.

Reply all

Reply to author

Forward