As a result of high demand for the Visualization API, Fusion Tables API, and Maps API functionalities, several applications have been developed by Google to provide a simple graphic user interface (GUI) on top of these common but fairly complex integrations. Fusion Tables and Google Spreadsheets provide application interfaces for chart creation, but ultimately rely on various underlying Google API infrastructure functionalities. Using the GUIs prior to direct API manipulation offers an easy-to-understand building block for learning the Visualization API.
In this chapter we will cover:
- Creating charts in Google Spreadsheets
- The Chart Editor
- Apps Script
- Creating charts in Google Fusion Tables
- Geocoding
- Markers, Lines, Areas, and Info Windows
For anyone who has used a generic spreadsheet application in the past, Google Spreadsheets becomes a natural starting point when learning about Google visualizations. Data entry, chart creation, and formula functionality in Google Spreadsheets is extremely similar to other generic spreadsheet applications. Yet, a significant difference between traditional spreadsheet charting applications and Google Spreadsheets is the ability to enhance capabilities by overlying Apps Scripts, and re-create and enhance the same chart with the Visualization API. Direct API access allows a developer to include advanced features such as style customization and dynamic manipulation of the data.
Spreadsheets are built from Google infrastructure, and thus inherit some dynamic capabilities from the Visualization API. These features are generally presented as static capabilities in Spreadsheets, and can only be modified if control has been offered through the GUI (for example, colors and fonts). Then, additional chart functionality can be implemented through the JavaScript language used to extend Google Apps, named Apps Script. It is also worth noting that the Spreadsheets application has its own API interface, which opens up entirely new opportunities to be used that are outside the scope of the Spreadsheets GUI application.
To create a new spreadsheet, click on the CREATE button, or open an existing spreadsheet in Google Drive.
Enter data into the spreadsheet manually, or use the bulk import method. To import, select File | Import… from the Spreadsheets menu. The Import file dialogue will give a variety of import formatting options as well as data placement options within the existing or new spreadsheet.
The data used in the following Spreadsheets example is the United States Census data, collected from the city of Chicago in 2010. The data lists the number of male and female individuals residing in the city of Chicago, divided by age group.
With data populated in the spreadsheet, select Insert | Chart… from the menu options to create a visualization of the data.
In Google Spreadsheets, the Chart Editor appears when we open an existing chart to edit in the advanced mode. It is the interface for developing visualizations from within Google Spreadsheets.
Chart Editor offers three primary functions. These functions are as follows:
In the Chart Editor, the ability to define which data is used in the visualization is found under the Start tab, which also happens to be the default view when opening Chart Editor. In this tab, the spreadsheet cells to be included in the visualization are set in the Data – Select ranges… box. It is also possible to combine ranges, switch rows and columns, and set label/header information to be displayed in the visualization.
Chart types are determined primarily in the Charts tab, but can also be set in the Recommended charts box in the Start tab. Depending on the configuration of the columns and rows in the spreadsheet, Spreadsheets will recommend the most suitable chart for the existing data's structure. If a desired type of chart does not correspond to the current data layout, it is necessary to learn about a chart's layout in order to configure the data appropriately. A chart that fits the current data configuration is represented by a colored thumbnail preview of itself. A chart that does not fit the current data layout will be represented as a grayed-out thumbnail preview of itself. Select the grayed-out thumbnail preview to view information on the required data layout for that particular visualization.
If additional detailed modifications to the chart are required beyond the quick edit funcionality, return to editing an existing visualization by selecting the Advanced chart… option in the drop-down menu on the chart itself.
Basic formatting of a visualization can be accomplished in the Customize tab. The features available for customization are dependent on the type of chart, but generally always include abilities to create a title, placement, and font format for the chart's legend, and change the colors and fonts of various attributes.
The Visualization functionality from within Google Spreadsheets is fairly basic. However, additional capabilities can be implemented through the use of Google Apps Script.
Apps Script is a JavaScript-based platform for scripting customized features into a variety of Google applications. Apps Scripts are allowed to call Google APIs and thus can be used to manipulate various aspects of a handful of Google applications. Apps Script is also as much a script management platform as it is a script executable platform. Scripts can be embedded in the respective Google application or can be standalone files in Google Drive. Additionally, Apps Script is capable of launching the scripts as a standalone web application.
Note
You can find more information about Apps Script Documentation at https://developers.google.com/apps-script/.
The following is a list of the Apps Script compatible Google products. Apps Script can be used in:
- Spreadsheets
- Documents
- Google Drive
- Sites
- Google-Hosted Web Application
- Gmail
- Calendar
The advantage of using Apps Script is that it provides a single platform on which to mix Google application functionality. With this capability, it is possible to create a single, feature-rich application from many Google applications and services. For example, the 2010 Chicago Census chart could benefit from dynamic user-controlled filters on the Male or Female columns or the Age column. Apps Script can facilitate data retrieval from the Spreadsheet and a development platform from which user-defined filtering web applications can be launched. Apps Script web applications are stripped-down versions of Google App Engine applications, but they run on the App Engine infrastructure. The Apps Script framework is designed so that scripts can be easily deployed as web applications.
In Fusion Tables, creating new scripts and customizing scripts is done in the manager and editor options. Navigating to Insert Tab | Script… allows insertion of existing third-party scripts from the user community gallery. Gallery scripts can then be modified once they are placed in a new, Apps Script supportive Google file. Navigating through the Tools Tab options, Tools | Script editor... and Tools | Script manager… allows access to the script editing console and a management function for spreadsheets embedded in the Spreadsheets file respectively. The Apps Script development platform also opens as a separate tab in the browser window, in this case, separate from the Spreadsheets tab.
The Apps Script editor console is arranged in a project style, which also includes basic debugging and web app publishing capabilities. Code is entered in the code view or edit window for which a file of type Google Script (gs) is created. Scripts contained in the project are organized in the side column. The menu tab and console controls contain several of the same functions, allowing various workflow preferences while developing.
To test a function in your project, select the name of the function from the drop-down menu and then click on the arrow or run button. This capability is nice to have when it's difficult to determine a script error. Running each function separately is a good way to test if each one is working on its own. To select a function to test apart from other functions in a script, use the drop-down list in the console controls bar.
Clicking on the drop-down list will display functions in the script that can be run. Select a function in the list to test its functionality.
The console control button with a bug icon will put the console in debug mode when clicked.
Apps Script is also a development environment for Google web apps that make use of various Google APIs. The debug mode, in addition to inline syntax and function help, makes Apps Script a decent development platform for Google API based apps.
The purpose of triggers is to initiate an event, such as sending notifications via e-mail at a scheduled time, if a particular action on or about the spreadsheet has occurred. The clock icon in the controls console bar will initiate a dialog with the developer to add, modify, or remove event triggers from the script.
Types of events that can trigger script execution are as follows:
- Opening the spreadsheet itself
- When a script function has been run
- Time-driven events
The cloud button is used to publish an Apps Script to Google App Engine.
To publish, Apps Script requires saving and managing versions by navigating to File | Manage Versions…. After a version is saved, navigating to Publish | Deploy as web app… or clicking on the cloud button will initiate the web deployment dialog.
The versioning control and the user interface creation options are currently in the experimental phase and are both currently under the File tab. Google updates its services and applications regularly, so whenever an experimental feature is added, it will be marked as EXPERIMENTAL! within the application itself.
Apps Script is versatile in its abilities beyond scripting web apps using the APIs. It is also capable of acting as a medium for using the APIs to create customizations in Google applications themselves. The following is a short example of how Apps Script can be used for creating a custom spreadsheet menu item. The menu item created in this example is named Launch App. Launch App contains a drop-down list for a script function called doGet. To continually illustrate the usefulness of Apps Script when combined with the Google APIs, Apps Script compatible code will be provided throughout the remainder of this book for each topic.
functiononOpen() {
/* Retrieves the active spreadsheet as a source */
var sheet = SpreadsheetApp.getActiveSpreadsheet();
/* Create the menu entry for a function */
var entries = [{
name : "Launch App",
functionName : "doGet"
}];
/* Create the Spreadsheet Menu tab. */
sheet.addMenu("Script Menu", entries);
};