Thursday 31 July 2014

Create a Motion Chart gadget using Google Sheets

***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)

4 comments:

  1. Hi James, this is Alex. It is nice to say hello to you again after several months. I have a question for you. Can you tell why is it that I can not see the motion chart with your own spreadsheet when I copy the page code to a proof web page?

    Thanks in advance.

    Best regards,

    Alex.

    ReplyDelete
    Replies
    1. Hi Alex - Nice to hear from you again. I am afraid it is not good news on this one. Google announced earlier this year they were phasing out Motion charts. As of September 2017 it is not possible to create any new ones, and any existing ones will also stop working. https://gsuiteupdates.googleblog.com/2017/06/charts-in-sheets-updates_1.html

      All the best

      James

      Delete
  2. Hi James. Thank so much for your quick response. That explains the reason why I can´t see the flash rendered motion chart. So, are we finally arriving to the last days of Flash Player? Well, I guess it would be better to wait for the native HTML5 render for interactive motion charts. Don´t you think? Any suggestion about this?

    Best regards,

    Alex.

    ReplyDelete
    Replies
    1. Hey Alex - agree with you, waiting for the HTML5 version would probably be the best bet. Am slightly concerned that Google are suggesting using Bubble charts instead, as they really don't have the same functionality..! I have had a look around since it was first mooted that this flash based chart would be retired at some point, but have not found a good alternative. If I do find anything Ill update the blog and give you a heads-up.

      All the best,

      James

      Delete