Data sources for the Charts

11 min read

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


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.


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.


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 com/drive/bin/

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

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.


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

(' =0AhnmGz1SteeGdEVsNlNWWkoxU

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.


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.
var data = response.getDataTable();
visualization = new google.visualization.Table
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 >
<meta http-equiv="content-type" content ="text/html; charset=utf-8"/>
Google Visualization API Sample
<script type="text/javascript" src ="">
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
<script type="text/javascript">
var visualization;
function drawVisualization() {
// To see the data that this visualization uses, browse to

var query = new google.visualization.Query



// Send the query with a callback function.

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

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


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


View live examples for Spreadsheets at

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: //
?key= 0AhnmGz1SteeGdEVsNlNWWkoxU3ZRQjlmbDdTTjF2dHc&headers=-1")
.setDimensions(600, 400)
.setXAxisTitle("Age Groups")
.setTitle("Chicago Population by Age and Gender - 2010 Census")
varui = UiApp.createApplication();

View live examples in Apps Script at https://script. 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.


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.


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:


This ID is the unique identifier for the table.


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() {
containerId: 'visualization',

query: 'SELECT Age, Male, Female FROM 1Olo92KwNin8wB4PK_
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.

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://

Information on API Explorer—column list is available at

https:// reference/column/list#try-it.


Please enter your comment!
Please enter your name here