Data Queries

Introduction

Visualizations display data stored in a DataTable class. You can create a DataTable by using JavaScript code in your page. Another way to create a DataTable is by sending a query to a data source that supports the Visualization API, for example you may read data from a Google Spreadsheet and visualize it using the Visualization API. This section describes how to create and send queries to data sources that support the Visualization API.

Sending a Query

Using the Query class, you can create and send queries to data sources. The data source is identified by a URL that is provided by the data source application. For example, to get the data source URL from a Google Spreadsheet, do the following:

1. Open a spreadsheet.
2. Insert a gadget and select the range of cells you want to read.
3. In the gadget title, click the arrow icon on top right of the title bar.
4. Select 'get query data-source URL' from the popup menu.

The data source URL for a Google Spreadsheet will look something like http://spreadsheets.google.com?...&key=123ABC

function initialize() {
  // Replace data source URL on next line with your data source URL
  var query = new google.visualization.Query('http://spreadsheets.google.com?...&key=123AB');
  query.send(handleQueryResponse);  // Send the query with a callback function
}

function handleQueryResponse(response) {
  // Called when the query response is returned
}

The default query returns all of the information in the data source. Note that in the case of Google Spreadsheets, a data-source is most often a group of cells, not the whole spreadsheet. You can refine your selection criteria, and perform data manipulations by using the query language.

function initialize() {
  var query = new google.visualization.Query('http://spreadsheets.google.com?...&key=123AB');
  query.setQuery('select C, sum(B) group by C');
  query.send(handleQueryResponse);  // Send the query with a callback function
}

Processing the Query Response

The response handler function is called when the data source query returns. The parameter to the response handler function is of type google.visualization.QueryResponse. A query can fail, or run successfully. If the query was successful, the query response contains a data table (class google.visualization.DataTable). If the query failed, the query response contains information about the error, and no DataTable.

Typically, a response handler does the following:

1. Checks if the query failed or ran successfully, and issues an error message when needed.
2. Creates some visualization of the returned data.

function handleQueryResponse(response) {

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

  var data = response.getDataTable();
  var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
  chart.draw(data, {width: 400, height: 240, is3D: true});
}