Data sources for the Charts

(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 >
  <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

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;
}

SELECT Column_name FROM Table_ID

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'
            }
          }
        });
}