Back to Blog

JSON API Response Debugging: From Postman to Excel Analysis

You're testing an API endpoint. Postman shows 200 OK. But when you actually look at the response data, something is wrong.

Duplicate IDs. Missing fields. Unexpected null values.

You need to analyze 500 rows of response data to find the pattern. Opening raw JSON in Notepad won't cut it.

Why Developers Use Excel for API Debugging

Excel isn't just for accountants. It's one of the best tools for:

1. Spotting patterns in large datasets
Sort, filter, and pivot 10,000 API responses in seconds.

2. Comparing expected vs actual
Paste your test fixtures in one column, API responses in another, use formulas to compare.

3. Sharing findings with non-technical teams
Product managers can't read JSON. They can read Excel.

Common API Debugging Scenarios

Scenario 1: Pagination Returns Duplicates

You're calling /api/users?page=1, /api/users?page=2, etc.

Suspicion: Page 2 contains some users from page 1.

Manual approach:

  1. Copy JSON from each page
  2. Try to find duplicates visually
  3. Give up, write a Python script

Excel approach:

  1. Combine all responses into one JSON array
  2. Convert to Excel using JsonExport
  3. Use Excel's "Remove Duplicates" feature
  4. See exactly which IDs appear multiple times

Scenario 2: Null Values Where They Shouldn't Be

Endpoint: /api/products

Expected: All products have price and stock fields.

Reality: Some products have null values.

Excel approach:

  1. Convert API response to Excel
  2. Filter price column → Show blanks/nulls
  3. Instantly see which products are broken

Example Excel formula to count nulls:

=COUNTBLANK(B2:B1000)

Scenario 3: Date Format Inconsistencies

Your API returns dates in mixed formats:

{
  "orders": [
    {"createdAt": "2024-01-15T10:30:00Z"},
    {"createdAt": "1642248600000"},
    {"createdAt": "2024-01-15"}
  ]
}

In Excel, you can:

  • See all three formats side-by-side
  • Use TEXT() formulas to normalize them
  • Identify which records need fixing

Step-by-Step: Postman → Excel

1. Export from Postman

After running your request:

Option A: Single Response

  • Click "Save Response" → "Save to a file"
  • Choose JSON format
  • Save as response.json

Option 2: Collection Runner (Multiple Requests)

  • Run your collection
  • Click "Export Results"
  • Choose JSON format
  • Get a file with ALL responses

2. Handle Collection Runner Output

Collection Runner exports look like this:

{
  "runs": [
    {
      "request": {...},
      "response": {
        "body": "[{...}, {...}]"
      }
    }
  ]
}

The actual data is nested inside runs[].response.body.

Most converters fail here because they try to flatten the entire structure.

JsonExport handles this:

  1. Detects the nested structure
  2. Lets you navigate to the body field
  3. Extracts just the data you need

3. Convert to Excel

  1. Open JsonExport
  2. Upload your response.json
  3. If using Collection Runner format:
    • Switch to "Nested View"
    • Expand the runs array
    • Select the response.body field
  4. Download Excel

4. Analyze in Excel

Now you can:

  • Filter by status codes (if you included them)
  • Sort by timestamp to see chronological order
  • Use formulas to calculate averages, counts, etc.
  • Create PivotTables for response time analysis

Advanced Techniques

Compare Two API Versions

Testing a new API version vs the old one?

  1. Export old API responses → old.xlsx
  2. Export new API responses → new.xlsx
  3. Use Excel's "Compare Files" or VLOOKUP:
=VLOOKUP(A2, old!A:Z, 5, FALSE)

This finds matching records and compares specific fields.

Response Time Analysis

If your JSON includes timestamps or response times:

{
  "requestId": "abc123",
  "responseTime": 245,
  "status": 200
}

In Excel:

  1. Create a histogram of response times
  2. Find the 95th percentile
  3. Identify slow outliers

Formula for 95th percentile:

=PERCENTILE(B2:B1000, 0.95)

Error Rate Tracking

Extract all error responses (status 400-500):

  1. Convert full response log to Excel
  2. Filter status column → 400-599
  3. Create a PivotTable: Error Code × Count
  4. See which errors are most common

Handling Complex API Structures

Nested User Objects

{
  "user": {
    "id": 123,
    "profile": {
      "name": "Alice",
      "email": "alice@example.com"
    },
    "settings": {
      "notifications": true
    }
  }
}

Becomes in Excel:

user.iduser.profile.nameuser.profile.emailuser.settings.notifications
123Alicealice@example.comtrue

Dot notation preserves the structure.

Arrays in Responses

{
  "userId": 1,
  "permissions": ["read", "write", "admin"]
}

Option 1: Flat view

userId | permissions.0 | permissions.1 | permissions.2
1      | read          | write         | admin

Option 2: Nested view (better for variable-length arrays)

  • Parent sheet: Users (userId)
  • Child sheet: Permissions (userId_ref, permission)

You can then join them in Excel using VLOOKUP.

Real-World Case Study

Background

API endpoint: /api/transactions

Expected: 10,000 transactions per day

Problem: Duplicate transaction IDs causing billing errors

Investigation Steps

  1. Exported 7 days of API logs (70K records)

  2. Converted to Excel using JsonExport

  3. Used Excel formula to find duplicates:

=COUNTIF($A$2:$A$70000, A2) > 1

  1. Filtered to show only duplicates

  2. Discovered pattern:

    • Duplicates only occurred between 2-3 AM UTC
    • All had status: "pending" instead of "completed"

  3. Root cause: Timezone conversion bug in cron job

Time saved: Hours of grepping logs and writing SQL queries.

Tips for Large API Response Sets

1. Use Query Parameters to Filter

Instead of exporting 1M records:

GET /api/orders?startDate=2024-01-01&endDate=2024-01-07

Only get what you need.

2. Combine Multiple Responses

Using jq (command-line JSON processor):

jq -s 'add' response1.json response2.json response3.json > combined.json

Then convert the combined file.

3. Sample Large Datasets

Don't need every record? Sample 10%:

jq '[.[] | select((. | .[0:2] | tonumber) % 10 == 0)]' huge_response.json > sample.json

This takes every 10th record.

Security Considerations

API responses often contain:

  • Auth tokens
  • User emails
  • API keys
  • Sensitive business metrics

Never paste into random online tools.

JsonExport processes data 100% client-side:

  • ✅ No file upload to servers
  • ✅ Works offline after initial page load
  • ✅ Your API keys never leave your browser

Integration with CI/CD

Automate API response validation:

# GitHub Actions example
- name: Test API
  run: |
    curl https://api.example.com/data > response.json
    
- name: Convert to Excel for analysis
  uses: script-action
  with:
    script: node convert.js response.json

Then commit the Excel file to your repo for historical tracking.

Common Errors and Solutions

Error: "Cannot convert cyclic structure"

Your API response has circular references:

{
  "user": {...},
  "parent": <reference to user>
}

Fix: Remove circular refs before converting (use jq or manual edit).

Error: "Maximum file size exceeded"

Your response is >10MB.

Solutions:

  1. Split into smaller chunks
  2. Use server-side tools for massive files
  3. Sample the data (see section above)

Conclusion

Debugging APIs doesn't require expensive APM tools or custom scripts.

For most issues, Excel + proper JSON conversion is enough:

  • ✅ Find duplicates
  • ✅ Spot null/missing values
  • ✅ Compare API versions
  • ✅ Calculate error rates
  • ✅ Share findings with non-technical teams

Convert Your API Response Now - Paste JSON, get Excel in seconds.

Related Articles: