NumberRangeFilter Control Google Spreadsheet integration

186 views
Skip to first unread message

David Rodger

unread,
Jun 15, 2011, 6:23:42 AM6/15/11
to Google Visualization API
Hi there

Is it possible to lookup values from a google spreadsheet rather than
specify the contents in the code for a NumberRangeFilter Control
visualization? (prepare the data part) :

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/
>
<title>
Google Visualization API Sample
</title>
<script type="text/javascript" src="http://www.google.com/jsapi"></
script>
<script type="text/javascript">
google.load('visualization', '1.1', {packages: ['controls']});
</script>
<script type="text/javascript">
function drawVisualization() {
// Prepare the data
var data = google.visualization.arrayToDataTable([
['Name', 'Age'],
['Michael' , 12],
['Elisa', 20],
['Robert', 7],
['John', 54],
['Jessica', 22],
['Aaron', 3],
['Margareth', 42],
['Miranda', 33]
]);

// Define a NumberRangeFilter slider control for the 'Age'
column.
var slider = new google.visualization.ControlWrapper({
'controlType': 'NumberRangeFilter',
'containerId': 'control1',
'options': {
'filterColumnLabel': 'Age',
'minValue': 0,
'maxValue': 60
}
});

// Define a bar chart
var barChart = new google.visualization.ChartWrapper({
'chartType': 'BarChart',
'containerId': 'chart1',
'options': {
'width': 400,
'height': 300,
'hAxis': {'minValue': 0, 'maxValue': 60},
'chartArea': {top: 0, right: 0, bottom: 0}
}
});

// Create the dashboard.
var dashboard = new
google.visualization.Dashboard(document.getElementById('dashboard')).
// Configure the slider to affect the bar chart
bind(slider, barChart).
// Draw the dashboard
draw(data);
}

google.setOnLoadCallback(drawVisualization);
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<div id="dashboard">
<table>
<tr style='vertical-align: top'>
<td style='width: 300px; font-size: 0.9em;'>
<div id="control1"></div>
<div id="control2"></div>
<div id="control3"></div>
</td>
<td style='width: 600px'>
<div style="float: left;" id="chart1"></div>
<div style="float: left;" id="chart2"></div>
<div style="float: left;" id="chart3"></div>
</td>
</tr>
</table>
</div>
</body>
</html>

Any help at all is very much appreciated.

Thanks for your time and effort

Riccardo Govoni

unread,
Jun 15, 2011, 8:13:39 AM6/15/11
to google-visua...@googlegroups.com
It is certainly possible.

Just replace the code that assembles the datatable locally ('prepare the data' section) with a google.visualization.Query directed toward the spreadsheet containing the data you want to visualize. You can then use the rest of the example you posted with the datatable returned in the query response.

An example about issuing queries to spreadsheets:
( in your case, you'll have to plug the ControlWrapper and ChartWrapper creation after the 'response.getDataTable()' line)

Documentation about issuing queries:

and instructions on how to extract the query URL from your spreadsheet (which you will then feed to google.visualization.Query):

Hope this helps,
/R.

David Rodger

unread,
Jun 15, 2011, 10:46:06 AM6/15/11
to Google Visualization API
Hi Riccardo

Thank you for your assistance, my code has now developed to the below,
although I am getting the following error("missing } after property
list (retrieve_cache?
unique_id=5f67dda29e2f553b3d92717ed7ad7cc0266aa2e4,78)") in the code
playground. Although I think my code maybe a little messy, learning
this stuff very fast.

Thanks for your time


function drawVisualization() {
// Prepare the data
var query = new google.visualization.Query(
'THIS IS MY GOOGLE SPREADSHEET URL');

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

var slider = new google.visualization.ControlWrapper({
'controlType': 'NumberRangeFilter',
'containerId': 'control1',
'options': {
'filterColumnLabel': 'Rate',
'minValue': 0,
'maxValue': 60
}
});

// Define a bar chart
var barChart = new google.visualization.ChartWrapper({
'chartType': 'BarChart',
'containerId': 'chart1',
'options': {
'width': 400,
'height': 300,
'hAxis': {'minValue': 0, 'maxValue': 60},
'chartArea': {top: 0, right: 0, bottom: 0}
}

function initialize() {
var opts = {sendMethod: 'xhr'};
var dashboard = new google.visualization.Dashboard('THIS IS MY
GOOGLE SPREADSHEET URL',bind(slider,barChart),draw(data));
var ControlWrapper = new google.visualization.ControlWrapper('THIS
IS MY GOOGLE SPREADSHEET URL');
var ChartWrapper = new google.visualization.ChartWrapper('THIS IS MY
GOOGLE SPREADSHEET URL');

});
google.setOnLoadCallback(drawVisualization);


On Jun 15, 1:13 pm, Riccardo Govoni <battleho...@gmail.com> wrote:
> It is certainly possible.
>
> Just replace the code that assembles the datatable locally ('prepare the
> data' section) with a google.visualization.Query directed toward the
> spreadsheet containing the data you want to visualize. You can then use the
> rest of the example you posted with the datatable returned in the query
> response.
>
> An example about issuing queries to spreadsheets:http://code.google.com/apis/ajax/playground/?type=visualization#data_...

Riccardo Govoni

unread,
Jun 15, 2011, 11:38:45 AM6/15/11
to google-visua...@googlegroups.com
There is some confusion in your script. The sequence of events should be:

google.setOnLoadCallback(drawVisualization);

function drawVisualization() {
  // Visualization API is loaded, fetch data from spreadsheet.
  var query = new google.visualization.Query( 
    'THIS IS MY GOOGLE SPREADSHEET URL'); 

  query.send(handleQueryResponse); 
}

function handleQueryResponse(response) {
  if (response.isError()) {
    // handle Error
  }
  // Fetch was successful, extract datatable
  var data = response.getDataTable();

  // Define the controls you need
  var slider = new google.visualization.ControlWrapper({
    // ...
  });

  var barChart = new google.visualization.ChartWrapper({
    // ...
  }

  // Define the dashboard and give it the data you retrieved from the spreadsheet.
  new google.visualization.Dashboard(document.getElementById('dashboard')).
    bind(slider, barChart).
    draw(data);
}

As you see, you specify your spreadsheet url only once, fetch the data from it via Query and then proceed as in the simple dashboard example in assembling the dashboard.

/R.

d d

unread,
Jun 15, 2011, 7:31:37 PM6/15/11
to google-visua...@googlegroups.com
thx

2011/6/15 Riccardo Govoni <battl...@gmail.com>

/R.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/abjY_rL-cScJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

David Rodger

unread,
Jun 16, 2011, 7:01:02 AM6/16/11
to Google Visualization API
Thanks again Ricardo, my code has now developed after what you have
suggested, but i still seem to get an error response, apologies for
bothering you with this again,

Do I have to specify the columns to base it on.

thanks again
:

<!--
You are free to copy and use this sample in accordance with the terms
of the
Apache license (http://www.apache.org/licenses/LICENSE-2.0.html)
-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/
>
<title>
Google Visualization API Sample
</title>
<script type="text/javascript" src="http://www.google.com/jsapi"></
script>
<script type="text/javascript">
google.load('visualization', '1.1', {packages: ['controls']});
</script>
<script type="text/javascript">
google.setOnLoadCallback(drawVisualization);

function drawVisualization() {
// Visualization API is loaded, fetch data from spreadsheet.
var query = new google.visualization.Query('THIS IS THE
SPREADSHEET URL');

query.send(handleQueryResponse);

}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}
// Fetch was successful, extract datatable
var data = response.getDataTable();
// Define the controls you need
var slider = new google.visualization.ControlWrapper({
'controlType': 'NumberRangeFilter',
'containerId': 'control1',
'options': {
'filterColumnLabel': 'Rate',
'minValue': 0,
'maxValue': 60
}
});

var barChart = new google.visualization.ChartWrapper({
'chartType': 'BarChart',
'containerId': 'chart1',
'options': {
'width': 400,
'height': 300,
'hAxis': {'minValue': 0, 'maxValue': 60},
'chartArea': {top: 0, right: 0, bottom: 0}
}
});



// Define the dashboard and give it the data you retrieved from the
spreadsheet.
new
google.visualization.Dashboard(document.getElementById('dashboard')).
bind(slider, barChart).
draw(data);

}
</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<div id="dashboard">
<table>
<tr style='vertical-align: top'>
<td style='width: 300px; font-size: 0.9em;'>
<div id="control1"></div>
<div id="control2"></div>
<div id="control3"></div>
</td>
<td style='width: 600px'>
<div style="float: left;" id="chart1"></div>
<div style="float: left;" id="chart2"></div>
<div style="float: left;" id="chart3"></div>
</td>
</tr>
</table>
</div>
</body>
</html>

On Jun 16, 12:31 am, d d <aux03s...@gmail.com> wrote:
> thx
>
> 2011/6/15 Riccardo Govoni <battleho...@gmail.com>
>
>
>
>
>
>
>
> > There is some confusion in your script. The sequence of events should be:
>
> > google.setOnLoadCallback(drawVisualization);
>
> > function drawVisualization() {
> >   // Visualization API is loaded, fetch data from spreadsheet.
> >   var query = new google.visualization.Query(
> >     'THIS IS MY GOOGLE SPREADSHEET URL');
>
> >   query.send(handleQueryResponse);
> > }
>
> > function handleQueryResponse(response) {
> >   if (response.isError()) {
> >     // handle Error
> >   }
> >   // Fetch was successful, extract datatable
> >   var data = response.getDataTable();
>
> >   // Define the controls you need
> >   var slider = new google.visualization.ControlWrapper({
> >     // ...
> >   });
>
> >   var barChart = new google.visualization.ChartWrapper({
> >     // ...
> >   }
>
> >   // Define the dashboard and give it the data you retrieved from the
> > spreadsheet.
> >   new google.visualization.Dashboard(document.getElementById('dashboard')).
> >     bind(slider, barChart).
> >     draw(data);
> > }
>
> > As you see, you specify your spreadsheet url only once, fetch the data from
> > it via Query and then proceed as in the simple dashboard example<http://code.google.com/apis/ajax/playground/?type=visualization#simpl...>in assembling the dashboard.

Riccardo Govoni

unread,
Jun 20, 2011, 7:04:49 AM6/20/11
to google-visua...@googlegroups.com
Sorry for the late answer. Your code seems fine. Be sure to use the correct spreadsheet url for the Query object.

Try following these instructions on how to extract a datasource url from a spreadsheet:

Your query url should look like:

Also be sure that the 'filterColumnLabel' of your NumberRangeFilter matches the column label in your spreadsheet.

I have put together this example to demonstrate an equivalent case:

/R.
Reply all
Reply to author
Forward
0 new messages