Visualise Google Sheet Data in Apache Superset

In this blog post, we will visualize data from a Google Sheet and create charts in Apache Superset. We will also add these charts to a dashboard.

We will follow these steps in this blogpost

1. Download the Dataset from Kaggle.
2. Connecting your Google Sheet as a data source in Superset.
3. Creating a "Dataset" within Superset.
4. Building your first chart.
5. Adding your new chart to a dashboard.

Let's dive in! 🚀

Download the Dataset from Kaggle 📈

For this guide, we'll use a popular IMDB Top 1000 Movies dataset from Kaggle.

1. Download the data: Grab the CSV file from the Kaggle dataset page.
2. Upload to Google Sheets: Create a new Google Sheet, and import the CSV file (File > Import > Upload).
3. Share the Sheet: This is a crucial step! Your Google Sheet must be shareable. Click the Share button in the top right, and change the setting to Anyone with the link can view Copy the shareable link.

Step 1: Connect Google Sheets as a Database in Superset 🔌

1. In the Superset interface, click the + icon in the top-right menu.
2. From the drop-down, select Data and then Connect Google Sheet.
3. A pop-up window will appear. Paste the shareable Google Sheet URL you copied earlier into the "URL" field. Give your connection a clear "Database Name" (e.g., "IMDB Movie Data GSheet").
4. Click Connect.
5. Verify the connection: To make sure it worked, you can navigate to Settings > Database Connections. You should see your new "IMDB Movie Data GSheet" connection listed.

Step 2: Create a New Dataset from Your Google Sheet 📝

1. Navigate to the Datasets tab (you might find this under Data > Datasets).
2. Click the + Dataset button in the top-right.
3. In the "Create a new dataset" screen:
   • DATABASE: Select the "IMDB Movie Data GSheet" connection you just created.
   • SCHEMA: Select the specific tab within your spreadsheet (e.g., "Sheet1").
   • TABLE: The same tab name will likely appear here. Select it.
4. Superset will load all the columns from your sheet, along with their data types.
5. Click the Create Dataset and Create Chart button at the bottom.

Step 3: Build Your First Chart with the Google Sheet Data 📊

1. Select your Dataset: If you weren't redirected, go to Charts > + Chart. Select the new "IMDB Movie Data" dataset you created in Step 2.
2. Choose a Visualization Type: Select the "Table Chart" visualization.

Customizing Your Table Chart

In the "Data" tab, you have two main sections: Dimensions and Metrics.

• Dimensions: These are the columns you want to group by or display as text (e.g., Movie Title, Genre, Director).
• Metrics: These are the columns you want to aggregate (e.g., SUM of Gross, AVERAGE of Rating, COUNT of Votes).

1. Add Dimensions: Drag columns like Series_Title, Released_Year, and Genre from the "Columns" list on the left into the Dimensions box.
2. Add Metrics: Let's find out the highest-grossing movies.
   • Drag the Gross column into the Metrics box.
   • Click the SUM label to edit the aggregate. Change it to MAX. 💰

   

3. Add More Metrics: Let's also see the number of votes.
   • Drag the No_of_Votes column into the Metrics box. The default SUM is fine here, or you could use MAX as well. 🗳️

   

4. Update the Chart: Click the Update Chart button. You'll see your table populate on the right with the data directly from your Google Sheet!

Step 4: Save Your Chart and Add to a Dashboard 🖼️

Once your chart looks good, you'll want to save it and add it to a dashboard.

1. Click the Save button near the top-left of the Explore view.
2. A pop-up will appear. Give your chart a descriptive name (e.g., "IMDB Top Movies by Gross & Votes").
3. You can then choose to "Add chart to new dashboard" (give the new dashboard a name) or "Add chart to existing dashboard."
4. Click Save & Add.