Excel to JSON: How to maintain the field order predefined in data structure?

mim24 · March 4, 2025, 12:28pm

Hi Community,

We have built the following scenario:
When calling a webhook, then an (OneDrive-hosted) Excel file is read out, the rows are filtered through several query parameters (passed over from the webhook), then the resulting rows are being “aggregated to JSON” and returned as JSON in the webhook response.

This works quite well. (Constructive feedback regarding filters and error handling welcome though.)

I have the following issue with my scenario:
The resulting JSON which is passed over to the webhook (response) contains the mapped fields but not in the order which I have mapped in the “Aggregate to JSON” module and which I have defined in the chosen “Data Structure”. The JSON output always comes in another order of fields (also when calling the webhook via Postman).
See screenshots.

Already the input to the “Aggregate to JSON” module comes in the wrong order which is not transformed into the given order of the Data Structure:

Where is the mistake?
I have experimented already a lot with

the modules “Array Aggregator”, “Create JSON”, “Transform JSON” and “Parse JSON” in between,
the option “Preserve the order of object keys” in the Data Structure,
etc.

=> My goal is to keep it tool-based without custom code / scripts / individual structure definition (e.g. in the Transform JSON), so that I can easily add fields later on with the given means of the tools (Excel, Make module settings).

I hope you can help. Thanks in advance.
Mica

samliew · March 4, 2025, 12:47pm

Welcome to the Make community!

To “preserve” the order of the keys in a JSON collection, you need to do it manually using a “Compose a String” module, and writing the JSON yourself.

This is because in the JSON specification, there is no such thing as key ordering within an object.

Hope this helps! Let me know if there are any further questions or issues.

— @samliew

P.S.: Investing some effort into the Make Academy will save you lots of time and frustration using Make.

mim24 · March 4, 2025, 12:57pm

Thanks for the swift reply, Sam.

So if this is really the only solution:
Does the “Compose a string” module need to come before the “Aggregate to JSON” or after (i.e. before the webhook response)?

Is it possible to at least facilitate the maintenance a bit by creating “sub-strings”? E.g., one string for each collection in the resulting JSON?

Thanks,
///M

samliew · March 4, 2025, 1:00pm

You can use a Text Aggregator to build each item.

e.g.:

{
  "key1": "<value>"
}

Then, set the aggregator’s separator to a comma.

Next, use a Set Variable module to add the array brackets around it:

e.g.:

{
  "items": [ {{1.text}} ]
}

Example

Here is a demo example for you to play with:

Module Export - quick import into your scenario

You can copy and paste this module export into your scenario. This will import the modules (with fields/settings/filters) shown in my screenshots above.

Move your mouse over the line of code below. Copy the JSON by clicking the copy button on the right of the code, which looks like this:
Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the editor.
Click on each imported module and re-save it for validation. There may be some errors prompting you to remap some variables and connections.

JSON module export — paste this directly in your scenario

{"subflows":[{"flow":[{"id":416,"module":"json:ParseJSON","version":1,"parameters":{"type":""},"mapper":{"json":"{\n  \"example_array\": [\n    {\n      \"example_key\": \"example_value\",\n      \"example_key2\": \"example_value2\"\n    },\n    {\n      \"example_key\": \"example_value\",\n      \"example_key2\": \"example_value2\"\n    },\n    {\n      \"example_key\": \"example_value\",\n      \"example_key2\": \"example_value2\"\n    }\n  ]\n}"},"metadata":{"designer":{"x":319,"y":-3042},"parameters":[{"name":"type","type":"udt","label":"Data structure"}]}},{"id":415,"module":"builtin:BasicFeeder","version":1,"parameters":{},"mapper":{"array":"{{416.example_array}}"},"metadata":{"designer":{"x":567,"y":-3040}}},{"id":417,"module":"util:TextAggregator","version":1,"parameters":{"rowSeparator":"other","otherRowSeparator":",","feeder":415},"mapper":{"value":"{\n  \"new_key1\": \"{{415.example_key}}\",\n  \"new_key2\": \"{{415.example_key2}}\"\n}"},"metadata":{"designer":{"x":810,"y":-3040,"name":"Build Item JSON"},"parameters":[{"name":"rowSeparator","type":"select","label":"Row separator","validate":{"enum":["\n","\t","other"]}},{"name":"otherRowSeparator","type":"text","label":"Separator"}],"advanced":true}},{"id":418,"module":"util:SetVariable2","version":1,"parameters":{},"mapper":{"name":"json","scope":"roundtrip","value":"{\"items\": [ \n\n{{417.text}}\n\n]}"},"metadata":{"designer":{"x":1059,"y":-3041,"name":"Final JSON"}}}]}],"metadata":{"version":1}}

Note: Did you know you can reduce the size of blueprints and module export code like the above, using the Make Blueprint Scrubber?