Extracting Data from a JSON with pagination to an excel

[Anonymous]

I have extracted data from an API with pagination. I have attached screenshots for all my components and I'm getting an output for every page in the API. I need to extract these information into an excel but the output will not be coming as a tabular format (see the output figure).

The issue why my data comes in one row is my data is under "$.task_assignments.[*]" and the next page url is in "$.links.[*]" path of JSON (see the attached JSON image).

If I define the Loop Jsonpath query as "$.task_assignments.[*]" and extract data I can get my data in a tabular format but I can't get the URL and If I define the Loop Jsonpath query as "$.links.[*]" then I can get the URL and in that case I don't get the data.

So as a solution I set up the  Loop Jsonpath query as "$" which is a common path but in that case although the data comes it doesn't comes in a tabular format.

I hope you can understand what I'm asking. So it would be great if someone can help in extracting these data into one table and export into excel

[Anonymous · Accepted solution]

Why not build urls custom? No need to read the json each time to get the next url.

You can do an initial call to the api and extract total_pages, put that in a tLoop from 1 to total_pages,

then just append ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) to your tRestClient query string

so it looks something like this: 

"https://api.harvestapp.com/v2/task_assignments_page="+ ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) +"&per_page=100"

This way your jsonpath from tExtractJSONFields wont have to change, and you'll get only the relevant data you need.