1. Retrieve the URLs and necessary tokens and keys.
This is the first and most crucial step of the whole process and will establish the difficulty of the task ahead. Every API will have its own unique documentation. Some are clearer than others, some will require keys and others will need tokens. It is important to read all the documentation clearly and select the required URL and paste it in as a text input. Any keys should also be input as a separate column in the same text input. If this step is done correctly, the rest of the process should be straightforward.
2. Download
Under the “basic” tab, select the URL in the field dropdown. If a key is required, tick the checkbox in the “headers” tab.
3. JSON Parse
Select DownloadData in the JSON Field dropdown. Select “Output values into a single string field”.
4. Text to columns
Select JSON_Name to be the column to split and enter a period (.) as the delimiter to be split on. Select the split to columns option and carefully look at the JSON_Name column in the data. The number of periods will indicate the number of columns to split to. Here is a general rule to use: 1 period -> 2 columns, 2 periods -> 3 columns and so on. Enter the required number in the number of columns option.
5. Select
This step is not necessarily required but helps greatly with clarity and documentation. Select the JSON_ValueString and rename it to “value”. Select the JSON_Name field that contains the record ID and rename it as “Record ID”. Select the JSON_Name field that relates to the column headings and rename it as “header”.
6. Cross tab
If the previous step was not skipped, select “Record ID to group by, select Header as Column Headers, and “Value” as Values for New columns. The method for aggregating values should be “concatenate”.
If these six steps were followed correctly, the JSON data should have been parsed out in a neat table format.
