Build a Dashboard from Google Sheets
  • 11 Apr 2022
  • 7 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Build a Dashboard from Google Sheets

  • Dark
    Light
  • PDF

Overview

With Preset you can connect to a multitude of databases and quickly get meaningful insights about your data. Preset comes with a PostgreSQL instance that contains various preloaded example databases, but why not load your own data to familiarize yourself with Preset?

In this walkthrough article we will show how easy it is to connect data to Preset, build charts, and then create a gorgeous dashboard. Specifically, we'll be working with Netflix data stored in a very common medium: the Google Sheet.

By the end of this tutorial your new dashboard will look like this:

New_Finalized_Dashboard


Step 1: Create a Google Sheets Database & Dataset

To start, we need to connect a database to Preset. One of the easiest ways for us to do this is to use a Google Sheet populated with data.

To create one for this walkthrough, please visit our Google Sheets (Public) walkthrough article and create a new Google Sheet-based database.

When done, please return here so that we can use that data to create charts and build a dashboard!


Step 2: Create a Big Number with Trendline Chart

Open Explore Page

We are going to create several charts based on this dataset, after which we will put those charts in a dashboard so you can get an overview of the different metrics in your data.

Start by navigating to DataDatasets.

To create a chart, just click on the dataset you added in Step 1.

Select_Dataset_1

This will take you to the dataset's Explore page, as shown below.

Select_Dataset

The default view is a table of your data showing the aggregate count of records over a week. With Superset you have access to many types of data visualization that suit your specific needs.

Select Big Number with Trendline Chart

Let’s start with a Big Number with Trendline chart — this allows you to quickly identify scale and direction in your data.

In the Visualization Type field, select Table (the default selection).

Change_Visualization_1

...and, in the Select a visualization type panel, select Big Number with Trendline. Please see Create a Chart if you need some assistance with selecting a visualization type.

Change_Visualization_2

Configure Time and Query

In the Time Column field, select date_added and then, in the Time Grain field, select a time grain of Week.

In the Time Range field, let's filter data by the last year. Start by selecting No filter and then, in the Range Type field, select Last. We'll further refine this by selecting last year. To learn more about how to create custom time ranges, please see Configure the Time.

Select_Time_Range

We are going to count the number of movies / tv shows in our table so, in the Metric field, drag & drop the COUNT(*) metric. If you need help with adding metrics and columns to the Query section, please see Configure a Chart.

The Chart Type, Time, and Query panels should look as follows:

Big_Number_Chart_Type-Time-Query

Configure Options

Now we will configure the big number with trendline chart to compare week on week data.

To do this, expand the Options section and complete as follows:

Big_Number_Chart_Options

Configure Rolling Window

We will count data cumulatively over a rolling period of 52 weeks.

To do this, expand the Advanced Analytics section and define a Rolling Window as follows:

Big_Number_Chart_Rolling_Window

Run Query

Lastly, select Run Query to see the results. The results should look as follows:

Big_Number_Chart_Content_Panel


Step 3: Create a Dashboard

A dashboard is a visual representation of multiple sets of data. In the world of Preset and Superset, a dashboard is a collection of charts. Dashboards have the unique capability to tell a story by combining different types of charts to form a narrative.

We can save the chart we just created and create a dashboard at the same time. Let’s try it!

Start by selecting Save — the Save chart window appears.

Save_as_Big_Number_With_Trendline

Select Save as and, in the Add to Dashboard field, enter a name for your new dashboard — after entering the name, be sure to select the option below that features your new dashboard name. In this example, we are creating a dashboard called Netflix Insights.

Lastly, select Save & Go To Dashboard.

A first draft of your new dashboard appears! It should look similar to the following:

New_Dashboard_v1

By the way, you can also navigate to Dashboards (in the Toolbar at the top) and then select +Dashboard to create a new dashboard. From there, you can drag & drop your charts to the new dashboard.


Our dashboard so far looks a bit plain and may need some visual customization for more impact. To do this, let’s add a Netflix logo to the dashboard.

Start by editing your dashboard by selecting the Edit pencil icon. Then, under the Components tab, drag & drop Markdown to the dashboard.

Replace the default markdown text with an html container:

<div class="image-container"></div>

Next, click on the ellipsis icon on the right (3 dots) and select Edit CSS.

In the Live CSS Editor panel, paste the following:

.image-container {
  background-image: url('https://cdn.vox-cdn.com/thumbor/5VGuWp5szQWExUnPTbsA0wXYoQI=/1400x1050/filters:format(png)/cdn.vox-cdn.com/assets/755523/netflix-logo-705px.png');
  background-size:cover;
  background-repeat:no-repeat;
  width:100%;
  height:100%;
} 

Add_Netflix_Logo_GIF


Step 5: Create a Treemap v2 Chart

Now let’s go back to our dataset and add another chart. We want to see a visual breakdown of categories and ratings for movies in the US market.

For this, a Treemap v2 chart is an ideal choice, so let’s create one! Use the graphic below as a guide to configure a Treemap v2 chart:

New_Treemap_v2

When done, you can save it to the dashboard but opt to remain on the Explore page so that you can create another chart. In the Add to Dashboard field, remember to select your dashboard name (e.g., Netflix Insights).

You can do this by selecting the Save button (instead of Save & Go To Dashboard) — be sure to create a unique chart name.

Save_as_Treemap_v2


Step 6: Create a Sunburst Chart

Next, we will convey the re-partition of ratings per category — for this, a Sunburst chart makes an ideal candidate.

Use the graphic below as a guide to configure a Sunburst chart:

New_Sunburst_Chart

Great work! Remember to save the chart to your dashboard. you can save it to the dashboard but opt to remain on the Explore page so that you can create another chart.


Step 7: Create a Pie Chart

Now, let’s add a simple pie chart to see the top 5 categories. Use the settings in the graphic below to create the pie chart:

New_Pie_Chart

Feel free to select the Customize tab and configure the colors and settings.

Customize_Pie_Chart(2)

Lastly, use Save as to save the chart under a unique chart name and then select Save & Go To Dashboard.

Your dashboard should look similar to the following:

New_Dashboard_v2


Step 8: Create a Pivot Table

Lastly, we’re going to add a pivot table that will display the re-partition of Netflix programs by country and rating.

Like before, select your dataset and then, from the Explore page, create a Pivot table. Configure your Pivot table as follows:

Pivot_Table_Configuration

Also, in the Pivot Options panel, complete as follows:

Pivot_Table_Options

After running your query, the results should look similar to the following. This table displays the number of movies and TV shows by category, rating, and country.

Complete_Pivot_Table_2

Save the Pivot table and then go to your dashboard.


Step 9: Finalize Dashboard

At this stage, more than likely your charts are not organized too well on the dashboard, but that's perfectly fine!

Here's an example:

New_Disjointed_Dashboard

Let's take a moment to move some of those charts around so that we have a more user-friendly look & feel.

In the top right corner, select the pencil Edit icon and then drag & drop the charts to match the aesthetics you're looking for.

Here's our finalized dashboard:

New_Finalized_Dashboard


A Word About Filtering...

Preset's Dashboard Filters offer powerful data filtering functionality coupled with an easy-to-use intuitive interface. From any dashboard, you can expand the Filters sidebar to quickly apply a variety of filters across-the-board to all charts within a dashboard.

We strongly recommend you take a moment to browse through our Dashboard Filtering article where we discuss how to apply filters to dashboard charts.

Your new Google Sheet-based dashboard is a perfect candidate, so give it a try!


Conclusion

With Preset, you can get a good looking, filterable, and insightful dashboard up and running in a matter of minutes. Dashboards are powerful tools that enable you to make sense of your data and provide strong visual support for stakeholders in your organization.

Now that you are connected to your Google Sheet data, why not try to connect to your database of choice and start building some more amazing dashboards?

You can also check Preset’s User Corner on YouTube for quick tutorials and help on how to get started!


Was this article helpful?

What's Next