Converting from UiApp + Chart Service to Html Service + Google Visualization API

In July 2012 Roee Eilat and Eric Koleda, respectively members of the Google Charts and Apps Script Teams, published a popular tutorial: “Building a Charts Dashboard”. Later, in December 2014, Google deprecated the UI Service that supports Charts – while strangely not deprecating the Charts Service itself. That’s left some confusion for developers.

For users of Google Apps Script, there are two alternatives for visualizing data:

  1. Continue using Charts + UiApp. While deprecated and not supported for auto-completion in the editor, UiApp still works. There has been no announcement that it will actually be shut down… yet.
  2. Follow Google’s recommendation, and use HtmlService instead. A feature request to have Charts supported by the HtmlService has been declined. So this seemingly-simple recommendation entails the additional requirement that you use Google’s other Charts offering, also known as the Google Visualization API, or GViz.

In today’s post, I will show you how to convert the old dashboard example from from UiApp + Chart Service to HtmlService + Google Visualization API. For those willing to geek out with me, there’s even an introduction to System Architecture.

UiApp + Chart Service

The tutorial presents five steps to produce a simple dashboard as a stand-alone web application:

Original dashboard
Original dashboard
  1. Create the data source
  2. Create the charts and controls
  3. Create and bind dashboard
  4. Create application and add dashboard
  5. Deploy as web app

The code is all available in the tutorial, so it’s not replicated here, but you can see it in action by clicking on the image.

System Architecture

Let’s take a detour to talk about system architecture. If you’re impatient, skip along to the next section to build a new dashboard.

If you’re like most people starting out with Google Apps Script, you got right into writing code to do little jobs without any concern about architecture, and it’s served you just fine. Thing is, the earliest GAS services were designed to support that approach – UiApp and Charts are prime examples. Both those services are easy to start to use because Google abstracted away many of the details of web development to make it more accessible for beginners. That was great, but the price was performance (as many slow system calls are required to build web pages) and loss of freedom (there’s a restricted set of supported visualizations, and no way to control how an application is styled). Eventually, many developers outgrow these services, and find the restrictions get in the way of being awesome.

To better understand how to divide a charting problem up for implementation with the HtmlService and GViz, it really helps to have some background in architecture. Time for some catching up, then!

When we talk about “System Architecture” at the highest level, we are referring to the relationships between interacting components of a system. Pretty much any system implemented in Google Apps Script can be generalized into three layers.

Presentation, Business and Data Layers in GAS
Presentation, Business and Data Layers

This three-layer (or three-tier) architecture represents an idealized model for system design.ref It allows us to separate concerns at a high level to simplify development and maintenance of the system.

  • Presentation Layer: Also referred to as “Front End” in web-based applications, this layer comprises the bits that an end user can see in their browser – the charts and control widgets of our dashboard in this example, sometimes custom menus, sidebars and dialog windows. It includes any client-side JavaScript that produces dynamic content (our filters), and the style sheet(s) dictating our app’s look.
  • Business Logic Layer: The code that implements the core functionality of your application is in this layer. Functions that support the Presentation Layer are typically invoked via google.script.run calls. Cooperative interfaces to external services appear here as well – for example, if your web app used the ContentService to provide JSON data for consumption by another script, we’d consider that Business Logic.
  • Data Access Layer: You’ll find this referred to as the “Back End” in web applications, usually along with the previous layer. This layer takes care of storage and retrieval of data for the application. In a Google Apps Script web application, typical storage may be a spreadsheet, custom-formatted text file on Google Drive, or an external database service.

The original dashboard implementation did not follow this model, but our new one will. As already mentioned, the original dashboard could not separate concerns completely, because of the design of the Google Apps services it used.

New Dashboard

Remember, the Charts service is essentially a wrapper around GViz, so every chart and widget you’ve seen for Charts is available natively through the API. In fact, we have many more options available to us – jump ahead to Additional Chart Types for more about that.

We can create a client-side GViz dashboard that is a fair reproduction of the original dashboard, as shown here. As before, click to see a demonstration. (Spoiler alert – you can’t tell them apart.)

Dashboard demo new
New dashboard

Data Layer / Spreadsheet

The GViz documentation includes a Controls and Dashboards section with a demo based on the same data in the original UiApp + Chart Service demo. In both of those examples, the data for the dashboard Data Table is hard-coded. This causes a collision between the Presentation and Business Logic layers, as the data code mixes directly with generation of the user interface. When adapting the demo code, many people will simply replace the hard-coded data used in column & row construction with their own code for retrieving data. This results in unmaintainable code and does not scale up well, as illustrated in this question on StackOverflow.

In the new dashboard example we’ll take a different approach, using a spreadsheet as our data source, and take advantage of GViz capabilities that are not available in the Charts Service to simplify our code and provide better performance than we’d have by looping through spreadsheet content.

As a result of this decision, there is no code required to implement our Data Layer!

Code for new dashboard

The new dashboard demo assumes that you start with the “web app” temSelect Web Appplate script. Following best practices, the template contains one script file and three html files, which is an effective separation of concern, at the code level.

HyperText Markup Language (HTML), Cascading Style Sheets (CSS), and JavaScript (JS) are complementary languages used in the development of webpages and websites. HTML is mainly used for organization of webpage content, CSS is used for definition of content presentation style, and JS defines how the content interacts and behaves with the user.Wikipedia

Each file in the project contains code that addresses a single one of these concerns.

File Focus of concern
Code.gs Server-side Google Apps Script
Index.html Web App HTML (DOM Design)
Stylesheet.html Web App CSS
JavaScript.html Client-side JavaScript

This is a good habit to have, so we’ll replace the code in those files to produce our new dashboard. Let’s see how those files and concerns support the remaining two layers of our three-layer architecture.

Business Logic Layer

Code.gs

There are just two functions required for our dashboard.

  • doGet() prepares the web app’s presentation files, and servers them via the HtmlService. If we were implementing the dashboard as a dialog within a document, spreadsheet or form editor, we would instead write a function to open the dialog, but none of the display layer code would need to change.
  • getSpreadsheetData()note contains the business logic that connects our data source (spreadsheet) with the presentation layer. For our simple dashboard, this is a very simple read-and-respond function to pull from our source spreadsheet. To support a change in data source, or to enhance performance by adding Cache Service calls, we would make changes only here, without touching the display layer.

Presentation Layer

The dashboard’s presentation layer consists of code in the other three files, Index.html, JavaScript.html and Stylesheet.html. Google Apps Script projects may only contain two types of files, gs or html. If our presentation layer files were implemented in a different environment, they would typically have file extensions that indicated the file contents. Here, everything that is not Google Apps Script must be in an html file.

Index.html

We’ve already seen that this file contains the Web App HTML code, which defines the basic content that we will display. We will retain several blocks of code from the template:

A preamble that pulls in the CSS content.

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

A script block that facilitates passing templated values to imported JavaScript. Our dashboard has no such values, so the script block will be empty. Delete it if you wish. I prefer to leave the structure in place for possible future requirements.

<-- Store data passed to template here, so it is available to the
 imported JavaScript. -->
<script>
</script>

Finally, a postamble that pulls in the client-side JavaScript file.

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>

With those template elements explained, let’s look at the code between the <html> tags, which comprises the DOM Design for our new dashboard.

The original dashboard was constructed from panels, which in turn contained further panels or Charts Service elements. This was expressed as:

 dashboard.add(uiApp.createVerticalPanel()
                    .add(uiApp.createHorizontalPanel()
                              .add(ageFilter).add(genderFilter)
                             .setSpacing(70))
                    .add(uiApp.createHorizontalPanel()
                              .add(pieChart).add(tableChart)
                              .setSpacing(10)));

If you examine the resulting html output that is generated by this code, you’ll see that the panels are realized as cells in an HTML table. Rather than duplicate that decision, we’ll use a container tag (<div>) to create the same relationships. (We could alternatively use <span>, both are useful for identification and containment of elements.) Unlike UiApp panels, our <div>s carry no layout information (ala “VerticalPanel” or “HorizontalPanel” and setSpacing() calls), as we are separating that concern to our CSS code.

During design, it helps to sketch out the elements of a page before jumping to code. The following diagram shows the containers we’ll use, with their names, as well as the way they are expressed in our code.

DOM design

In summary, the steps to take when converting a UiApp + Chart Service display to html are:

  1. Identify the container elements in your UiApp code. These consist of various Panels.
  2. Sketch out the containment relationships of those containers. Give each container a relevant name.
  3. Write your html code as nested <div>s, respecting the container relationships in your design. The container names from the previous step should be added as id tags to their <div> equivalents.

Here is our resulting html code for the new dashboard:

JavaScript.html

Our separation of concerns strategy places all our Client-side JavaScript into this file, as part of the Display Layer of our architecture. We’ll use JavaScript to configure all aspects of the visualizations; define chart types and control types, associate data tables with them, and bind controls to charts. To support the simple Index.html which defines container relationships, we’ll also use JavaScript to dynamically add display and control elements to containers. Since we’re separating the style concerns to Stylesheet.html, our JavaScript will change class tags on elements to cause them to adopt the desired CSS style settings – we won’t directly manipulate the look of the dashboard here.

There are two external JavaScript libraries used by our client-side script. We’re using jQuery to help find elements and perform style property manipulation, and Google’s JavaScript API (jsapi) to load the visualization library, which is loaded as soon as the page is opened:

 // Load the Visualization API and desired package(s).
 google.load('visualization', '1.0', {'packages':['controls']});

Two functions cooperate to retrieve the dashboard’s data and display it. As soon as the visualization API is loaded, the sendQuery() function is invoked. Using the google.script.run facility, it sends its request to the server-side getSpreadsheetData() function. This is an asynchronous operation, so two callbacks are provided, a successHandler and a failureHandler. One or the other will receive the result of the server call, depending on the outcome.

The successHandler is drawDashboard(), and it expects to receive the data to display as a two-dimensional array. This design choice means that we can use google.visualization.arrayToDataTable() to have the entire table interpreted by GViz, instead of having to define columns and rows ourselves. The rest of the function uses GViz to define individual dashboard elements attached to our <div> containers, then binds the controls to chart elements and invokes the draw() method on the dashboard to start showing it.

StyleSheet.html

The final concern in our display layer is the Cascading Style Sheet that defines the layout and appearance of our dashboard. The inclusion of Google’s styling package add-ons1.css takes care of providing the basic look and behavior for most elements, as well as default spacing between them to conform with the look of the various Google Drive user interfaces. Referring to the id tags of our containers, we can customize them with minimal bother.

  • The setSpacing() calls for Chart Service elements become margin settings in the corresponding <div>
    #control-div {
     margin: 70px;
    }
    #charts-div {
     margin: 10px;
    }
  • HorizontalPanel contents are floated within their containers, and have dimensions applied if necessary.
    #piechart-div {
     width: 50%;
     height: 250px;
     float:left;
    }
    #table-div {
     width: 50%;
     float:right;
    }

Ultimately, we require about 15 lines of style info to mimic the original dashboard.

Additional chart types

Bubble ChartEarlier, I mentioned that Chart Service restricted our freedom; it limits us to just seven chart types, while through GViz we have 28 base chart types plus more contributed by the community.

One example of using GViz to display a Bubble Chart was presented in Google Apps Script – possible charts types, as shown in the accompanying image.

Using the same three-layer architecture and accompanying separation of concerns that’s been described in this article, you have a solid foundation for adopting any of these additional visualization types to your spreadsheet data.

Advertisements

12 thoughts on “Converting from UiApp + Chart Service to Html Service + Google Visualization API

  1. Very interesting article. Excellent explanation of the three-layer approach. I’m a newbie in Google Apps Script and found your article. It will help me to take right decissions when building my dashboard.

    Like

  2. Excellent article! Very clear, informative and helpful on a number of topics. One of the best and most helpful articles I’ve seen in a quite a while. You should be hired by Google to write their docs. Thank you very much for taking the time to do this.

    Like

  3. Great description of how to overcome the restrictions of a deprecated, over-simplified interface and open your web application to the myriad of new google charts. I only wish google would update their sample code to point to this article! This really helped, Thanks!

    Like

  4. Great job ! Thank you so much for all these explanation. I was really lost by following the “official” documentation. Your tutorial should replace Google’s one.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s