Data-Driven API Testing

When testing insurance APIs, static test cases can quickly fall short—especially when coverage requirements vary by state and are subject to change. This post highlights a dynamic, low-code testing framework that uses Google Sheets to define rule-based inputs, Apps Script to generate structured test data, and Postman to run iterative tests.

The result is a flexible, maintainable solution for simulating real-world business logic across many API scenarios.

Defining Business Rules in Google Sheets

Insurance rules often vary by state. For example:

State	Required Coverages
CA	generalLiability ≥ $2M, cyberLiability ≥ $500k
NY	generalLiability ≥ $2M, professionalLiability ≥ $1M
TX	generalLiability ≥ $1M
MA	cyberLiability ≥ $500k
IL	waiverOfSubrogation, primaryNonContributory = "included"
WA	professionalLiability ≥ $500k
NJ	All of the above, bundled: GL, RE, Cyber

These rules can often become complex and time consuming to hardcode in your Postman tests — so I externalized them in Google Sheets for easier management.

At the core of this approach is a Google Sheet that outlines state-specific coverage requirements. Each row represents a unique scenario, where the state column maps to client.address.state in the API request, and coverages_selected holds a JSON array of required coverages.

For example:

Business Rules

These coverages mirror the structure used in the API payload's coverages array, allowing the test to validate not only presence but also the correct limits or values.

Test Data with Google Apps Script

Rather than exporting the sheet manually, a custom Apps Script automates the conversion of the sheet into a JSON file suitable for Postman's Collection Runner.

function sheetToJson() {
  const sheetName = "Sheet1"; //Whatever the sheet name is
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) throw new Error(`Sheet with name "${sheetName}" not found.`);

  const rows = sheet.getDataRange().getValues();
  const headers = rows[0];
  const data = rows.slice(1).map(row => {
    let obj = {};
    headers.forEach((header, index) => {
      if (header === "coverages_selected") {
        try {
          obj[header] = JSON.parse(row[index]);
        } catch (e) {
          obj[header] = [];
        }
      } else {
        obj[header] = row[index];
      }
    });
    return obj;
  });

  const json = JSON.stringify(data, null, 2);
  const folder = DriveApp.getFolderById("ABC123"); //Replace with known Google Drive folder id
  folder.createFile(`${sheetName}_data.json`, json);
}

The resulting file can be downloaded from Google Drive and used as input for test execution in Postman.

Running Iterative Tests in Postman

With the JSON file ready, the next step is using Postman Collection Runner to iterate through each test case. Steps below:

1. Open Postman → Collection Runner

2. Choose your test collection

3. Upload the generated JSON as the data file

4. Run the collection — one iteration per row

The API request uses Postman variables to dynamically insert the test values:

"client": {
  "address": {
    "state": "{{state}}"
  }
},
"coverages": {{coverages_selected}}

During each iteration, Postman pulls the corresponding values from the data file and injects them into the request. This makes it possible to run 10, 50, or 100 test cases with a single request setup. Remember, the variables in the API request must match the column name on the worksheet.

In the pre-request tab, we need to convert the coverages_selected field from a JSON string to a proper JavaScript object, then assign it to an environment or collection variable that can be injected into the body. Before executing the Postman runner, its necessary to add the following pre-request script.

// Parse JSON data manually to handle correctly
const data = pm.iterationData.toObject();
pm.variables.set("state", data.state);

// Stringify coverages array and set it as a variable
pm.variables.set("coverages_selected", JSON.stringify(data.coverages_selected));

This pre-request script ensures that both the 'state' and 'coverages_selected' fields from the external data file are properly prepared for use in the request body. The 'state' is set as a simple string variable, while 'coverages_selected'—an array of coverage objects—is explicitly stringified to ensure it's injected as valid JSON.

Without this step, Postman would treat the array as a raw object, resulting in output like '[object Object]' instead of a proper JSON array. This setup allows each test iteration to dynamically and accurately populate the request with scenario-specific values.

Postman DDT Results

Note: Instead of using the converted JSON file, Postman allows for running iterative scenarios using a CSV file. It is often a faster approach to testing simpler scenarios such as this.

Takeaways

This method fully embraces the power of data-driven testing:

• Dynamic iterations: The same request is reused across all test cases

• No hardcoding: Rules live in the sheet, not in test scripts

• Realistic validation: Sheet columns directly reflect real API fields

• Ease of maintenance: Updates to business logic require no code changes

Whether you're testing regulatory compliance or dynamic coverage logic, this setup offers a scalable way to validate complex input structures against evolving requirements.