Use Postman Visualizer to get JSON into a format for Excel

What is Postman Visualizer?

Postman Visualizer is a tool in Postman that allows you to render the JSON responses from FOLIO in easier to understand visual formats, like tables and bar charts.

https://learning.postman.com/docs/sending-requests/visualizer/

You essentially add extra javascript code to the tests that you can run in the Postman tool to output it in different formats. 

Postman offers training and info on it on the page above, but their examples are more than a little complicated for what we might want to do. So I'm just going to detail an example of how I used this tool to output permissions information in tabular format, which I could then copy/paste super easy into an excel spreadsheet.

Step 1: Set up an API call in Postman that successfully returns the data you want to visualize.

In this case, I'm going to set up a call to the Permissions API to get a list of the permissions available in the local development environment running Fameflower. (Input your own URLs, tenant and token values as appropriate.)

I added a parameter of length=10000 because FOLIO's default parameter is 10, so it needs to be tons longer in order to get all the results back.

Step 2: Set up your visualization

This is the part I had the most trouble with because I am definitely not a programmer. But hopefully it can make sense. 

Go to the Tests tab in your Postman API connection.

This is your basic setup for using Visualizer. 

You have a part at the beginning:

var template = `

`;

This is where you put in your code for the visualization you want to show up. It's essentially like extended HTML.

Then the code underneath that:

// Set visualizer
pm.visualizer.set(template, {
    // Pass the response body parsed as JSON as `data`
    response: pm.response.json()
});

is what spits out the visualization.

SO, because I want to make a table show up, I'm going to put code in that var section that looks a lot like HTML for making a table. It looks like this:

var template = `
    <table bgcolor="#FFFFFF">
        <tr>
            <th>Display Name</th>
            <th>Permission Name</th>
            <th>Sub Permissions</th>
            <th>Child Of</th>
        </tr>


        {{#each response.permissions}}
            <tr>
                <td>{{displayName}}</td>
                <td>{{permissionName}}</td>
                <td>{{subPermissions}}</td>
                <td>{{childOf}}</td>
            </tr>
        {{/each}}
    </table>
`;

So what I think is happening here is that you put in the table code, and the table headers just like you would expect. But for the table rows, you get to use this magic {{#each response.SOMETHING}} loop to format the JSON data!

The excellent thing about this is that you can change the order of the JSON fields. So in this example, I have Display Name showing up first in the table, when in the JSON permission name shows up first.

Also, depending on the structure of the JSON, you would change the response.permissions part to specify which part of the record should be iterated over. So a JSON for a permission record on the top level is one big record enclosed in {} and within it, a record named "permissions" that is an array of individual permissions entries. I want it to loop over those individual permissions entries, so I use response.permissions.

So to sum up, this is everything that is in the Tests tab:

var template = `
    <table bgcolor="#FFFFFF">
        <tr>
            <th>Display Name</th>
            <th>Permission Name</th>
            <th>Sub Permissions</th>
            <th>Child Of</th>
        </tr>


        {{#each response.permissions}}
            <tr>
                <td>{{displayName}}</td>
                <td>{{permissionName}}</td>
                <td>{{subPermissions}}</td>
                <td>{{childOf}}</td>
            </tr>
        {{/each}}
    </table>
`;

// Set visualizer
pm.visualizer.set(template, {
    // Pass the response body parsed as JSON as `data`
    response: pm.response.json()
});

Step 3: Test Your Visualization and View Your Output

So now you run your API!

To get to the Visualizer, scroll down to the Body section, and instead of clicking on Pretty or Raw to get the JSON, click Visualize.

Then to copy the output to Excel, click in the window, do a CTRL-A CTRL-C (select all and copy), and then in Excel, paste it as "match destination formatting." If that doesn't work, try Paste Special > text to paste without any formatting.

Tip: Handling nested JSON fields

There will be times when your JSON data will include nested fields. For example, Loan Types:

        {
            "id": "",
            "name": "",
            "metadata": {
                "createdDate": "",
                "createdByUserId": "",
                "updatedDate": "",
                "updatedByUserId": ""
            }

To include those in your Visualizer script, reference the parent and the child in your TD elements like so:

                <td>{{metadata.createdDate}}</td>
                <td>{{metadata.createdByUserId}}</td>
                <td>{{metadata.updatedDate}}</td>
                <td>{{metadata.updatedByUserId}}</td> 

Another Example: Visualizing "normal" and nested fields in a table

(Discussed in Slack July 2023: https://folio-project.slack.com/archives/CQ7EK52LB/p1688134681791399)

In this little example i wanted to know which external IDs belong to each title in instance-storage/instances (using the development environment Snapshot). As final result i wanted to display/visualize all titles from instances with each corresponding external ID, so that i can put the data in an easy-to-use Excel table. Hopefully, this example will be helpful - especially for non-programmers (e.g. for librarians like me : )). Thanks to Owen Stephens for helping out with the code!

This is the GET request (with default login data for Snapshot):

Let's have a look at the response first, to identify the required fields:

So, we see that the top level data is "instances". For the desired table which i want to visualize, i am needing "title" from "instances" and also "value" inside "identifiers" inside "instances".

This is the code in the Tests tab:

var template = `
    <table bgcolor="#FFFFFF">
        <tr>
            <th>Title</th>
            <th>External ID</th>
        </tr>

        {{#each response.instances}}
            <tr>
                <td>{{title}}</td>
                <td>{{#each identifiers}}
				{{value}},
				{{/each}}		
				</td>
            </tr>
        {{/each}}
    </table>
`;

// Set visualizer
pm.visualizer.set(template, {
    // Pass the response body parsed as JSON as `data`
    response: pm.response.json()
});

As you can see it's very similar to the code used in the example above on this page (permissions example). The difference is 1. that #each response is set to loop trough the data from "instances": {{#each response.instances}} and 2. after the iteration trough instances and before {{value}} we type {{#each identifiers}} to iterate over the data inside {{value}}. So in total we need 2 loops trough the data. First trough "instances" (top-level) and then trough "identifiers" (which is inside "instances" and contains a nested field "value").

The final result looks like beneath - the left column of the table shows all the titles. The right column shows all external IDs from each title.