Swagger + Excel Sheet - a wonderful way of Automated API Testing

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.