Data Driven API Testing with Katalon using Spreadsheet Data

In expanding my use of Katalon Studio, I have started working on automated API testing. Like Postman, you can set up a manual API test to confirm a request/response combination. But it's possible to take that further and not only send multiple API requests, but validate the response.

For a really good tutorial on putting together the API test, take a look at this video. It steps through creating the API object and configuring parameters.

Katalon Studio API Testing

Following up from that, the code listed below creates a data driven API test wherein it reads data from an XLSX spreadsheeting using the POI model.

The purpose of the test is to send a proper request for an inventory item and confirm the price of the item is correct. The spreadsheet contains the inventory data and the price of the item.

Additionally, the test records the Response Time and the Status Code of the request. Within Postman this would display as 398 ms 200 OK.

The request is built using data from the spreadsheet and is sent to the API object using the WS.sendRequest command.

Determining the number of rows in the spreadsheet is handled by:
int rowCount = sheet.getLastRowNum()

Reading data from the spreadsheet is done by reading a cell value and assigning it to a variable:
int customer_number=sheet.getRow(loop).getCell(0).getNumericCellValue()
String uom=sheet.getRow(loop).getCell(3).getStringCellValue();

The response time is recorded as:
api_response_time=api_request.getWaitingTime()

The status of the response is:
api_request.getStatusCode()

The response is parsed using the JsonSlurper
def api_response = slurper.parseText(api_request.getResponseBodyContent())

The different response items are found using the following method:
String itemPrice = api_response.pricing.tiers[0].price

Parsing the JSON correctly took a couple of tries as I was using pricing.tiers.price to traverse the hierarchy rather than seeing it as an array and using tiers[0].price. Using a site like http://jsonparseronline.com/ got things sorted out.

For output, the test records several pieces of information:
The total number of requests
The number of pricing errors along with status code errors
A finally tally of the success rate

Using the code below, 100 requests can be processed in less than 20 seconds
1500 items can be processed in 5 minutes

This test will continue to expand to validate more data points, but at the moment, it's a solid gauge of whether the API is working, the response time, and accuracy.

import groovy.json.JsonSlurper as JsonSlurper

import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import com.kms.katalon.core.logging.KeywordLogger as KeywordLogger

import groovy.time.TimeCategory 
import groovy.time.TimeDuration

KeywordLogger log = new KeywordLogger()

def timeStart = new Date()
int responseTimeError, pricingError, statusCodeError, priceAvailableError, messageFailureError=0
int api_response_time
float successRate

def slurper = new JsonSlurper()

FileInputStream file = new FileInputStream ("//Postman Testing//Data Files//pricing.xlsx")
XSSFWorkbook workbook = new XSSFWorkbook(file);
//XSSFSheet sheet = workbook.getSheetAt(1);
XSSFSheet sheet = workbook.getSheet("pricing-100-items");
int rowCount = sheet.getLastRowNum()

'Read data from excel'
for (loop = 1; loop <=rowCount; loop++) {
    //Assign spreadsheet columns to variables
    int customer_number=sheet.getRow(loop).getCell(0).getNumericCellValue()
    int branch_number=sheet.getRow(loop).getCell(1).getNumericCellValue()
    String tier_code=sheet.getRow(loop).getCell(2).getStringCellValue();
    String uom=sheet.getRow(loop).getCell(3).getStringCellValue();
    String item_price=sheet.getRow(loop).getCell(4).getNumericCellValue();
    
    //Send API Request
    api_request = WS.sendRequest(findTestObject('Pricing Request', 
        [('customer_number') : customer_number, 
        ('branch_number') : branch_number, 
        ('tier_code') : tier_code, 
        ('uom') : uom]))
    
    //Store Response time
    api_response_time=api_request.getWaitingTime()
    
    //If the Status of the request is 200 (OK) process the response
    if (api_request.getStatusCode()==200){
        
        def api_response = slurper.parseText(api_request.getResponseBodyContent())
        //Store the item price
        String itemPrice = api_response.pricing.tiers[0].price
        //Verify there is no failure message
        String failure_message=api_response.pricing.tiers[0].failure_messages
        if (failure_message!='[]'){
            log.logError("ERROR: Failure Message on pricing request " + loop + " " + failure_message)
            messageFailureError++
        }
        
        //Verify that Price Available is true within the response
        String price_available = api_response.pricing.tiers[0].price_available
        if (price_available!="true"){
            log.logError("ERROR: Price Available error on pricing request " + loop + " " + price_available)
            priceAvailableError++
        }
        
        //Display pricing details
        log.logWarning('Tier Code: ' + tier_code + ' <---> ' + 'Item Price: ' + itemPrice + ' <---> ' + 'Expected Price: ' + item_price + ' <---> ' + 'Response time: ' + api_response_time)
        if (itemPrice!=item_price){
            log.logError("ERROR: The returned price for request " + loop + " does not match the expected price")
            pricingError++
        }
    } else {
        log.logError("ERROR: There was an error with the pricing request " + loop + ". Error code: " + api_request.getStatusCode())
        statusCodeError++
    }
    
    if (api_response_time>10000){
        log.logError("The server response time is higher than expected with a time of: " + api_response_time)
        responseTimeError++
    }
}

def timeStop = new Date()
TimeDuration duration = TimeCategory.minus(timeStop, timeStart)

log.logWarning("<--- API Pricing Request Results --->")
log.logWarning(rowCount + " items priced:")
log.logWarning("There were " + responseTimeError + " requests with a higher than average response time")
log.logWarning("There were " + pricingError + " requests with a different price than expected")
log.logWarning("There were " + statusCodeError + " requests with an unexpected status code")
log.logWarning("There were " + priceAvailableError + " requests with a Price Available error")
log.logWarning("There were " + messageFailureError + " requests with a Message Failure error")
successRate = ((rowCount - (Integer.valueOf(pricingError) + Integer.valueOf(statusCodeError))) / rowCount) * 100
log.logWarning("Success rate: " + successRate.round(2) +"%" )
log.logWarning("Execution Time: " + duration)

file.close();

Other articles of interest:

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Comments
  • How To Disable the Quicken Registration Prompt (30)
    • David: A BIG Thank you! I regularly reinstall Windows OS’s using different hardware configs. In short, Quicken 2011 (it’s March of 2020 as I write this) has been a program I’ve been very happy with and continue to use....
    • Karen L: Today I rang Reckon Australia again, got a different guy and he talked me through the process of getting me a new product key to enter since I already had the licence and it had been extended. Reckon have only extended it by 1...
    • Karen L: I have Australian Quicken 2008 on Windows 8.1. It keeps asking me to renew my licence which Reckon did for me however the activation won’t work. I tried this to disable the registration prompt however it did not stop the...
  • Parsing Strings in Katalon – Split, Substring and Readlines (6)
    • Don Pedro: String tempText=”Date Month January. Revenue $1,355,721.00″ println(tempText.split(“ \$”)[1]) You need to escape the $ with \$ The [1] on the split captures characters on the right of the split [0]...
    • Prasad: Hi, I need to get the substring from the below string ‘Date Month January. Revenue $1,355,721.00’ I want to extract text after $ value. Please help Thank you
  • Working with Dates and Date Formatting in Katalon Studio (15)
    • Sangeethaa: How to get previous day’s date(Yesterday’s date)? Actually I was using today.previous() before,it was working fine till today morning.Now its not retrieving right date. Can anyone please guide me on this.
    • Don Pedro: That could be a little tricky. First, is the text/format always going to be the same? You will need to do some parsing. Separate the first but of text at 2019. After that, split the text again to get rid of Central Standard...
    • Don Pedro: It should be of the same Date type as today. You could then do some calendar trickery with Calendar /*Calendar c = Calendar.getInstance(); //c.set(Calendar.MONTH, 1) //Set the month, 0=January, 11=December println...
    • josh: I’m testing a page with a string like “Updated November 21, 2019 16:25:32 PM Central Standard Time.” How might I capture the date and time and compare it to current time to verify that the update time was within...
    • zakir hussain: today = new Date() lastWeek = (today – 1.week) can u please help me in understanding what is the return type of lastweek? and I wanted to set nextyear date how can i do it
  • Output status messages and test information by writing to the Log File Viewer in Katalon Studio (6)
    • Saranya: Good One. Could you plz share link to access all your katalon related blogs. ThankQ
    • Rodrigo Calabretta: I’m using the KeywordUtil.markFailed or KeywordUtil.markError and my test stops is being shown as Error and If I use in the @afterTestCase the testCaseContext.getTestCaseSta tus() to show the status test case...
  • Setting up Environment Profiles in Katalon Studio (2)
    • Don Pedro: Not quite sure I understand. The term “Execution Profile” is normally used now, but they are still GlobalVariables and are created in the same way for version 7 as previous. You make a new execution profile, then...
  • Here is my drive cluster (2)
    • Don Pedro: While I still have those drives, the drive cluster looks nothing like that now. Almost all of my external drives have had the cases removed and they have been installed into a series of MediaSonic 8 Bay Drive Enclosures. I...
    • Marin Boucher: Hi! Verry funny blog with “go to the point” on many post! I spent good time reading some of your post. Regarding this one about NAS, “many NAS” in fact, I would be curious to see a screenshot of...