Manage your tests using Google Spreadsheet

I have been working as a Test Engineer for a decade and used many test management tools which consist of lots of handy features for managing tests but have you tried to manage your tests using Google Spreadsheet or excel sheet? If no then you are in the right place to give it a try. This article has been designed for the fresher, beginner, or who wants to learn and manage tests using Google spreadsheets.

At the end of this article, you will be familiar with the following

• Manage Features, Test Scenarios, and Test cases
• Hide rows/columns and spreadsheet
• Spreadsheet Charts
• Data Validation
• Conditional Format
• Spreadsheet in-built Function and Usage

Q. Why do we need to manage tests in the spreadsheet although there are lots of tools available in the market?.

This is a very common and obvious question. In my opinion, the following are some reasons to answer this question

1. It’s free
2. Easy to create tests
3. Sheets can share easily with peers
4. Sheets can attach easily to the emails
5. Easy to configure charts in the tests
6. Easy to manage access permission
7. Easy to extend functional library using Script Editor feature

Ok! too much talked, Let’s get straight to the point.

Let’s create two spreadsheets in Google drive for managing Features, Test Scenarios, and Test cases

1. Feature Test Management | This spreadsheet is used to manage all features and overall feature statistics
2. Test Scenarios | This spreadsheet is used to create test scenarios, test cases, and tests statistics

Feature Test Management

Let’s create a sheet called “Feature_List” in the “Feature Test Management” spreadsheet, add a feature table, and leave space for the chart. The sheet looks similar to the image below.

Feature_List Sheet

Let’s delete an unused column, rows, and background color.

Delete columns |

• Click on the column from where you want to start deleting and press Command/Ctrl + Shift + Right Arrow (→) to select all columns from the left to right.
• Right clicked and hit “Delete Column G-Z” (G is my start point and Z is the endpoint)

Delete rows |

• Click on the row from where you want to start deleting and press Command/Ctrl + Shift + Bottom Arrow to select all rows from the top to bottom.
• Right-click and hit the “Delete Row 24 - 999” (24 is my start point and 999 is my endpoint.

For now, let’s add dummy data in the table and, add a chart. Now the sheet turns like the image below

Let’s create one more sheet called config, this sheet contains a list of Assignee, Test Status, execution mode.

Perfect!

Now, Let’s create another spreadsheet for test scenarios called “Login Test Scenarios” and link it to the feature list so you can access the test scenarios spreadsheet from the feature spreadsheet.

I have added 2 sheets in the “Login Test Scenarios” spreadsheet; one is used to list out all scenarios and another one is used for test cases.

Login Scenario (Login Test Suites)

Login Test cases

Data Validation

Since config data are in another spreadsheet(Feature Test Management → Config), therefore, let's create a Config_Ref sheet in the “Login Scenario” spreadsheet and import data from the “Feature Test Management -> Config” sheet.

IMPORTRANGE | is an in-build function in the google spreadsheet that helps to import a range of cells from a specific spreadsheet.

In our case, we want to import Assignee, Test status, and Automation mode so I called IMPORTRANGE function 3 times in the Config_Ref sheet.

• Import Assignee Range | =IMPORTRANGE(“<URL>”, “Config!B4:B”)
• Import Staus Range | =IMPORTRANGE(“<URL>", “Config!E4:E”)
• Import Mode Range | =IMPORTRANGE(“<URL>", “Config!H4:H”)

Let’s add data validation so we can select Assignee, Test status, and automation mode instead of write.

Select a column, right-click and hit “Data Validation” Options, Data validation dialog appeared.

Enter ‘Login Scenarios’!C2:C in Cell range, select “List from a range” and specify the range ‘Config_Ref’A2:A

Data Validation

You now can select the QA assignee from the drop-down if data validation configured correctly. Do the same for Status and automation mode(I will skip in this article)

The result after data validation

Conditional Format

Conditional Format is nothing but config a format when a specific condition satisfy. For Instance, A cell background-color should be green when a test mark passed and red when a test marked failed.

Let’s add a conditional format!

Select a column, right-click and hit “Conditional format” options, Conditional format dialog appeared on the right side.

• Apply to range
• Format cell if
• Color

I added a conditional format for Passed status, do the same for the Failed, In_Testing, or Not_Started.

Test Statistics

Now it's time to collect real data or the data should be refreshed automatically when the status change in the Login scenarios spreadsheet.

I used in-build functions in order to get data from other spreadsheets.

IMPORTRANGE | Imports a range of cells from a specified spreadsheet

COUNTIF | Returns a conditional count across a range

COUNTA | Returns the number of values in a dataset.

For Example:

=COUNTIF(IMPORTRANGE(“<URL>”, “Login Scenarios!E2:E”), “Passed”)

=COUNTA(IMPORTRANGE(“<”URL>”, “Login Scenarios!E2:E”))

Note: 
IMPORTRANGE function may require access permission to import data range from another spreadsheet, therefore, call IMPORTRANGE function somewhere in the sheet and grant the permission and then use in the other function

You can refer to the demo spreadsheet from Feature Test Management and Login Scenarios. Note that the drop-down will not work due to read-only permission. You can clone both sheets for any modification