Sample API Test Cases in Excel Sheet: Data-Driven Testing

This is lesson 9.7 of Shift-Left API in Depth. In lesson 9.6 you wrote one test's checks in the test workbench; now you run that kind of test against every row of a sheet. For the demo, use the free edition of Shift-Left API and the public sandbox store API.

How do you run sample API test cases in an Excel sheet?

Sample API test cases in an Excel sheet are how a lot of teams keep them: a row per case, with the inputs and the result they expect. The question is how to run that sheet instead of retyping it. In Shift-Left API, you import it as a dataset, bind its columns to a test, and run the test once per row.

What a data-driven test is

A data-driven test is one test, run many times, with different data each time. The request stays the same (create a user); what changes is the row: a name, an email, a role, an age. Four rows means four runs and four results. So a sheet of API test cases examples becomes a test suite, without writing four tests.

The data can come from almost anywhere: a CSV or Excel file; a JSON, YAML or XML file; a saved dataset, which is a table you keep in the project; a database (MySQL, PostgreSQL, SQL Server, SQLite, MongoDB or Oracle) with a query; an API; or a random generator, for names, emails and dates you don't want to type.

How to write API test cases in an Excel sheet that runs

Name the sheet's columns after the request's fields (name, email, role, age) so each column binds to a field. Then add a column for the result, EXPECTED_STATUS: 201 for a row that should pass, 400 for one that should be refused, like an age of 151. The sheet now holds inputs and expected results together, and the test just reads it.

Here is the video's sheet. Copy it into Excel, or use it as an API test case Excel template for your own rows:

Import the sheet as a dataset

The demo project is built from the sandbox store's spec, with one test: Create a user, on POST /api/v1/users, expecting a 201. The sheet comes in through the project's settings.

1. Click Project Settings at the top right of the page.
2. Click the Data for Running Tests tab, then Datasets: the project's tables of test data.
3. Under Import a dataset, type a name, such as Users sheet.
4. Click Choose file and pick the spreadsheet, users.xlsx. The format switches to Excel and the file is loaded.
5. Click Import at the bottom of the box. The dataset appears as version 1, used by no tests yet. Close the settings with the cross at the top right.

Bind the columns and run once per row

1. Click Users in the Features table, then its endpoint, Create a new user.
2. Click the test's name, Create a user, to open it in the test workbench, then click the Data tab.
3. Data-driven testing is off by default. Switch it on with the toggle on the right.
4. Open the Data source list: an internal data table, a saved data source, a saved dataset or a random generator. Choose Saved dataset and pick Users sheet.

The columns bind to the request by name: name, email, role and age go to the body. EXPECTED_STATUS isn't a request field, so it's left unbound for now. Click Preview iterations: four of four rows selected, four iterations. Click Save at the top right of the workbench, then Run 4 iterations. Each row becomes a run on the Shift-Left Engine, against the live sandbox.

Read the row that failed

Click the results banner to open the full report. At the top, Run Triage sums up the failure: one of four tests failed, grouped under one cause. (In the video an AI provider is configured, so the summary is written by AI and its wording varies.) Click it to see the cause: the test expected 201 and got a 400, a validation error. Whether the request or the API is at fault, it can't say yet.

Below it are four runs of the same test: three passed, and number four failed. Click View Details on it. The data row that drove it is row 4 of 4: Gus Moreno, age 151. The status code came back as 400, not the 201 the test expects. The sandbox refused the age, exactly as the sheet said it would.

Let each row carry its expected result

So let the sheet decide.

1. Click Back to editor at the top right to leave the report, then click the Assertions tab.
2. Assertion 1 checks the status code against 201. Delete the value, type two braces, and pick row.EXPECTED_STATUS from the list. Now each row brings its own expected status.
3. Click Run 4 iterations again and open the report from the results banner.

All four pass. Row four expected a 400, and got one.

Try it yourself

Download the sample sheet, users.xlsx (or users.csv), or make your own with the same columns. Import it as a dataset, bind it to a Create a user test on the sandbox store, and run it: which row fails? Then set the status assertion to {{row.EXPECTED_STATUS}} and run it again.

Recap

• Sample API test cases in an Excel sheet are already a test suite: a row per case, with its inputs and its expected result.
• In Shift-Left API, import the sheet as a dataset and bind its columns to a test by name; the test runs once per row, with a result for each, and the rows can also come from CSV, JSON, a database or an API.
• A column like EXPECTED_STATUS lets every row carry its own expected result, so your test cases stay in the sheet, where your team keeps them.

Data-driven testing is in every edition, including the free one. Next, in lesson 9.8, you chain tests into visual workflows and let Workflow Doctor find the broken step. For more on writing the cases that go in the sheet, read how to write API test cases.