Home Guides Reference Support

Querying a Fusion Table

Overview

You can use public Google Fusion Tables as a data source for your chart. In order to do this, you must first understand how to send data queries for charts, and you should consult the Fusion Tables documentation and the Fusion Tables API.

Details

The following instructions are for an old version of the Fusion Tables API and may not work with the current version.

Here is how to query a Fusion Table for data.

  1. Find the ID of the Fusion Table table that you want. Open the Fusion Table in your browser and look for the docid in the URL. For example, if the URL is https://www.google.com/fusiontables/data?docid=1VlPiBCkYt_Vio-JT3UwM-U__APurJvPb6ZEJPg, the table ID is 1VlPiBCkYt_Vio-JT3UwM-U__APurJvPb6ZEJPg. Note that the table must be public for the query to work (that is, view permissions must be granted to everyone).
  2. Choose which columns to reference. You can use either the column label, or the string "colN" where N is the zero-based column index, starting from the leftmost column, for example: col0, col1, and so on.
  3. Create a query that includes a FROM clause that references the table ID, as well as the columns you have selected. Important: The query should use the Fusion Tables SQL syntax; this syntax is similar to, but not the same as the same as the Google Chart Tools query syntax. Clause order matters in this syntax: for example, the FROM clause must precede any optional GROUP BY clauses. A successful response will return a data table with columns appropriate for your chart. Read your chart's documentation to learn its required data format. You can send and handle the query using any supported methods, including google.visualization.drawChart() or the Query object.
  4. If you need to customize the column names, modify the retrieved table by calling DataTable.setColumnLabel() on it.
  5. Set the table as your chart's data source.

Important: Columns can appear to be a different data type than actually are. For example, column with values such as "1/12/2011" might be in a column of type string; Fusion Tables does not expose a way to learn the declared column type in tables that you do not own. Trying to reformat or sort a string-formatted date using a date formatter will fail.

Here is an example table that queries this Fusion Table using drawChart(). 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<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="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript">
    google.charts.load("current", {packages:['table']});
    google.charts.setOnLoadCallback(drawVisualization);
    function drawVisualization() {
      google.visualization.drawChart({
        "containerId": "visualization_div",
        "dataSourceUrl": "//www.google.com/fusiontables/gvizdata?tq=",
        "query":"SELECT 'Scoring Team', 'Receiving Team', 'Scorer', 'Minute of goal' FROM " +
                "1VlPiBCkYt_Vio-JT3UwM-U__APurJvPb6ZEJPg",
        "refreshInterval": 5,
        "chartType": "Table",
        "options": {}
     });
    }
  </script>
</head>
<body style="font-family: Arial;border: 0 none;">
  <div id="visualization_div" style="width: 600px; height: 400px;"></div>
</body>
</html>