I’m trying to pull data from monday and after sorting it export it into excel 365. It’s working, but the sorting is not successful.
Steps taken so far
I’ve browsed the help topics and other resources but have not found anything that works. Right now it’s at the Create JSON step and the sorting is done there, but the sorting is not happening. I’ve attached the max three files as a newcomer, so please let me know what else I can provide. Any help is much appreciated!
Here are some useful links and guides you can use to learn more on how to use the Make platform, apps, and app modules. I found these useful when I was learning Make, and hope they might benefit you too —
Thanks! I had already tried using the raw key value too, and just ran it again with the following changes, 1. use the key in the sort argument 2. changed the field names in monday to English only (propogating the changes throughout the flow). Sadly, it didn’t do it.
The output bundle post Create JSON is shown below, with the 8 still being the second record. What am I doing wrong?
[
{
“json”: “{"Sorted":[{"End of Policy Year":1,"Surrender Value (Pre WD) Guaranteed Accrual Rate":1.1},{"End of Policy Year":8,"Surrender Value (Pre WD) Guaranteed Accrual Rate":8.8},{"End of Policy Year":6,"Surrender Value (Pre WD) Guaranteed Accrual Rate":6.6},{"End of Policy Year":12,"Surrender Value (Pre WD) Guaranteed Accrual Rate":12.12},{"End of Policy Year":9,"Surrender Value (Pre WD) Guaranteed Accrual Rate":9.9}]}”
}
]
The above is not valid JSON. You can verify this by copy-pasting into jsonformatter.org
Could you please try providing it again with the following instructions?
1. This forum might have or already changed your text
When pasting text into this forum, you should format the example text using the rich-text editor, otherwise the forum software might modify the displayed text, and you might get incorrect answers from others because of it.
Some things this forum software might do to mangle your text:
– remove extra spaces (which may be necessary)
– convert links to titles (when copied is incorrect)
– incorrect joined links
– convert single and double quotes to smart angled quotes (“ ”)
– emojis
– etc.
This interferes with you receiving correct answers, because it:
– makes JSON invalid (you can check by copy-pasting into jsonformatter.org)
– makes incorrect text examples when we need to build a pattern for text parsing
2. To prevent this in future, please format text in code blocks
These are the two ways to format text so that it won’t be modified by the forum:
Method 1: Type code block manually
Add three backticks ``` before and after the content/bundle, like this:
``` content goes here
```
Method 2. Highlight and click the format button in the editor
3. You might need to re-copy the original text
Once the post has been submitted, it’s too late to format it since it’s already butchered, and you need to make a re-copy of the text, and format it before submitting the forum post.
Please let us know once you have corrected the issue. This will avoid others potentially providing wrong answers based on incorrect data in your question.
[
{
"json": "{\"Sorted\":[{\"End of Policy Year\":1,\"Surrender Value (Pre WD) Guaranteed Accrual Rate\":1.1},{\"End of Policy Year\":8,\"Surrender Value (Pre WD) Guaranteed Accrual Rate\":8.8},{\"End of Policy Year\":6,\"Surrender Value (Pre WD) Guaranteed Accrual Rate\":6.6},{\"End of Policy Year\":12,\"Surrender Value (Pre WD) Guaranteed Accrual Rate\":12.12},{\"End of Policy Year\":9,\"Surrender Value (Pre WD) Guaranteed Accrual Rate\":9.9}]}"
}
]
The output bundle is shown again hopefully in the right format. I copied it with the correct format and validated it to be VALID JSON. However, I’m guessing this only shows the incorrect result, where my problem lies in the following Create JSON Module (of which the output JSON bundle result is downloaded for).
Hi Sam, I wish it did but I actually started with asc and saw ASC somewhere to just try but neither worked (also tried just omitting it as the default is asc anyway). I rebuilt that part as I was trying some other stuff, but now I’m getting this error. I checked that it is a number. I am not sure what it means by that it encountered an invalid number. I even found your old solution about this using customJS and that didn’t work for me either (the Excel add a worksheet row can’t find the output field, but I’d prefer to not go there for now). I don’t know why sorting an array is so hard for me.
Regarding the mapping (I’m not sure if I’m addressing this correctly), I am using the parse JSON to change the string back to an array. That part seems to work fine, although the next step into Excel is also not working properly now but I’m not going there yet lol.
As I went through the academy, I got more familiar with the tools, and tried it out. The courses are really helpful. I’ve learned a lot more but can’t say I’m an expert or anything so I could be presenting a less than optimal solution. I’ve added my blueprint which includes some extra steps because I was testing to see where the sorting would work.
The flow goes as:
monday - watch groups items
Array aggregator (mapped to JSON data structure)
JSON - Create JSON (array aggregator array)
JSON - Parse JSON
Tools - Set multiple variables (sorting happens here)
Iterator
Router
Microsoft Excel grabbing fields from the iterator
The sorting works this way and it made sense to me. I’ve tried doing the sorting in the iterator or JSON and thus saving some operations but it hasn’t worked for me. At least it would be something for reference.
Also the updating of the Excel rows is very sluggish. Not sure how to get around that. blueprint (3).json (83.9 KB)
