How to Build a Free, Scalable API Testing Solution With Microsoft Excel

One of the most overlooked steps in designing, developing, and maintaining APIs has to do with testing them for data consistency, response times, and availability. Automated API testing is very useful for achieving testing objectives during the API software development lifecycle, and also when the APIs are out in the wild in production. When it comes to testing APIs throughout their normal lifecycle, there are a variety of approaches that API providers can take. They could manually write code that makes the specific API calls, or run manual API executions using a REST client like POSTman. Another options is that they could write their own code using CURL with bash scripts or Python. There are no shortage of ways to develop your own API testing Framework. And more recently, given how the need for API testing has given rise to companies like API Science and Runscope, API providers can turn to a third party solution. One other approach is to develop your own testing framework as I have done with tools that already exist on every Windows desktop that has Microsoft Excel installed. Microsoft Excel?! Say what?! The big advantage of this approach is that it scales relatively easily to manage hundreds of tests (try to manage hundreds of tests using a Web-based solution, you will see what I mean), relies on a product that is installed on millions of computers (let's face it, Excel is everywhere, and we depend on it more than we realize or like to acknowledge), and costs nothing (compared to a commercial solution).

In this article, I'm going to show you how to continually test your APIs using a framework I developed called

webtesting api is a data-driven, automated API testing solution that allows developers and testers to create automated tests for JSON Web Services. uses Microsoft Excel as the Front-end client to manage the automated test scripts, identify expected results, capture the actual results, log API response times, and track historical test metrics for each test cycle. The JSON Web Services Automated Testing solution is free to download, and use. Click here to download.

  • Doesn't require any coding experience
  • Supports the test early and often software engineering tenet
  • Reduces the number of manual tests while increasing test accuracy and reliability
  • Promotes the creation of quality test data and reusable test scripts
  • Keeps test scripts, test results, and test metrics in one place
  • Automates QA test metrics collection and reporting

Creating An Automated API Test with

The following tutorial will create an automated API test that verifies a calculated subtotal value for an import duties and taxes JSON-based Web Service. The Documentation for this API is available here. The documentation is helpful to understand the API Target Endpoint URL, the required JSON elements for both the Web Service's Request and Response representations.

To get started, download the JSON API Automated Testing software. Click here to download.

Next, open the software in the same manner you would open any Microsoft Excel Spreadsheet. Enable macros for this spreadsheet, if prompted.

Once the spreadsheet is opened, create a test case called Landed Cost API - Sub Total Test, and then enter this value in the Test Name cell. Explanation: As with any test case in any test plan, you should uniquely label your automated tests for reference purposes.

webtesting test name

Enter in the Target URL cell. Explanation: Each automated test will have a testable target API endpoint. In order for you to properly test your APIs, you will need to know its URL, otherwise you will be unable to test it. Your API documentation should clearly identify the target API endpoints, which will help expose the available API URLs that should be tested.

webtesting target URL

Enter POST in the HTTP VERB cell. Explanation: Two commonly used HTTP Methods applied during a typical HTTP Request-Response exchange are POST and GET. POST submits data to be processed by the Web Service (such as our tutorial here). GET requests data from the Web Service.

webtesting http verb

Enter the following code snippet into the JSON Document ( HTTP POST Only) cell. Explanation: This is the data required to satisfy this Web Service's incoming data requirements. ( Additional datails: This JSON data contains the user_id and password values of a test account. We have written permission to use this account, and post this information publicly.)

"merchant":"demo_merchant", "cart":[
"name":" Boys Shirt",
"product_id":"731056", "price":"23.99",
"category":"Button-Up", "department":"Kid’s Casual Shirt", "attributes":"Cotton",
"weight":"1", "country_of_origin":"US"
"name":"Kid’s Backpack",
"product_id":"1171547", "price":"20.39",
"quantity":"1", "category":"Backpacks", "department":" Street Gear Bags ",
"weight":"1", "country_of_origin":"US"

After entering the above JSON Document code snippet, the worksheet will look like the following:

webtesting json document


Be sure to read the next Testing article: Runscope Simplifies API Test Management With New Import/Export Functionality