Before an external data source can be visualized in a chart, a translation from the data source format to a Visualization API-compatible format must be performed. The Visualization API does not directly handle the modification of the values stored in the dataset itself. The intention is not to be a data management tool, instead, the API environment provides a handful of programmatic methods to load selected data into the application. Once in a format it can understand, the Visualization API can manipulate any aspect of the data structure and content. In this way, the Visualization API is able to store initial data and any permutations for visualization purposes, but not become a tool for data source management.
To translate between data sources and Visualization API applications, the data source must be "Visualization API-aware". There are several data source options already prepared to work with the Visualization API requests. These data sources may also have alternative methods of modification, such as manual entry or an API, but include the integrated ability to accept requests from the Visualization API.
Visualization API-ready data sources are as follows:
- Google Spreadsheets
- Google Fusion Tables
At first the idea of primarily using Spreadsheets and Fusion Tables as data sources appears very limiting. However, the additional feature of API integration makes these two applications fundamental for Google API-based applications. This is amplified by the fact that Google Apps are scalable, and performance enhancements are an inevitable aspect of the future. It is also very likely to be higher-powered data instances such as Google's SQL and Big Query which will eventually become Visualization API compatible by default. To strengthen this prediction, although efforts are still in experimental mode, API integration into the Apps Script environment is already available.
In the meantime, however, it is of course not realistic to assume that data can be confined to either Spreadsheets or Fusion Tables. Being able to directly query alternate sources from a Visualization API application is very desirable. To query datasets beyond Spreadsheets and Fusion Tables, the Visualization API provides a set of development libraries for the purpose of creating custom "Visualization-friendly" data sources. These libraries give developers the ability to integrate their own data source with the Visualization API protocol.
The process of retrieving information from a data source follows three general steps regardless of the data source type. First, the Visualization application must make a request for data from the data source. This operation occurs in the Visualization API command format. Next, the data source must interpret the application's request and assemble a response the Visualization code is able to understand, which is: DataTable(). The application receives the familiar DataTable() type as a response from the data source and can then use the response as usual.
As mentioned previously, retrieved data, in any format, is staged for visualization by the DataTable() class. In this book, DataTable() has so far been a necessary component of a visualization but has not been explicitly discussed. In simple examples for which data is a part of the application code, DataTable() is often observed in its arrayDataTable() helper function form, such as the case with simple area chart examples discussed in Chapter 4, Basic Charts.
var data = google.visualization.arrayToDataTable([
['Age', 'Male', 'Female', 'Both Sexes'],
['Under 5 years', 94100, 91787, 185887],
['5 to 9 years', 84122, 81955, 166077],
DataTable() also takes the form of newDataTable(), specifically in Google Apps Script applications, as illustrated in the following code snippet:
var data = Charts.newDataTable()
.addColumn(Charts.ColumnType.STRING, "Age")
.addColumn(Charts.ColumnType.NUMBER, "Male")
.addColumn(Charts.ColumnType.NUMBER, "Female")
.addColumn(Charts.ColumnType.NUMBER, "Both Sexes")
.addRow(['Under 5 years', 94100, 91787, 185887])
.addRow(['5 to 9 years', 84122, 81955, 166077])
DataTable() may seem like a simple component of a Visualization API script when presenting data from within the API script itself. Yet as data sources external to the script are introduced, the big picture role of DataTable() becomes increasingly important given its role as a data intermediary for external dataset query.
Note
Data Table reference is available at https://developers.google.com/chart/interactive/docs/reference#DataTable.
Beyond simple database add/remove functionality, DataTable() also allows for table grouping and joining capabilities. This functionality can be applied with a DataTable() or between two separate DataTable() functions The group operation sorts a DataTable() function's rows and then groups them together by column value. In the following table, the 2010 Chicago Census data has several columns that are in duplicate.
The DataTable().group operation consolidates multiple rows in the table, and also sums the values of the rows that were combined. To do this, group needs to know on which column the consolidations should be defined. In this example, the first column (column [0]) is the key, or defining column for grouping. The group operation can also perform select other manipulations on a column when a consolidation of rows occurs. The example displays the sum of column three for the rows grouped together, and the existing value in the cell of column three if there is no grouping needed. The group option follows this syntax:
varmy_table = google.visualization.data.group(data_table, keys, columns)
Here, keys is the column to group by, and columns provides additional parameters for which columns to display or perform a manipulation.
function drawGroup() {var data_table = google.visualization.arrayToDataTable([
['Age', 'Male', 'Female', 'Both Sexes'],
['Under 5 years', 94100, 91787, 185887],
['5 to 9 years', 84122, 81955, 166077],
['5 to 9 years', 0, 0, 0],
['10 to 14 years', 83274, 81192, 164466],
['15 to 19 years', 91528, 91405, 182933],
['20 to 24 years', 108407, 114620, 223027],
['25 to 29 years', 134931, 0, 0],
['25 to 29 years', 0, 141208, 276139],
['30 to 34 years', 119828, 119584, 239412],
['35 to 39 years', 100651, 99857, 200508],
['40 to 44 years', 89957, 87674, 177631],
['45 to 49 years', 85645, 86217, 85931],
['45 to 49 years', 85645, 86217, 85931],
['50 to 54 years', 80838, 86037, 166875],
['55 to 59 years', 68441, 76170, 144611]
]);
// Group data_table by column 0, and show column 3 aggregated by sum.
var grouped_dt = google.visualization.data.group(
data_table, [0], [{'column': 3, 'aggregation': google.visualization.data.sum, 'type': 'number'}]);
var table = new google.visualization.Table(document.getElementById('table'));
table.draw(data_table, null);
var grouped_table = new google.visualization.Table(document.getElementById('grouped_table'));
grouped_table.draw(grouped_dt, null);
}A join
is another DataTable() operation that joins two separately defined DataTables() together. This type of manipulation is particularly useful when two datasets are available, each containing similar elements of the same item. For example, a table of census data with only male age groups could be combined with a similar table of the complementary female population by age group.
The preceding separate but complementary tables are joined to create a single combined table.
The syntax for a DataTable().join is as follows:
Var my_talble = google.visualization.data.join(table1, table2, joinMethod, keys, table1Columns, table2Columns)
Here, table1 and table2 are the tables to be joined. The joinMethod function uses one of four possible parameters: full/inner/left/right.
full: The output table includes all rows from both the tables, regardless of whether the keys match or not. Unmatched rows will have null cell entries; matched rows are joined.inner: This is a full join, but only includes rows where the keys match.left: The output table includes all rows fromdata_table1, whether or not there are any matching rows fromdata_table2.right: The output table includes all rows fromdata_table2, whether or not there are any matching rows fromdata_table1.
Keys is a set of definitions, which tells the join operation which columns to join as well as in what order the joins are to be performed. The syntax for keys is an array. table1Columns and table2Columns
indicate which columns are to be displayed from each original table in the resulting joined table. These two configurations are also expressed as arrays. For example, the following code illustrates that the join operation is to be based on the comparison of the first column's values ([0,0] where one zero represents table1 table's first column and the other represents table2 table's first column). The second two bracketed 1's represent the original data table columns to be displayed along with the final joined table.
function drawJoin() {var data_table1 = google.visualization.arrayToDataTable([
['Age', 'Male'],
['Under 5 years', 94100],
['5 to 9 years', 84122],
['10 to 14 years', 83274],
['15 to 19 years', 91528],
['20 to 24 years', 108407],
['25 to 29 years', 134931],
['30 to 34 years', 119828],
['35 to 39 years', 100651],
['40 to 44 years', 89957],
['45 to 49 years', 85645],
['50 to 54 years', 80838],
['55 to 59 years', 68441],
['60 to 64 years', 54592],
['65 to 69 years', 37704],
['70 to 74 years', 27787],
['75 to 79 years', 20448],
['80 to 84 years', 14637],
['85 to 89 years', 7842],
['90 years and over',3340]
]);
var data_table2 = google.visualization.arrayToDataTable([
['Age', 'Female'],
['Under 5 years', 91787],
['5 to 9 years', 81955],
['10 to 14 years', 81192],
['15 to 19 years', 91405],
['20 to 24 years', 114620],
['25 to 29 years', 141208],
['30 to 34 years', 119584],
['35 to 39 years', 99857],
['40 to 44 years', 87674],
['45 to 49 years', 86217],
['50 to 54 years', 86037],
['55 to 59 years', 76170],
['60 to 64 years', 63646],
['65 to 69 years', 47366],
['70 to 74 years', 38238],
['75 to 79 years', 30252],
['80 to 84 years', 24467],
['85 to 89 years', 16548],
['90 years and over',9303]
]);
// Create an full join of data_table1 and data_table2, using columns 0 as the key, include column 1 from both data tables
var joined_dt = google.visualization.data.join(data_table2, data_table1, 'full', [[0,0]], [1], [1]);
var table1 = new google.visualization.Table(document.getElementById('table1'));
table1.draw(data_table1, null);
var table2 = new google.visualization.Table(document.getElementById('table2'));
table2.draw(data_table2, null);
var joined_table = new google.visualization.Table(document.getElementById('joined_table'));
joined_table.draw(joined_dt, null);
}Note
Information on Data Manipulation methods is available at https://developers.google.com/chart/interactive/docs/reference#google_visualization_data.
Live examples on Group is available at http://gvisapi-packt.appspot.com/ch6-examples/ch6-group.html.
Live examples on Join is available at http://gvisapi-packt.appspot.com/ch6-examples/ch6-join.html.
The primary method for manipulating data housed in the Spreadsheets application is through the application GUI itself. Since the Visualization API is not a data management editing interface, it is wrong to assume that an application will have adequate control over the data through DataTable() functionality. Rather, changes to the data stored in Spreadsheets must be accomplished through the GUI, Spreadsheets API, and forms.
Google Spreadsheets contains a form creation tool, which can be published to various destinations directly from the Spreadsheets application. Forms does not contain all of the advanced features of enterprise-class survey software, but is an easy-to-use data input method. To create a Spreadsheets Form, select Tools | Create a form from the menu bar.
In the form document that is created, use the Add Item button, found in-line with the form questions, to add additional questions to the form. Alternatively, select the Insert option from the menu bar to insert form questions.
After adding the desired questions, publish the form to start collecting data. From the File menu, select an option for publication or sharing. The form can be published on the Google social networking site, Google+, emailed, embedded in a web page, or linked to form a web page.
To view the form live in a browser, locate the View live form button and click on it.
Forms can be edited after publication, and can be set to accept or reject new entries without damaging the existing data collected. Although remedial, forms are a powerful way to input data into a Visualization API-friendly data source.
The Spreadsheets API is accessible through the HTTP protocol, with client libraries available for Java and .NET. The HTTP methods consist primarily of GET and POST methods (RESTful), where data is structured with Google's custom XML namespace tags (<gsx>). For example, adding a row to the 2010 Census data, the xmlns entry would look something like the following code:
<entry xmlns="http://www.w3.org/2005/Atom"
xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">
<gsx:age>30 to 32 years</gsx:age>
<gsx:male>49</gsx:male>
<gsx:female>51</gsx:female>
<gsx:both_sexes>100</gsx:both_sexes>
</entry>The preceding row entry would then be inserted via a POST message to the appropriate location in the Spreadsheet.
The manipulation of the Spreadsheets API is beyond the scope of this book, but more information can be found on Google's Spreadsheets API developer resource.
Note
Spreadsheets API reference is available at https://developers.google.com/google-apps/spreadsheets/?hl=en.
The Fusion Tables API is invoked through SQL-like queries. SQL is a special-purpose programming language designed for managing relational database systems. The Chart Tools Query Language discussed later in this chapter is intentionally very similar to SQL as SQL is a widely used standard language for data management.
Note
Fusion Tables API SQL Query reference is available at https://developers.google.com/fusiontables/docs/v1/sql-reference.