Create and Embed Dashboard with Google Sheets data | Bold BI

Create and Embed Dashboard with Google Sheets data

Embedded analytics help you integrate analytical capabilities within your business applications. Bold BI Embedded helps you achieve this easily, so you can maintain your website and analytical processes in one place and increase your business productivity. In this blog, we look at how to create and embed a dashboard using Google Sheets data with live updates. Using this blog, you can learn how to convert your business data stored in Google Sheets into meaningful visualizations to make business decisions effectively. Blog contents:

How Google Sheets data is represented

Google Sheets can be accessed through your personal Gmail account and is a free online spreadsheet workspace for personal or business use. The Google Sheets data structure is similar to Excel in that you can maintain a formatted document. Here is one such view of a document showing how Google Sheets data is represented.

Structured Google Sheets workbook
Structured Google Sheets workbook

How to connect to Google Sheets

Bold BI allows you to connect Google Sheets data from a Google account to the Dashboard Designer. To create a Google Sheets data source from the Dashboard Designer, open the Data Sources configuration panel and select Google Sheets from the Cloud Storage category.

Cloud Storage listing page
Cloud Storage listing page

The Google Sheets connection window opens, as shown in the following image.

Google Sheets configuration window
Google Sheets configuration window

We have to enter our Google account details and sign into the account. Once you’ve configured the Google account, choose your Google account from window as shown in the following picture.

Window for choosing a Google account
Window for choosing a Google account

We can select Google Sheets from the Google account and provide the name of the data source in the configuration window.

Window for choosing a Google Sheets file
Window for choosing a Google Sheets file

After selecting the appropriate Google sheet, it will show the list of worksheets in that Google sheet. The first-row values of your worksheet will be considered column names by default in Bold BI. So, provide unique and meaningful column names in your spreadsheet that will make it easy to identify your data.

Table view
Table view

Choose the desired tables and click Connect. Now your selected data will move to the intermediate database specially reserved for your tenant. Once that’s done, you will be directed to Bold BI’s data source designer. The Google Sheets schema is listed in a tree view on the left side of the editing window.

Data source editing window
Data source editing window

Drag and drop the table you want to analyze and save the data source.

Save the data source
Save the data source

How to integrate Google Sheets data with other data sources

Joining data from multiple data sources is a common requirement for various online applications. To combine multiple data sources, use the Shared Tables panel at the bottom of the data source designer.

Sharing tables—data source editing window
Sharing tables—data source editing window

This pane displays the data sources you have permission to access. By default, the data sources you created previously for this or any other dashboard will be listed here. You can drag them to the canvas, as shown in the image, to integrate them.

Sharing tables—data source editing window
Sharing tables—data source editing window

One example is using data from Google Analytics and Stripe, which is discussed more in the blog post Track Website Conversions and Revenue Together. You can create a new data source by joining the already-created two data sources. Bold BI allows you to join tables in two or more different data sources provided they were extract-mode connections.

Now let’s see how to create a dashboard using Google Sheets data that we connected to Bold BI.

How to create a dashboard using Google Sheets data

Based on the data loaded from Google Sheets, we have defined metrics and KPIs to create a sales analysis dashboard. You can check out this video on how to create a dashboard from scratch in Bold BI. After creating a sales analysis dashboard using Google Sheets data, you can visualize product sales data through the following metrics, tracking sales performance:

  • Total sales
  • Total orders
  • Sales by country
  • Top 10 products by sales
  • Top 10 products by order quantity
  • Product summary
Sales Analysis Dashboard in Bold BI
Sales Analysis Dashboard in Bold BI

This dashboard helps sales teams analyze the sales process periodically and measure sales performance, which helps them make business decisions.

How to schedule data refreshes for a Google Sheets dashboard

You can schedule data refreshes in the dashboard to keep your data up to date. With a Google Sheets file, use the Refresh Settings option to create a schedule to move data from your cloud location to an intermediate database location.

Data source editing page—refresh settings
Data source editing page—refresh settings

You can add this settings refresh while connecting Google Sheets itself.

Refresh Settings—while connecting to Bold BI
Refresh Settings—while connecting to Bold BI

Once Refresh Settings is clicked, you can view the following configuration.

Refresh Setting window
Refresh Setting window

In the Refresh Setting window, you can schedule the data to refresh hourly, daily, weekly, or monthly. This will automatically trigger a call to the API configured in the data source to keep the data up to date. If you do not want to sync your new data, disable the Enabled toggle.

Disabled refresh settings
Disabled refresh settings

After the refresh is scheduled for the sales analysis dashboard, you can see the data updated in the dashboard every five minutes, as shown in the following image.

Sales Analysis Dashboard—after data update
Sales Analysis Dashboard—after data update

The Sales Analysis Dashboard can be viewed in this demo where it showcases additional metrics.

How to embed a Google Sheets dashboard in your application

Using Bold BI Embedded, you can quickly embed this sales analysis dashboard and avoid building an analytics or BI solution yourself, saving development time. Bold BI Embedded has various features and customizations, which you can see with examples in our sample browser.

Here, we have integrated the Sales Analysis Dashboard into an ASP.NET Core application. You can get details on how to integrate dashboards with the embedded SDK into ASP.NET MVC or ASP.NET Core applications in this blog post.

Sales Analysis Dashboard—embedded view from Bold BI
Sales Analysis Dashboard—embedded view from Bold BI

Also, you can go through the help documentation to embed your dashboards into any server-side application including for Angular, Ruby on Rails, React with ASP.NET Core, React with Go, and many more.

Conclusion

We hope this article guides you in preparing Google Sheets data, building a dashboard using it with Bold BI, and embedding the dashboard into any web application. If you have any questions, please post them in the comments section below. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.

Leave a Reply

April 29, 2021 6 min read

Share

Access live 30-minute demo with our experts

Related Blogs

Up arrow icon
Live Chat Icon For mobile
Hugo Morris

We are here. Let’s chat!

Live Chat Icon