Google Apps Script

Building a Charts Dashboard

Roee Eilat, Google Charts Team
Eric Koleda, Google Apps Script Team
July, 2012

Goal

This tutorial will demonstrate how to create a charts dashboard, with controls that allow you to instantly filter the data displayed in the charts.

Time to complete

Approximately 15 minutes.

Prerequisites

Before you begin this tutorial, you should ensure that:

  • You have a basic understanding of programming in JavaScript or similar languages.
  • You are comfortable using the Script Editor, as covered in Building your first script.

Overview

A dashboard is a visual structure that lets you organize and manage multiple charts that share the same underlying data. The three building blocks of a dashboard are the data source, charts, and controls. Controls are user interface widgets (category pickers, range sliders, or autocompleting text boxes) that someone can interact with in order to drive the data managed by a dashboard to the charts that are part of it.

Because of its interactive nature, a dashboard is built in a Google Apps Script UI application. A UI application can be embedded in a Google Spreadsheet, Google Site or served as HTML using the "Deploy as webapp" option. They are perfect for creating interactive reports, where users can gain extra insight through exploring the data.

Step 1: Create the data source.

function doGet() {
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, "Name")
      .addColumn(Charts.ColumnType.STRING, "Gender")
      .addColumn(Charts.ColumnType.NUMBER, "Age")
      .addColumn(Charts.ColumnType.NUMBER, "Donuts eaten")
      .addRow(["Michael", "Male", 12, 5])
      .addRow(["Michael", "Male", 12, 5])
      .addRow(["Elisa", "Female", 20, 7])
      .addRow(["Robert", "Male", 7, 3])
      .addRow(["John", "Male", 54, 2])
      .addRow(["Jessica", "Female", 22, 6])
      .addRow(["Aaron", "Male", 3, 1])
      .addRow(["Margareth", "Female", 42, 8])
      .addRow(["Miranda", "Female", 33, 6])
      .build();

This is the same as when using charts normally. In the example, we created and populated a DataTable; alternatively, we could have retrieved a Spreadsheet range and used it as a data source for the dashboard.

Step 2: Create the charts and controls

  var ageFilter = Charts.newNumberRangeFilter()
      .setFilterColumnLabel("Age")
      .build();

  var genderFilter = Charts.newCategoryFilter()
      .setFilterColumnLabel("Gender")
      .build();

  var pieChart = Charts.newPieChart()
      .setDataViewDefinition(Charts.newDataViewDefinition()
                            .setColumns([0,3]))
      .build();

  var tableChart = Charts.newTableChart()
      .build();

Notice that when creating the controls and charts objects we don't set a data table for them, we'll set the data table for the dashboard object which will behind the scenes driving the data through the controls (which will filter it according to their state) to the charts. Additionally, a DataViewDefinition is added to the pie chart, which specifies that it should only consider the first and fourth column (Name and Donuts eaten) when rendering that chart.

Step 3: Create and bind dashboard

  var dashboard = Charts.newDashboardPanel()
      .setDataTable(data)
      .bind([ageFilter, genderFilter], [pieChart, tableChart])
      .build();

When a control is bound to a chart, the chart will display only the part of the data that complies with the control's current state. The binding of controls to charts can be one to one or many to many as seen in the example above.

Step 4: Create application and add dashboard

  var uiApp = UiApp.createApplication();

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

  uiApp.add(dashboard);
  return uiApp;
}

The dashboard object itself is a panel container, but we use vertical and horizontal panels to have greater control over the dashboard's layout.

Step 5: Deploy as web app

Follow the steps in the Web Apps guide to deploy the script as a web app.

What exactly are the Filter and Dashboard objects?

A Filter object does exactly as its name implies: it filters the data table over a specific column so that only the rows that comply with its constraints are left. The widget is initiated with the entire range of optional values, and changes its constraint as the user interacts with it. In the example, the number range filter is set on the "Age" column and initially shows the whole range (3-54). When the user changes the range by interacting with it, only the rows with age values in that range will be displayed.

The Dashboard object has two purposes. One is managing the data flow, the other being a visual container. Driving data from the data source through the controls to the charts is done according to the binding definition. When a control is bound to a chart the dashboard first pass the data table through the control which in turn filters it and then passes it to the chart. When one or more controls are bound to one or more charts, the dashboard passes the intersection of the filtered data table to the charts. As a visual container a dashboard holds a single panel that contains all the controls and charts managed by it.

Complete script

function doGet() {
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, "Name")
      .addColumn(Charts.ColumnType.STRING, "Gender")
      .addColumn(Charts.ColumnType.NUMBER, "Age")
      .addColumn(Charts.ColumnType.NUMBER, "Donuts eaten")
      .addRow(["Michael", "Male", 12, 5])
      .addRow(["Michael", "Male", 12, 5])
      .addRow(["Elisa", "Female", 20, 7])
      .addRow(["Robert", "Male", 7, 3])
      .addRow(["John", "Male", 54, 2])
      .addRow(["Jessica", "Female", 22, 6])
      .addRow(["Aaron", "Male", 3, 1])
      .addRow(["Margareth", "Female", 42, 8])
      .addRow(["Miranda", "Female", 33, 6])
      .build();

  var ageFilter = Charts.newNumberRangeFilter()
      .setFilterColumnLabel("Age")
      .build();

  var genderFilter = Charts.newCategoryFilter()
      .setFilterColumnLabel("Gender")
      .build();

  var pieChart = Charts.newPieChart()
      .setDataViewDefinition(Charts.newDataViewDefinition()
                            .setColumns([0,3]))
      .build();

  var tableChart = Charts.newTableChart()
      .build();

  var dashboard = Charts.newDashboardPanel()
      .setDataTable(data)
      .bind([ageFilter, genderFilter], [pieChart, tableChart])
      .build();

  var uiApp = UiApp.createApplication();

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

  uiApp.add(dashboard);
  return uiApp;
}

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.