Boost your API Test efficiency using swagger and excel sheets

Today, in this post, I will highlight the process of most effective API automation Testing process and benefits of that process. This process makes use of swagger files and excel sheets and combines the power of these powerful tools to make your API testing experience more seamless. I have personally applied this process in various projects and found it to be very effective.

This process can be adopted with any API test automation tool or framework which supports swagger import process and reading test data from excel sheets. For a specific guide about detailed instructions, you may look at my article on Swagger + Excel Sheets, a wonderful way of validating REST APIs.


1. Write API Specification:

The process starts with documenting your API specifications in swagger files. Writing API specification in the beginning is the key to successful API Test Automation. The major benefit is resolving ambiguities among multiple key stakeholders e.g. Testing Team, Backend Team, Frontend Team etc.

2. Make a Test Plan:

At this stage, you may make a test plan on how you are going to validate each API in your swagger file. You may answer the following most basic questions:

  1. What different conditions you would like to validate for each API? This includes positive and negative test cases, boundary value conditions etc. basically defines your API testing scope.
  2. How you are going to setup the initial state? You may setup the initial state using several methods e.g. by restoring the database state from a dump, by invoking an API, by executing an external script or command etc.
  3. What initial state do you need for each API to handle different conditions of the API? Once you have considered all the conditions which you would like to validate for any API then you will need to generate all the application data required to validate for those conditions.

Making a test plan beforehand is also the key factor in successful API test automation. It will provide you an absolute clarity on how you are going to progress further, scope of testing, realistic deadlines etc.

3. Generate Test Logic

Now comes the generation of test logic. Instead of writing test logic manually. We can use the power of swagger files to generate that test logic. Because in swagger file, we have all the information we need to generate the test logic. The same test logic can be reused to validate the different conditions of an API and can be easily synced whenever there are some changes in our Swagger API Spec.

4. Write Test Data

Now, in this stage, we need to write the test data in our excel sheets or CSV files. We should have a separate excel sheet for each API available in our swagger file. And in excel sheet, we can write the test data to validate the different conditions of the API. We can also write, what should be our expected response of the API in that particular condition.


There are several benefits of this process but let me share you some of the key benefits of applying this process. You might have already imagined the benefits of using this approach while reading so far.

1. Removes ambiguity:

As your API specification is documented and your Test Plan is documented in this process. It resolves all the ambiguity among the key stakeholders and provides absolute clarity to them doing validation. Lack of clarity is the major reason behind many automation failures.

2. Increased Test efficiency:

As swagger file becomes the single source of truth for all of the generated test logic. It will increase your test efficiency drastically. The same test logic can reused to validate all the conditions of an API. So, it increases the reusability.

3. Easier to Maintain

Test Cases written using this process are easier to maintain over a period of time. Because whenever there are some changes in your swagger files then you will be able to change the test logic very quickly. You may even write a script that will update your test logic using swagger file automatically. So, your tests and api specification remain in sync all the time.

Maintaining test cases in excel sheets are also easy. Checking conditions of an API is like adding a new row in the excel sheet with desired data.

4. Separation of concerns

This process separates the test data from the automation logic. Anybody can write test data in the excel sheet without needing much technical knowledge. This enables you to write test data very quickly by giving you clear objectives on what to write in excel sheet.

Finally, I would like to know your feedback on this approach. You may share your feedback by giving comments on this post. If you like my post then do like or re-share this post with your friends and colleagues. Do let me know if you foresee any challenges in using this approach. I have personally applied this process in various projects and found it to be very effective.

If you are facing any issues related to API Automated testing in your organization, then do contact us. We will arrange the live meeting to discuss your needs and will also provide you the demo of our product vREST NG showcasing its capabilities.

Swagger + Excel Sheets, a wonderful way of validating REST APIs

Swagger Files (aka OpenAPI Specification) is the most popular way for documenting API specifications and Excel sheet provides an easy and simple way of writing structured data. Anybody can write data in excel sheet irrespective of their programming skills. Introducing vREST NG (An enterprise ready application for Automated API Testing), which combines the power of both to make your API Testing experience more seamless. The approach is also known as Data Driven Testing.

Data Driven testing is an approach in which test data is written separately from the test logic or script.

So, this is how the process looks like:

vREST NG uses swagger files to generate all of the test logic and sample test data CSV files. vREST NG reads test data from the CSV files and iterate over the rows available in the CSV files and run the iterations one by one. Today in this post, we will look at the following in detail:

  1. How you may generate the test cases by using the swagger files.
  2. How you may feed the test data to those generated test cases through an excel sheet.

How to perform Data Driven API Testing in vREST NG

To elaborate the process, I will take a sample test application named as contacts application which provides the CRUD APIs. I will guide you through the following steps:

  1. Setup the test application
  2. Download and Install vREST NG Application
  3. Perform Data Driven API Testing in vREST NG

1. Setup the Test Application:

You may skip this step if you want to follow the instructions for your own test application.

Otherwise, just download the sample Test Application from this repository link. This application is a NodeJS based application and tested with NodeJS v10.16.2.

To setup this application, simply follow the instructions mentioned in the README file of the repository.

2. Download and Install vREST NG Application

Now, simply download the application through vREST NG website and install it. Installation is simpler but if you need OS specific instructions, then you may follow this guide link.

After installation, start the vREST NG Application and use vREST NG Pro version when prompted in order to proceed further.

Now first setup a project by dragging any empty directory from your file system in the vREST NG workspace area. vREST NG will treat it as a project and store all the tests in that directory. For more information on setting up project, please read this guide link.

For quick start, if you don’t want to follow the whole process and just want to see the end result. They may download and add this project directory in vREST NG application directly.

3. Performing Data Driven API Testing in vREST NG

vREST NG provides a quick 3 step process to perform data driven API Testing:

(a) Import the Swagger File

(b) Write Test Data in CSV Files

(c) Setup Environment

Now, we will see these steps in detail:

(a) Import the Swagger File

To import the Swagger file, simply click on the Importer button available in the top left corner of the vREST NG Application.

An import dialog window will open. In this dialog window:

  1. Select “Swagger” as Import Source
  2. Tick the option `Generate Data Driven Tests`. If this option is ticked then vREST NG Importer will generate the data driven test cases for each API spec available in the swagger file.
  3. Provide the swagger file. For this demonstration, I will use the swagger file from the test application repository. Download Swagger File

The dialog window will look something like this. Now, click on the Import button to proceed further.

The import process has done the following things so far:

1. It has generated a test case for each API spec available in the swagger file. And test suites will be generated against each tag available in the swagger file.

2. It has automatically created the sample CSV files against each test case with desired columns according to your swagger file as shown in the following image.

We will discuss in detail on how you may fill this excel sheet later in this post.

3. The generated CSV files are also automatically linked as shown in the following image.

So, before every test execution, the API test will read the data from the linked CSV file and converts it into JSON format and store it in a variable named as data. Now the test case will iterate over the data received and run the iterations. So, if you make a change in CSV file, just run the test case again. Test Case will always pick up the latest state of the CSV file. No need to import again and again.

4. It has automatically inserted some variables in the API request params as per the API definitions available in the swagger file. These variables value will picked up from the linked CSV file automatically.

No alt text provided for this image

5. It has automatically added the response validation logic as well. Status code assertion is used to validate the status code of the API response. Text Body with Default Validator assertion compares the expected response body with the actual response body. Text body with Default Schema Validator assertion validates the API response through the JSON schema.

The expected status code will be picked up from the linked CSV file.

And the expected response body will also be picked up from the linked CSV file.

And the expected schema name is also picked up from the linked CSV file.

6. It has imported all the swagger schema definitions in the Schemas section available in the Configuration tab.

You may refer these schema definitions in the Expected Schema tab as discussed earlier. And in the CSV file, you will need to just specify the respective schema name for the test iterations in the expectedSchema column.

(b) Write Test Data in CSV Files

As we have already seen the data file generated from the import process. Let me show you the generated file again for the Create Contact API:

In this sample file, you may add test data related to various iterations for the Create Contact API. In the iterationSummary column, simply provide the meaningful summary for your iterations. This iteration summary will show up in the Results tab of the vREST NG Application. You will need to fill this test data by yourself. You may even generate this test data through any external script.

Now, let’s add some test iterations in the linked CSV file.

No alt text provided for this image

With the above CSV file, we are checking two test conditions of our Create Contact API:

  1. When the name field is empty
  2. And when the name field length is greater than the limit of 35 characters.

In the above CSV file, we have intentionally left the expectedBody column blank. We don’t need to fill this column. We can fill this column’s value via the vREST NG Application itself.

Before executing the test case, we need to configure the baseURL variable of your test application in the Configuration tab like this:

No alt text provided for this image

Now, let’s execute this test in vREST NG Application. Both the iterations are failed because expected response body doesn’t match with the actual response body as shown in the following image:

No alt text provided for this image

Now, click on button “Copy Actual to Expected” for each iteration. vREST NG will directly copy the actual response body to expectedBody column in the CSV file like this.

Now after this operation, if you look at the CSV file again. You can see that vREST NG has filled the expectedBody column for you as shown in the following image.

No alt text provided for this image

Note: If you have opened this CSV file in Microsoft Excel then you will need to close the file and open it again in order to reflect the changes. But some code editors automatically detect the changes on the file system and reflect it in real time.

Now, if you execute the test again, you can see that the tests are now passing.

No alt text provided for this image

You may also see the expected vs actual response for the selected test iteration:

No alt text provided for this image

And you may see the execution details of the selected iteration by going to Execution Tab:

No alt text provided for this image

So, in this way, you may add iterations in the CSV file. Just add iterations in your CSV file and run it in the vREST NG Application directly. No need to import again and again. It all just work seamlessly. So, it increases your test efficiency drastically.

(c) Setup Environment

For the generated steps, you may also need to set the initial application or DB state before executing your tests. So that you can perform the regressions in automated way. Some use cases of setting up initial state can be:

  1. Restoring the database state from the backups
  2. Execute an external command or script
  3. Invoke a REST API to setup the initial state

In this section, Let’s see, how you may execute an external command before the execution of our tests. As our sample test application is simple and built for demonstrating vREST NG. It stores all the contacts data in a JSON file. So, I already have the initial data in a JSON file which I can copy to our test application project directory before executing the test cases.

You may specify the command as shown in the following image:

No alt text provided for this image

The above command will restore the application state from the initial data which is already there in vREST NG Project directory >> dump.json file.

Note: You will also need to specify the cpCmd variable in the Environments section because in Linux/MacOS the command name is cp and for Windows OS, the command name is copy. So, for Windows OS, you may create another environment in vREST NG application. So that your API tests can run on any machine by just switching the environment.

No alt text provided for this image

So, this is how easy, you may perform the data driven testing in vREST NG. Let me know if you found this post helpful via comments or do like or share it with your friends and colleagues.