Ask a Question

How to get pass/fail status of groovy scripting in excel sheet

New Contributor

How to get pass/fail status of groovy scripting in excel sheet

Hello Everyone,

I am taking data from excel sheet for that I am using data source. I have written groovy script for asserting response. I want get status of groovy scripts status as pass or fail and I need to pass that status into the same excel sheet.

Please help me with solution

Champion Level 3

In general, there are no explicit assertion be added to groovy script test step unlike other test steps.
Of course one can have assertions with in groovy script, but one can't really find the status of groovy script test step and I doubt if there any API available.


Is there any other way by which we can pass pass/fail depends on groovy script status to excel sheet?




I can suggest a test with default reporting.

Create a project with a simple suite, add a test case and add the same script step and try generating report and see if you get the correct status.

Champion Level 2



I do this all the time.


I use Excel datasource for the params to call any given services for a single discrete test.  I use Groovy scripts to run checks (not assertions) on the response in order to work out what has passed or failed for that test.


I then use a datastore step to write out the data.  I do not put the results back into the same Excel spreadsheet as the datasource, this doesn't make sense for future tests runs.  But, using the datastore step, you could pretty much write back out each column from the current row in the datasource as well was well the results from Groovy steps containing assertions.  I do this too.


For the datastore setup, you can even use a groovy step to define the datastore filename.  I do this to give my datastore file a timestamp.


I haven't actually used Excel as a datastore, I use (a kind of) CSV  and define the pipe symbol as my separator.  Can't always trust commas because, y'know, sentences or strings.  I then import these into a nicely formatted spreadsheet that is full of conditional formatting to colour the passes and fails.


Each Groovy script will perform some sort of test.  E.g. customer surname in response matches the expected customer surname in the datasource by using a simple 'if' statement, then return the result. I don't use assertions because the test will fail at that point.  I don't want that.  I'd rather I run tests for every row and then see what has failed.  Some of my data-driven (aka functional tests) contain over 1,000 rows.  If I used assertions I'd have to continually have to stop-fix-retest.  


When setting up the datastore, you can use the nifty 'get data' by right-clicking in the value cell for the datastore prop, simply navigate to the Groovy step and then select Groovy Step result.  What this does is run the check and pull out the return value and shove it in the datastore.  


Sounds complicated, and it can be tricky to set up, so just try and get the flow working for one or two values.  Once you manage this, you'll be flying in no time.


I got your point, But can you please tell me how are you getting groovy script result. Like in custom groovy script properties there are two properties, one is result and other is script. For me I got nothing in result property value. So how are you getting value in result property? And from data store you mean dataSink test step?

Please help me with the solution.








OK, in your test, I'm assuming it looks similar to the following...


Screenshot 2021-09-23 085116.png


So, you have a datasource, where each row is a single 'test'.  When the test runs, SoapUI iterates over each row in the data by calling the service itself, then the Check Surname script, then the Check Forename script.  It loops back using the datasource loop and does this until there are no more rows.


Based on what you initially asked and then making some assumptions, you have assertions in your Groovy script and what you want are the results of the assertions put into Excel.  Assertions are fine to an extent, but if a step fails the test stops.  You know which row fails, but you don't know how many more failures you have until you fix this error and re-run.


In my Groovy scripts, and as mentioned at the start, I do not use assertions in Groovy scripts.  Instead, I return 'PASS', 'FAIL' or 'Not Checked'.


Here's an example of what my Check Surname Groovy script might look like....

// Let's compare expected surname with the surname returned from API.

// Get the expected value form the datasource.
def expectedSurname = context.expand( '${DataSource#SURNAME}' );

// Get the surname returned from the service
def actualSurname= context.expand( '${Some Service Call - REST Request#Response#$[\'message\'][0]}' );

def result;

// Check!
if (expectedSurname.contains(actualSurname)){
    result = 'PASS';
} else {
    result = 'FAIL';

return result;


Now, I want to record the result of this but I also want to record the result of this check for each row in my datasource.  This is where datasinks come into play.


Here's the flow of the test with the Datasink step added.


Screenshot 2021-09-23 091108.png


We're going to use the Datasink to 'record' each iteration.  Note how it is the last step before the end of the loop.  It has to be here as we need to run the service call and the checks before recording them.


Let's have a look at an example Datasink.  Note, I have not configured sink type (file, Excel, db etc.) we're just focussing on how to get certain values.

Screenshot 2021-09-23 091739.png


Each row in the table above shows what columns we want in our output and where to get it from.

When I said you cannot write into your datasource, but you can copy values from the datasource and put then into the datasink, the first two rows show  you how to achieve that.  The $ thing tells SoapUI we're going interpolate some value.  The first part of the $ syntax is the step to refer to; then #, then the value from that step.  You also saw examples in the Groovy scripts earlier.  Rows three and four show how we 'grab' the results of our checks.  The output will look like...




There is no need for custom properties, property transfers on anything like that.


Thank you for this brief answer. I have just one more doubt like in your case you are checking one condition in one groovy script. But in my case I have to check around 20 conditions in a single groovy script. So for that I can't use if else condition for all 20 condition. So how can I write my groovy script.

Please help me with the solution.






I use one Groovy script per condition to test.  In my own main test suite, I have approx. 20 groovy scripts.


I suppose you could return an array from your groovy script where each element is a condition test and then update your datasink step to reference the element of interest.  Not tried, but I imagine it's possible.  You could also return a JSON object from the Groovy script. E.g.

        "surname": "Scholes",
        "forename": "Paul",
        "surnameCheckResult": "PASS",
        "forenameCheckResult": "FAIL"

And then reference the values in the Datasink...  E.g.


Again, not sure if this would work.

Showing results for 
Search instead for 
Did you mean: