Data sources for the Charts

Exclusive offer: get 50% off this eBook here
Google Visualization API Essentials

Google Visualization API Essentials — Save 50%

Make sense of your data: make it visual with the Google Visualization API book and ebook.

$26.99    $13.50
by Traci L. Ruthkoski | August 2013 | Web Development

In this article by Traci L. Ruthkoski, the author of the book, Google Visualization API Essentials , we will discuss data sources for Charts. The integrated nature of Spreadsheets and Fusion Tables as data sources make the coding of Charts with these external data sources for the Visualization API quite straightforward.

(For more resources related to this topic, see here.)

Spreadsheets

In Spreadsheets, two preparation steps must be addressed in order to use a Spreadsheet as a data source with the Visualization API. The first is to identify the URL location of the Spreadsheet file for the API code. The second step is to set appropriate access to the data held in the Spreadsheet file.

Preparation

The primary method of access for a Spreadsheet behaving as a data source is through a JavaScript-based URL query. The query itself is constructed with the Google Query Language. If the URL request does not include a query, all data source columns and rows are returned in their default order. To query a Spreadsheet also requires that the Spreadsheet fi le and the API application security settings are con figured appropriately. Proper preparation of a Spreadsheet as a data source involves both setting the appropriate access as well as locating the fi le's query URL.

Permissions

In order for a Spreadsheet to return data to the Visualization API properly, access settings on the Spreadsheets fi le itself must allow view access to users. For a Spreadsheet that allows for edits, including form-based additions, permissions must be set to Edit . To set permissions on the Spreadsheet, select the Share button to open up the Sharing settings dialog. To be sure the data is accessible to the Visualization API, access levels for both the Visualization application and Spreadsheet must be the same. For instance, if a user has access to the Visualization application and does not have view access to the Spreadsheet, the user will not be able to run the visualization as the data is more restrictive to that user than the application. The opposite scenario is true as well, but less likely to cause confusion as a user unable to access the API application is a fairly self-described problem.

All Google applications handle access and permissions similarly. More information on this topic can be found on the Google Apps Support pages.

Google Permissions overview is available at

 http://support.google. com/drive/bin/answer.py?hl=en&answer=2494886&rd=1.

Get the URL path

At present, acquiring a query-capable URL for a Spreadsheet is not as straightforward a task as one might think. There are several methods for which a URL is generated for sharing purposes, but the URL format needed for a data source query can only be found by creating a gadget in the Spreadsheet. A Google Gadget is simply dynamic, HTML or JavaScript-based web content that can be embedded in a web page. Google Gadgets also have their own API, and have capabilities beyond Spreadsheets applications.

Information on Google Gadget API is available at https://developers.google.com/gadgets/.

Initiate gadget creation by selecting the Gadget... option from the Insert item on the menu bar. When the Gadget Settings window appears, select Apply & close from the Gadget Settings dialog.

Choose any gadget from the selection window. The purpose of this procedure is simply to retrieve the correct URL for querying. In fact, deleting the gadget as soon as the URL is copied is completely acceptable. In other words, the specific gadget chosen is of no consequence.

Once the gadget has been created, select Get query data source url… from the newly created gadget's drop-down menu.

Next, determine and select the range of the Spreadsheet to query. Either the previously selected range when the gadget was created, or the entire sheet is acceptable, depending on the needs of the Visualization application being built. The URL listed under Paste this as a gadget data source url in the Table query data source window is the correct URL to use with the API code requiring query capabilities. Be sure to select the desired cell range, as the URL will change with various options.

Important note

Google Gadgets are to be retired in 2013, but the query URL is still part of the gadget object at the time of publication. Look for the method of finding the query URL to change as Gadgets are retired.

Query

Use the URL retrieved from the Spreadsheet Gadget to build the query. The following query statement is set to query the entire Spreadsheet of the key indicated:

var query =new google.visualization.Query

('https://docs.google.com/spreadsheet/tq?key =0AhnmGz1SteeGdEVsNlNWWkoxU
3ZRQjlmbDdTTjF2dHc&headers=-1');

Once the query is built, it can then be sent. Since an external data source is by definition not always under explicit control of the developer, a valid response to a query is not necessarily guaranteed. In order to prevent hard-to-detect data-related issues, it is best to include a method of handling erroneous returns from the data source. The following query.send function also informs the application how to handle information returned from the data source, regardless of quality.

query.send(handleQueryResponse);

The handleQueryResponse function sent along with the query acts as a filter, catching and handling errors from the data source. If an error was detected, the handleQueryResponse function displays an alert message. If the response from the data source is valid, the function proceeds and draws the visualization.

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.
getDetailedMessage());
return;
}
var data = response.getDataTable();
visualization = new google.visualization.Table
(documnt.getElementById('visualization'));
visualization.draw(data, null);
}

Best practice

Be prepared for potential errors by planning for how to handle them.

For reference, the previous example is given in its complete HTML form:

<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', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;
function drawVisualization() {
// To see the data that this visualization uses, browse to
//https://docs.google.com/spreadsheet/ccc?key=0AhnmGz1SteeGdEVsNlN
WWkoxU3ZRQjlmbDdTTjF2dHc&usp=sharing

var query = new google.visualization.Query

('https://docs.google.com/spreadsheet/tq?key=


0AhnmGz1SteeGdEVsNlNWWkoxU3ZRQjlmbDdTTjF2dHc&headers=-1');

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

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

var data = response.getDataTable();
visualization = new google.visualization.Table(document.getEleme
ntById('visualization'));
visualization.draw(data, null);
}

google.setOnLoadCallback(drawVisualization);

</script>
</head>
<body style="font-family: Arial;border: 0 none;">
<div id="visualization" style ="height: 400px; width: 400px;">
</div>
</body>
</html>

 

View live examples for Spreadsheets at
http://gvisapi-packt. appspot.com/ch6-examples/ch6-datasource.html

Apps Script method

Just as the Visualization API can be used from within an Apps Script, external data sources can also be requested from the script. In the Apps Script Spreadsheet example presented earlier in this article, the DataTable() creation was performed within the script. In the following example, the create data table element has been removed and a .setDataSourceUrloption has been added to Charts. newAreaChart(). The script otherwise remains the same.


functiondoGet() {
var chart = Charts.newAreaChart().setDataSourceUrl

("https: //docs.google.com/spreadsheet/tq
?key= 0AhnmGz1SteeGdEVsNlNWWkoxU3ZRQjlmbDdTTjF2dHc&headers=-1")
.setDimensions(600, 400)
.setXAxisTitle("Age Groups")
.setYAxisTitle("Population")
.setTitle("Chicago Population by Age and Gender - 2010 Census")
.build();
varui = UiApp.createApplication();
ui.add(chart);
returnui;
}

View live examples in Apps Script at https://script. google.com/d/1Q2R72rGBnqPsgtOxUUME5zZy5Kul5 3r_lHIM2qaE45vZcTlFNXhTDqrr/edit.

Fusion Tables

Fusion Tables are another viable data source ready for use by Visualization API. Fusion Tables offer benefit over Spreadsheets beyond just the Google Map functionality. Tables API also allows for easier data source modification than is available in Spreadsheets.

Preparation

Preparing a Fusion Table to be used as a source is similar in procedure to preparing a Spreadsheet as a data source. The Fusion Table must be shared to the intended audience, and a unique identifier must be gathered from the Fusion Tables application.

Permissions

Just as with Spreadsheets, Fusion Tables must allow a user a minimum of view permissions in order for an application using the Visualization API to work properly. From the Sharing settings window in Fusion Tables, give the appropriate users viewaccess as a minimum.

Get the URL path

Referencing a Fusion Table is very similar in method to Spreadsheets. Luckily, the appropriate URL ID information is slightly easier to find in Fusion Tables than in Spreadsheets. With the Sharing settings window open, there is a field at the top of the page containing the Link to share . At the end portion of the link, following the characters dcid= is the Table's ID. The ID will look something like the following:

1Olo92KwNin8wB4PK_dBDS9eghe80_4kjMzOTSu0

This ID is the unique identifier for the table.

Query

Google Fusion Tables API includes SQL-like queries for the modification of Fusion Tables data from outside the GUI interface. Queries take the form of HTTP POST and GET requests and are constructed using the Fusion Tables API query capabilities. Data manipulation using Fusion Tables API is beyond the scope of this article, but a simple example is offered here as a basic illustration of functionality. Fusion Table query requests the use of the API SELECT option, formatted as:

SELECT Column_name FROM Table_ID

Here Column_name is the name of the Fusion Table column and Table_ID is the table's ID extracted from the Sharing settings window.

If the SELECT call is successful, the requested information is returned to the application in the JSON format. The Visualization API drawChart() is able to take the SELECT statement and the corresponding data source URL as options for the chart rendering. The male and female data from the Fusion Tables 2010 Chicago Census file have been visualized using the drawChart() technique.


function drawVisualization() {
google.visualization.drawChart({
containerId: 'visualization',
dataSourceUrl:

'http://www.google.com/fusiontables/gvizdata?tq=',
query: 'SELECT Age, Male, Female FROM 1Olo92KwNin8wB4PK_
dBDS9eghe80_4kjMzOTSu0',
chartType: 'AreaChart',
options: {
title: 'Chicago Population by Age and Sex - 2010 Census',
vAxis: {
title: 'Population'
},
hAxis: {
title: 'Age Groups'
}
}
});
}

The preceding code results in the following visualization:

Live examples are available at http://gvisapi-packt. appspot.com/ch6-examples/ch6-queryfusion.html.

Important note

Fusion Table query responses are limited to 500 rows. See Fusion Tables API documentation for other resource parameters.

API Explorer

With so many APIs available to developers using the Google platform, testing individual API functionality can be time consuming. The same issue arises for GUI applications used as a data source. Fortunately, Google provides API methods for its graphical applications as well. The ability to test API requests against Google's infrastructure is a desirable practice for all API programing efforts. To support this need, Google maintains the APIs Explorer service. This service is a console-based, web application that allows queries to be submitted to APIs directly, without an application to frame them.

This is helpful functionality when attempting to verify whether a data source is properly configured. To check if the Fusion Tables 2010 U.S. Census data instance is configured properly, a query can be sent to list all columns, which informs which columns are actually exposed to the Visualization API application.

Best practice

Use the Google API Explorer service to test if API queries work as intended.

To use the API Explorer for Fusion Tables, select Fusion Tables API from the list of API services. API functions available for testing are listed on the Fusion Tables API page. Troubleshooting a Chart with a Fusion Tables data source usually involves fi rst verifying all columns are available to the visualization code. If a column is not available, or is not formatted as expected, a visualization issue related to data problems may be difficult to troubleshoot from inside the Visualization API environment. The API call that best performs a simple check on column information is the fusiontables.column.list item.

Selecting fusiontables.column.list opens up a form-based interface. The only required information is the Table ID (collected from the Share settings window in the Fusion Tables file). Click on the Execute button to run the query.

The API Explorer tool will then show the GET query sent to the Fusion Table in addition to the results it returned. For the fusiontables.column.list query, columns are returned in bracketed sections. Each section contains attributes of that column. The following queried attributes should look familiar, as it is the fusiontables.column.list result of a query to the 2010 Chicago Census data Fusion Table.

Best Practice

The Column List Tool is helpful when troubleshooting Fusion Table to API code connectivity. If the Table is able to return coherent values through the tool, it can generally be assumed that access settings are appropriate and the code itself may be the source of connection issues.

Fusion Tables—row and query reference is available at https:// developers.google.com/fusiontables/docs/v1/sqlreference.

Information on API Explorer—column list is available at

https:// developers.google.com/fusiontables/docs/v1/ reference/column/list#try-it.

Google Visualization API Essentials Make sense of your data: make it visual with the Google Visualization API book and ebook.
Published: April 2013
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Chart Tools Query Language

The Visualization API query language (Chart Tools Query Language) is intended to provide developers with a method for mapping data from an external source to the various visualization data configuration requirements.

Just as with Spreadsheets and Fusion Tables data queries, the Chart Tools Query Language follows a SQL-like structure. An alternative to the URL query method is to set the query from within JavaScript, but the more common SQL method is represented by the following example. Set the query in JavaScript as follows:

var query = new google.visualization.Query(DATA_SOURCE_URL);
query.setQuery('select D where B > 50000);
query.send(handleQueryResponse);

The preceding query selects the item in column D only if the value in column B is greater than 50000. The same query can also be sent as part of the data source URL. There are four segments to a complete query formed as a URL. In the URL query:

This results in the following query URL:

http://spreadsheets.google.com/a/google.com/tq?key=DATA_SOURCE_KEY&tq=select%20D%20where%20B%20%3E%2050000

Use the web tool found in the Chart Tools Query Language documentation to encode or decode a query string.

Information on Query language reference and encode/decode tool is available at

https://developers.google.com/chart interactive/docs/querylanguage#Setting_the_Query_in_the_Data_Source_URL.

Build your own data source

Enabling a data source to work with the Visualization API requires some amount of programming experience. Java or Python development experience is ideal as the helper libraries available are written in these languages. However, it is also possible to create a data source from scratch. To create a reliable Visualization API-friendly data source, the prospective data source should meet several minimum requirements.

The data source should:

  • Be available to clients
  • Accept HTTP GET requests
  • Handle unknown properties sent as part of requests (does not fail)
  • Take proper security precautions regarding:
    • Unknown clients
    • Parsing expectations
  • Make request and response strings UTF-8 encoded
  • Support JSON first
  • Document requirements for use

To make the addition of API compatibility easier, Google provides helper libraries in both Java and Python languages. The resulting task for the developer is then to be sure that the data source accurately relays its data to the helper library functions. The library functions will then relay the data, in an API-friendly format, to the application.

Summary

In this article we discussed data sources for Charts. We also discussed how Spreadsheets and Fusion Tables are used as data sources.

Resources for Article :


Further resources on this subject:


Google Visualization API Essentials Make sense of your data: make it visual with the Google Visualization API book and ebook.
Published: April 2013
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

About the Author :


Traci L. Ruthkoski

Traci L. Ruthkoski is from an eclectic background in computation, statistics, media technology, and business. Traci is always looking for the latest challenges in technology. Having worked as an IT professional in both clinical and medical research settings, she has built High Performance Computing clusters from hardware to software application. More recently, Traci has been enabling academic research advancement through computing and cyber infrastructure in the cloud domain. She continues to work at the University of Michigan, now holding several roles supporting the overall advancement of Research Cyber Infrastructure and Information Technology at the University. Traci has published academic work in the IEEE CloudCom 2010 proceedings as well as maintaining a blog/tutorial repository for cloud computing tools and trends.

Books From Packt


NumPy Beginner’s Guide - Second Edition
NumPy Beginner’s Guide - Second Edition

Sage Beginner's Guide
Sage Beginner's Guide

Matplotlib for Python Developers
Matplotlib for Python Developers

R Graphs Cookbook
R Graphs Cookbook

wxPython 2.8 Application Development Cookbook
wxPython 2.8 Application Development Cookbook

NumPy Cookbook
NumPy Cookbook

Sage ACT! 2012 Cookbook
Sage ACT! 2012 Cookbook

Google App Engine Java and GWT Application Development
Google App Engine Java and GWT Application Development


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
v
g
w
z
U
a
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software