***Update September 2017 - Google has now discontinued the motion chart, so the below solution will no longer work***
In Google Sheets, you have the option to simply create a chart and display this on your Google Site. However, if you code this in a gadget, you have lots more potential to customise things. In this example I am using Google Sheet data for a motion chart, and customising the initial state that will display on my site. A quick demo is below
I found that motion charts do not load when the data range includes blank rows. For the data I am using which gets updated automatically each week using the method I blogged about here, the data range will grow over time, which causes a problem. The solution to this is to use the QUERY function in Google sheets to bring in only the data in an IMPORTRANGE that contains valid data. This means you can specify a large range for the importrange to cover, but wont bring in any blank rows.
In the sheet that I want the motion chart to read from, I therefore applied the following formula,
=query(importrange("https://docs.google.com/a/nihr.ac.uk/spreadsheets/d/abcdefghijklmnopqrs1234567891011", "motion!Q2:S580"),"Select Col1, Col2, Col3 where Col3 > 0")
This extracts the data from my other Google sheet for the data I want to chart, only where the third column in the range (the values) are greater than zero.
In my Google Sheet, I then went to File -> publish to the web, to make this data available for the gadget to read.
In the final chart I wanted to set the initial chart state as columns, with our network highlighted, and with unique colours for each network. Once I applied these settings on the chart, by going to the advanced settings as per the below screenshot, it was possible to get the code for that state to then use in the gadget code so that when the chart loads, it will display exactly in this format.
Here's the completed gadget, with the live chart here:
<?xml version="1.0" encoding="UTF-8" ?>
<Module>
<ModulePrefs title="Recruitment by LRN FY2014/5" width="600" height="500" scrolling="false">
<Require feature="ads"/>
<Require feature="flash"/>
</ModulePrefs>
<UserPref name="clickurl" datatype="hidden" default_value="DEBUG"/>
<UserPref name="aiturl" datatype="hidden" default_value="DEBUG"/>
<Content type="html"><![CDATA[
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {'packages':['motionchart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var query = new google.visualization.Query(
'http://spreadsheets.google.com/tq?key=*****insert your key here******&pub=1');
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var chart = new google.visualization.MotionChart(document.getElementById('motionchart'));
var options = {};
options['state'] =
'{"yZoomedDataMax":20000,"yLambda":1,"iconType":"VBAR","xAxisOption":"2","yZoomedDataMin":0,"dimensions":{"iconDimensions":["dim0"]},"iconKeySettings":[{"key":{"dim0":"NIHR CRN: Kent, Surrey and Sussex"}}],"nonSelectedAlpha":0.4,"orderedByX":true,"playDuration":15000,"showTrails":false,"orderedByY":false,"yZoomedIn":false,"xZoomedDataMin":0,"colorOption":"_UNIQUE_COLOR","time":"2014-04-01","xZoomedIn":false,"yAxisOption":"2","sizeOption":"_UNISIZE","xZoomedDataMax":15,"xLambda":1,"uniColorForNonSelected":false,"duration":{"timeUnit":"D","multiplier":1}};';
options['width'] = 600;
options['height'] = 500;
chart.draw(data, options);
}
</script>
</head>
<body>
<span id='motionchart'></span>
</body>
]]></Content>
</Module>
(For details on how to upload a gadget and get the url to use on a Google site, see my blogpost here)
