I had a mass data export project and needed to get historical data. When I was exporting from their system, it wasn’t giving me all the fields I needed but they were in the API. Unfortunately, adding rows to excel row by row can be operations intensive. Not sure if this is a new way but it is one way I found to save on operations and get tons of data by exporting to JSON. Normally this export would have cost well over 1k operations. By using this method, it cost 26 operations.
In my project, the API has an endpoint for counting how many rows of data are present with the applied filters. This is how many rows of data I would need to export to Excel.
Since the limit per call was 100, I had to use a repeater. Dividing the count by 100 gives me how many times the repeater will need to trigger.
I setup my API call and have it aggregate to JSON. Source module for the aggregator will be the COUNT Activities module. There are 11 bundles returned (1019 records) and within each bundle there is a 100 collections. If I set my aggregator to my iterator, it will return 11 bundles in my aggregator. I do not want that. I want everything in one string, not 11 strings.
When creating the aggregator, you’re going to have to create a data structure. I’m not a developer/coder by trade. In your API documentation, there should be the schema of the JSON object. (forgive me if my language is not accurate, like I said, I don’t know alot about coding.)
You will copy and paste this into your data structure. Make sure to click generate and then paste the code. I pasted the schema into CHATGPT just to make sure there was no issues with it. I have pasted other ones and something was wrong it. I just asked CHATGPT to return a properly formatted one.
After I pasted it, your aggregator should have the fields it needs. You will just need to map for the iterator now.
Next step, I need to get all the results into a JSON text file. I use OneDrive but I believe it is similiar workflow for Google Drive. I use the Upload File module. Name the file whatever you want but the file extension must be .json. Data will be the JSON string created by the Aggregator.
This will create this file. I accidentally had an additional copy in there so it called activities 2 instead of activities. This JSON file should be populated with the 1019 records from the aggregator.
Now go into Excel > Data > Get Data > File >From JSON. Select the file we just created. This will load the data into power query.
It will look like this when it is loaded into Power Query. In the top left, click “To Table”
After you click To Table, you will have this screen. You will now expand the records and can select which fields you want to keep.
Now you have your table of data. If you’re familiar with Power Query, you can continue to manipulate the data or just load it into Excel by clicking ‘Close and Load’ in the top left.
Here’s your table of data.
