How to build a test results dashboard in Google Sheets

How to build a test results dashboard in Google Sheets

When it comes to programming, people sometimes forget about simple and quick solutions that can make life a lot easier. I would remind you not to underestimate the power of Excel! In this article, we will build a small dashboard with regression test results for Android, iOS and API and a list of bugs found during testing.

Calculations

Test Suite

We will start our Google spreadsheet journey with the calculations tab. Our solution will be divided into separate Android, iOS and API tabs. Each contains a regression tests list (Figure 1). Each test’s execution has its own app version number.

In the [Bugs] column, we will store bugs found during regression. Bugs will have a defined pattern  [“Severity”] JIRA-XXXX. “Severity” will be then replaced by 5 available options (MI, MA, TR, BL, CR) which correspond to Minor, Major, Trivial, Blocker and Critical respectively.

Test Suite: How to build a test results dashboard in Google Sheets?
Figure 1. Test Suite

Calculations

Below the Test Suite, we will make some calculations for the dashboard (Figure 2). Starting with test results per version and the total number of tests executed. For the sake of simplicity of the dashboard, the “Executed” means Pass+Fail excluding None. 

  • “Fail” – using formula: =COUNTIF(C3:C11, “*Fail*”) etc.
  • “Executed” – using formula: =sum(C13:C14) 

Next we will calculate the number of bugs by severity and version: 

  • “Total Bugs” – using formula: =COUNTIF(F3:F11, “*JIRA*”)
  • “Critical” – using formula: =COUNTIF(D3:D11, “*[CR]*”) etc.

We then sum all bugs (by severity and total) in the last column.

Test Suite: How to build a test results dashboard in Google Sheets?
Figure 2. Tests Results

Dashboard

Executed Tests

The first section contains a number of tests executed from the last build (Figure 3). We use the “Executed” field to plot values.

Number of tests executed.
Figure 3. Number of tests executed

Bugs by severity

The next section contains the number of bugs by severity (Figure 4). We use values of the last column with summed bugs: Critical, Major, Minor etc. The plots have defined colors for each severity of the bug.

Test Suite: Bugs Severity
Figure 4. Bugs by severity

Bugs per version

The last section contains the number of bugs per version (Figure 5). We take the values of the “Total Bugs” field counted for each app version. 

Number of bugs per vision
Figure 5. Number of bugs per version

The result

The complete dashboard is displayed in Figure 6. It took 2-3 hours with little Google spreadsheet knowledge. The Google spreadsheet is then converted to an html link and is made accessible to “invited” people. The html link contains the dashboard and all tabs we want to display.

Complete dashboard - Google Sheets
Figure 6. Complete dashboard

Top AI innovations delivered monthly!

The administrator of your personal data is Miquido sp. z o.o. sp.k., with its ... registered office in Kraków at Zabłocie 43A, 30 - 701. We process the provided information in order to send you a newsletter. The basis for processing of your data is your consent and Miquido’s legitimate interest. You may withdraw your consent at any time by contacting us at marketing@miquido.com. You have the right to object, the right to access your data, the right to request rectification, deletion or restriction of data processing. For detailed information on the processing of your personal data, please see Privacy Policy.

Show more
Click me
Written by:

Przemysław Jagodziński

Senior Developer in Test Przemysław is a Senior Software Developer in Test at Miquido, dedicated to ensuring the highest quality in every project. He meticulously designs and implements testing strategies to guarantee flawless performance. Przemysław is committed to continuous improvement and excellence in software quality assurance.
Click me

The administrator of your personal data is Miquido sp. z o.o. sp.k.,... with its registered office in Kraków at Zabłocie 43A, 30 - 701. We process the provided information in order to send you a newsletter. The basis for processing of your data is your consent and Miquido’s legitimate interest. You may withdraw your consent at any time by contacting us at marketing@miquido.com. You have the right to object, the right to access your data, the right to request rectification, deletion or restriction of data processing. For detailed information on the processing of your personal data, please see Privacy Policy.

Show more