Google Sheets - for Storing and Working with Test Cases

Test management is an important question for any QA team - very often, one needs to store not only test cases themselves, but also the results of their execution. At that, all participants should be able to work with them in parallel. And, creating detailed reporting on the performed testing should be available indeed. SmartBear Software provides a very powerful tool solving these tasks - QAComplete.

 

And, I'm here to share the concept of work with Google Sheets that any entry-level QA engineer can use seeking a convenient and reliable tool for storing and working with test cases. With this tool, you will easily start using Google Sheets as a test management system for manual testing.

 

Why Google Sheets

 Let's consider why one can ever have an idea to use Google Sheets as a test management system.

If we compare them with other test management systems, they have a number of advantages.

  • Easy and clear access administration.
  • General availability and reputation.
  • Available for use on any device.
  • Requiring minimal JavaScript knowledge.
  • Flexible in data representation.

Assume one of the criteria turned the scale and you have decided to use Google Sheets for manual testing of your application. What to start with?

Let's consider a small example. It shows how you can store your test cases, get and analyze work results, and even configure synchronization with an issue tracker.

Here, you can find the ready-to-use google sheets we will be considering:

https://docs.google.com/spreadsheets/d/1MXVakA5kTod_KGzGz1MKc6KpOCiYv1MR3_tqgUXLTqw

https://docs.google.com/spreadsheets/d/14dljNcGYyVblSktUt6g7K1wgp5zACh2wA4o658BuiUw

Use the "Make a copy" option to get full access to the documents.

Now, let's have a closer look into the steps to create the provided google sheets.

 

Test application and list of test cases

For starters, let's take a simple application - Calculator. To make our example even simpler, let's set only two areas for testing: text field and arithmetic operations.

01.png

And, we will take several features for each area: the Copy and Paste features for the first area, and addition, subtraction, multiplication, and division for the second one.

Now, make a list of test cases.

Enter the testing areas and features into the information columns "Product Area" and "Feature". You will also need the following columns:

  • Assignee - a person performing the functionality check.
  • Test Cases - links to test cases, that is, the description of test cases used for the check.
  • Status - successful or not (pass/fail).
  • Issues - links to revealed issues. In case a check failed, you can specify the found bugs here.

02.png

 

 

Assignee

Let's consider each column in more detail.

It is a good practice to keep the list of people who perform checks in a separate sheet, and to make it hidden. Any change of this list will affect the whole document.

The Data | Data Validation dialog will help us use this list. The "А2:А" formula shows that it will validate all the A column cells containing a value, starting from the second one.

03.png

Data Validation will create a drop-down list with the assignees in each cell.

04.png

During the work, one will select his/her name from the drop-down list so that others could see who is working on this step.

 

Test Cases: limitation

The next column is Test Cases.

Google Sheets have a limitation of 2 million cells per document, that's why it is better to store test cases in separate documents not to face this limitation in an inappropriate moment.

05.png

 

Test Cases

Let's elaborate on an example of a document with test cases.

I suggest making three columns: Test Case, Steps, and Status. If needed, you can add a column showing whether a test case is covered by an autotest for example.

06.png

Using the =COUNTA(A2:A) formula, you can count the number of test cases in the first cell of the first column. And, using the 'Format | Number | More Formats | Custom number formats…' dialog, you can insert the number into the column header. Now, the number of test cases is prominently displayed.

07.png

For better visibility, it would make sense to highlight data in the Status column. Use the Format | Conditional formatting… option for this. There, you need to specify the whole column as a range, and, depending on the cell text, highlight it in green or red.

 

Test Cases: automated notifications

Google Sheets have scripts. With them, you can configure email notifications when issues are revealed during the testing. Use the 'onEdit' event for it. In the event handler, we check the behavior - if the Fail value is entered in the Status column, a notification should be sent. Here, in the email, the name of the test case where the error was found is specified. 

/*
  Sends an email message.
*/
function sendAlertEmail(sTestCaseName) {
  MailApp.sendEmail({
    to       : "someone@example.com",
    subject  : "An issue was found when checking the \"" + sTestCaseName + "\" scenario",
    htmlBody : "This is an automated message please do not reply.",
    name     : 'Automatic Notification'
  })
}

/*
  The "onEdit" event is triggered when a user changes the value of any cell in a spreadsheet.
*/
function onEditInstallableTrigger(e) {
  var nHeaderRowNumber        = 1;
  var nTestCaseColumnNumber   = 1;
  var nTestStatusColumnNumber = 3;
  var nRangeRow = e.range.getRow();
  var sTestCaseName; 

  if ((nRangeRow !== nHeaderRowNumber) &&
      (e.range.getColumn() === nTestStatusColumnNumber) &&
      (e.range.getDisplayValue() === "Fail")) {
    sTestCaseName = SpreadsheetApp.getActive().getSheetByName("Test Cases").
                    getRange(nRangeRow, nTestCaseColumnNumber).getDisplayValue();
    sendAlertEmail(sTestCaseName);   
  } 
}

You can also specify any other information: the link to a document, the name of a tester, the link to an issue revealed. All the information entered in the table is available in scripts.

 

 

Test Cases: statistics

The hidden sheet has current statistics automatically counted: the number of passed and failed test cases, execution progress and results.

08.png

  • The number of completed test cases is calculated by counting the number of the "Pass" or "Fail" words with IF and SUM functions:
     =ARRAYFORMULA(SUM(IF('Test Cases'!C2:C="Pass",1,0)))
     =ARRAYFORMULA(SUM(IF('Test Cases'!C2:C="Fail",1,0)))
  • Progress is the ratio of the number of completed test cases to the total number of test cases, which is stored in the A1 cell in the first sheet: 
 =(B1+B2)/ 'Test Cases'!A1
  • The status in this case is defined in the following way: if at least one test case has the Fail status, the overall status is also Fail:
=IF(B2=0,"Pass","Fail")

 The created and configured document with test cases can be used as a pattern to create similar documents for other features. The documents keep their formatting while being copied and remain shared for the same group of people.

 

 

Test cases: link to document

Let's get back to the main document.

It is a good practice to store the Paths to documents with test cases in an individual column as we are going to use them several times. You can make the column hidden not to confuse users.

09.png

Insert the link to this document in the Test Cases column itself using this formula:

=HYPERLINK(G2,"Link")

10.png

 

 Status

The next column is Status.

As the test case completion status is already defined in the separate document mentioned above, we only need to copy it to the main document now. For this, the ImportRange function is used. It works pretty fast, even if a sheet contains several thousand calls of it.

11.png

Here, you can make any cosmetic adjustments. For example, for the functionality being currently tested, display "In progress".

 

Issues

The latest column is Issues.

It has links to issues in the issue tracking system. In this case, for example, these are the links to the documents in Jira. But, I think you can use any other issue trackers.

12.png

It is notable that, with this formula, you can implement viewing of all the issues listed in the column.

=HYPERLINK(CONCATENATE("https://calculator.atlassian.net/issues/?jql=key%20in%20(", JOIN(", ", UNIQUE(TRANSPOSE(ARRAYFORMULA(mid(SPLIT(JOIN(CHAR(10),filter(F2:F,F2:F<>"")), CHAR(10)),41,100))))), ")"), "Issues")

 The formula goes through all the column cells and selects issue IDs from text. If one cell has several issues listed, which happens rather often, it will select them all.

 

The formula execution result is the link, where, in brackets, all the found IDs are listed:

https://calculator.atlassian.net/issues/?jql=key%20in%20(ISSUE-50,%20ISSUE-51)

This link leads to the page of the issue tracker, where all these issues are presented.

 

Data Filtering

Sometimes, it is useful to filter data in the table. For this, use the Data | Filter views… option. Using it, a tester can view what functionality is assigned to him/her.

13.png

One can also see all the failed tests or all tests with issues revealed.

The filters are kept in the document, so they can be created once and used further by all the participants.

 

Summary statistics

It is a good practice to keep all the summary statistics for all test cases at hand, on a separate page. You can gather any data. Here, as an example, we are counting the number of features, the progress of execution, and the percentage of successful execution. 

14.png

The calculation is performed by the COUNTIF function. Here, other functionality is used intentionally, to show that one and the same task can be solved in different ways. It counts the number of elements in the specified range, for which a special condition is met. For example, COUNTIF('Test Suite'!B2:B, "<>") counts the amount of non-empty cells in column B.

Number of features:

=COUNTIF('Test Suite'!B2:B, "<>")

 Progress:

=(B1 - COUNTIF('Test Suite'!E2:E, "In Progress"))/B1 

The percentage of the passed items:

=COUNTIF('Test Suite'!E2:E,"Pass")/B1 

 

For better visibility, we can create a chart or a diagram. For example, the diagram showing the percentage of successful test cases, failed and remaining test cases.

 

Publishing Results

There is a possibility of uploading a document or its part on the Internet as a web page. This is convenient when reporting to the management.

15.png

For example, you can upload statistics along with the above-mentioned diagram.

16.png

Test execution progress

Let's consider other data we can gather.

For example, manually, or using simple automation, you can store the test execution history by dates.

In the table in the picture, on March 1, the test was 10% completed. On March 2, by the end of the day - 35% completed, and so on.

Based on the saved history, you can create, for example, a test execution chart and compare it with the one expected or ideal.

17.png

For automation purposes, in this case, it would be convenient to set the script triggering by timer. For example, every day at 22:00, when all the works for the day are completed for sure, adding a row with the progress to the table.

 

Test execution history

In a similar way, you can save the test execution history.

For example, on the 1.0.1 build, tests were successfully completed by 30%. On build 1.0.2 - by 60%.

18.png

Here, you can also create and publish random charts and diagrams. For example, the percentage of test execution by builds or dates. This chart helps you monitor how the build quality changes over time.

 

Clearing test statuses

After all the required results of the previous testing have been saved, you can clear data of the Status column in the document with test cases.

After this, you can perform testing one more time, for example, for the next build. You can automate the clearing using the simple script below. 

/*
  Clears test statuses.
*/

function clearTestStatuses() {
  var links = [], i, range, sheet, value;

  // Getting information from the "Link to Test Cases" column.
  sheet = SpreadsheetApp.getActive().getSheetByName("Test Suite");
  range = sheet.getRange("G2:G");

  for (i = 1; i <= range.getHeight(); ++i) {
    value = range.getCell(i, 1).getValue();
    if (value !== "") {
      links.push(value);
    }
  }
 
  // Clearing the "Status" column in all files.
  for (i = 0; i < links.length; ++i) {
    sheet = SpreadsheetApp.openByUrl(links[i] + "/edit");
    range = sheet.getRange("C2:C");
    range.setValue("");
    Logger.log("The '" + sheet.getName() + "' document has been processed.");
  }
}

 

 The script goes through all the documents, which are specified in the column with the paths to test cases, one by one and clears the Status column in these sheets.

 

I would like to note that Google Sheets keep the history of changes. And, if anything was changed or accidentally deleted, the lost data can be recovered.

 

Synchronization with issue tracker

Finally, using scripts, we can also implement issue synchronization in our sheet with issues in an issue tracker. For example, delete closed issues from the Issues column.

The script in this case is more complex.

Probably, if you face this task, you would better start thinking of moving to some specific software with which this task can be solved out-of-the-box. For example, you can use QAComplete https://support.smartbear.com/qacomplete/docs/administrator/integration/jira/index.html .

In any case, this sample script will be useful to get the issue status in Jira.

/*
  Gets a status of an issue with the specified key.
*/
function getIssueStatus() {
  var user = "user@calculator.com";
  var password = "secret_password";

  var options = {
    'method' : 'get',
    'contentType' : 'application/json',
    'headers' : {
      'Authorization' : 'Basic ' + Utilities.base64Encode(user + ':' + password)
    }
  }

  var response = UrlFetchApp.fetch("https://calculator.atlassian.net/rest/api/latest/issue/ISSUE-50?fields=status", options);
  Logger.log(JSON.parse(response).fields.status.name);
}

 

Summary

The article covers the ways to use Google Sheets for storing and working on test cases.

It shows that, with little programming skills, one can create a flexible table according to specific requirements.

Also, it has some workflow examples, none of which is final and can be refined and adapted to your needs.

Staff
Users online (227)
New Here?
Join us and watch the welcome video: