Looking for an easy and free way to visualize data to gain insight? Google has provided a JavaScript programming interface so that you can integrate Google Charts in your applications. To my surprise, I found it is very easy to build interactive Google Charts that use data from Google Sheets too.
Using our sense of sight, we can naturally find patterns and outliers in data quickly. Interactive data visualization tools are designed to turn your data into insightful pictures. If you’re not familiar with creating charts in Google Sheets, check out this brief video introduction by John Calvert.
To introduce the Google Charts programming interface, I wanted to share a Google Motion Chart showing trends of population and vehicle count over time in various countries. We will briefly introduce how we built this chart.
Your Data in JavaScript code
The following HTML/Javascript code implements a Google Motion Chart by describing the information in JavaScript code.
<script type="text/javascript">
google.load('visualization', '1', {'packages':['motionchart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Country');
data.addColumn('date', 'Date');
data.addColumn('number', 'MotorVehicles');
data.addColumn('number', 'Population');
data.addRows([
['Africa', new Date(2011,1,1), 31193692, 962141891],
['Asia, Far East', new Date(2011,1,1), 265431331, 3722912359],
['Asia, Middle East', new Date(2011,1,1), 36077342, 302337690],
['Brazil', new Date(2011,1,1), 34654969, 197595498],
['Canada', new Date(2011,1,1), 21311000, 34030589],
['Central & South America', new Date(2011,1,1), 100492455, 589210431],
['China', new Date(2011,1,1), 93500000, 1336718015],
['Europe, East', new Date(2011,1,1), 102093375, 305286548],
['Europe, West', new Date(2011,1,1), 244572580, 414579002],
['India', new Date(2011,1,1), 24114000, 1189172906],
['Indonesia', new Date(2011,1,1), 16856000, 246065161],
['Pacific', new Date(2011,1,1), 19335216, 34121250],
['United States', new Date(2011,1,1), 248931633, 311588000],
['Africa', new Date(2001,1,1), 18148190, 757466312],
['Asia, Far East', new Date(2001,1,1), 138569841, 3349626391],
['Asia, Middle East', new Date(2001,1,1), 21142467, 251492491],
['Brazil', new Date(2001,1,1), 19845000, 176789957],
['Canada', new Date(2001,1,1), 17783343, 31376736],
['Central & South America', new Date(2001,1,1), 57684841, 523923953],
['China', new Date(2001,1,1), 14537000, 1270744232],
['Europe, East', new Date(2001,1,1), 64486961, 314161172],
['Europe, West', new Date(2001,1,1), 218908120, 396373562],
['India', new Date(2001,1,1), 8413000, 1023295084],
['Indonesia', new Date(2001,1,1), 5585000, 217197016],
['Pacific', new Date(2001,1,1), 15278446, 29746148],
['United States', new Date(2001,1,1), 216682937, 284968955]
]);
var chart = new google.visualization.MotionChart(document.getElementById('chart_div'));
chart.draw(data, {width: 600, height:300});
}
</script>
The following code imports Google JavaScript code library. The “google.load” function loads the Google Motion Chart into memory.
<script type="text/javascript">
google.load('visualization', '1', {'packages':['motionchart']});
Next, we define a trigger or call back function to run “drawChart” after the browser is loaded.
Let’s draw the chart. All of this work is located in the “drawChart” function. This involves code to describe our data, code to load our data, and execute drawing. In this step, we describe the structure of our data. As you study this code, imagine a spread sheet with column names at the top with data flowing down. We create an instance of a data table. For each column of data, we provide a name and data type. It should be noted that a Google Motion Chart data source needs to be structured in the following order:
- Column 1: Entity name (i.e. country)
- Column 2: Time or date stamp.
- Column 3: Numeric data for X axis.
- Column 4: Numeric data for Y axis.
data.addColumn('string', 'Country');
data.addColumn('date', 'Date');
data.addColumn('number', 'MotorVehicles');
data.addColumn('number', 'Population');
Check out Google’s documentation
for complete description of Motion Chart data assumptions and conventions.
Using the “data.addRows” function, we add JavaScript arrays to describe the data row by row.
To complete the drawing process, we execute the following lines. The chart will be placed in a ‘DIV’ element called ‘chart_div.’ The “draw” function executes the chart drawing.
chart.draw(data, {width: 600, height:300});
Connect Your Google Sheet Data to Google Chart
As a programmer, you can link data from a Google Sheet to a Google Chart. The Google Sheet needs to be configured so that it’s public on the web. You can configure your sheet to be public using the “Share” button in the upper right hand corner of the Google Sheet. The following code links data from our Google Sheet to a Motion Chart.
<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('https://docs.google.com/spreadsheet/ccc?key=0AjD9EQef28wTdHozcEd1dDRhYjhOMHVHVWR1X19IOFE&usp=sharing');
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var chart2 = new google.visualization.MotionChart(document.getElementById('chart_div'));
chart2.draw(data, {width: 600, height:300});
}
</script>
Data from a Spreadsheet
In the “drawChart” function, we use the Query object to reference the URL of our Google Sheet. When the query is sent, we specify a call back function called “handleQueryResponse.” Once the data arrives, the “handleQueryResponse” function will complete the drawing.
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var chart2 = new google.visualization.MotionChart(document.getElementById(‘chart_div’));
chart2.draw(data, {width: 600, height:300});
}
If you’re interested in learning more about programming Google Charts, check out the following links:
Google Charts Playground: https://code.google.com/apis/ajax/playground/?type=visualization
Google Charts Quick start: https://developers.google.com/chart/interactive/docs/quick_start
Data visualization is a pretty fun area. We love to hear from our readers. What are some of your favorite tools visualizing data? Leave us a comment below!
0 Comments