I must say, I'm amazed at what you put together. I'm immensely grateful for your time.
I am familiar with all of the elements of what you are suggesting. I put this together in my flow. I get a null() error. on the Apply to each. (ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['rows']' is of type 'Null'. The result must be a valid array.)
Data is at the end of this post.
Here is my stack:
Sample Data. I may not have the termination correct. I did go to the end of the file and find the "]}}" so I think its right...
{"body":{"id":4549752307115908,"name":"MASTER DRAWING LIST","version":289,"totalRowCount":2274,"accessLevel":"ADMIN","effectiveAttachmentOptions":["LINK","GOOGLE_DRIVE","ONEDRIVE","EGNYTE","BOX_COM","EVERNOTE","FILE","DROPBOX"],"ganttEnabled":false,"dependenciesEnabled":false,"resourceManagementEnabled":false,"cellImageUploadEnabled":true,"userSettings":{"criticalPathEnabled":false,"displaySummaryTasks":true},"permalink":"https://app.smartsheet.com/sheets/VP3W8jGmP7r3j43mMqC7VFwJ5hWmRCQFC556GX51","createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2019-10-28T18:38:05Z","isMultiPicklistEnabled":true,
"columns":[{"id":3898437847541636,"version":0,"index":0,"title":"DRAWING #","type":"TEXT_NUMBER","primary":true,"validation":false,"width":171},{"id":8402037474912132,"version":0,"index":1,"title":"DESCRIPTION","type":"TEXT_NUMBER","validation":false,"width":599},{"id":1083688080435076,"version":0,"index":2,"title":"NOTES","type":"TEXT_NUMBER","validation":false,"width":575},{"id":3796166086289284,"version":0,"index":3,"title":"LAST UPDATED","type":"DATETIME","systemColumnType":"MODIFIED_DATE","validation":false,"width":150},{"id":8299765713659780,"version":0,"index":4,"title":"LAST UPDATED BY","type":"CONTACT_LIST","systemColumnType":"MODIFIED_BY","validation":false,"width":213}],
"rows":[{"id":818468139362180,"rowNumber":1,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2019-01-22T14:55:44Z","cells":[{"columnId":3898437847541636},{"columnId":8402037474912132,"value":"JS75 General Arrg.","displayValue":"JS75 General Arrg."},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2019-01-22T14:55:44Z"},{"columnId":8299765713659780,"value":"t@company.com","displayValue":"t@company.com"}]},
{"id":3070267953047428,"rowNumber":2,"siblingId":818468139362180,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-09-26T20:46:35Z",
"cells":[{"columnId":3898437847541636,"value":201013.0,"displayValue":"201013"},{"columnId":8402037474912132,"value":"JS75S PRV Dwg. 72784-2-1","displayValue":"JS75S PRV Dwg. 72784-2-1"},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@company.com","displayValue":"n@company.com"}]},
{"id":7573867580417924,"rowNumber":3,"siblingId":3070267953047428,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z",
"cells":[{"columnId":3898437847541636,"value":201015.0,"displayValue":"201015"},{"columnId":8402037474912132,"value":"JS75S316 General Arrg.","displayValue":"JS75S316 General Arrg."},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@company.com","displayValue":"n@company.com"}]},
{"id":6447967673575300,"rowNumber":4,"siblingId":7573867580417924,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z","cells":[{"columnId":3898437847541636,"value":201017.0,"displayValue":"201017"},{"columnId":8402037474912132,"value":"JS75S316 General Arrg. w/Flue","displayValue":"JS75S316 General Arrg. w/Flue"},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@company.com","displayValue":"n@company.com"}]},
{"id":8699767487260548,"rowNumber":5,"siblingId":6447967673575300,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z","cells":[{"columnId":3898437847541636,"value":201020.0,"displayValue":"201020"},{"columnId":8402037474912132,"value":"JS75L General Arrg.","displayValue":"JS75L General Arrg."},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@company.com","displayValue":"n@company.com"}]},
{"id":5040592790022020,"rowNumber":6,"siblingId":8699767487260548,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z","cells":[{"columnId":3898437847541636,"value":201021.0,"displayValue":"201021"},{"columnId":8402037474912132,"value":"JS75H General arrg.","displayValue":"JS75H General arrg."},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@copmany.com","displayValue":"n@company.com"}]},
{"id":7292392603707268,"rowNumber":7,"siblingId":5040592790022020,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z","cells":[{"columnId":3898437847541636,"value":201022.0,"displayValue":"201022"},{"columnId":8402037474912132,"value":"JS75XL General Arrg.","displayValue":"JS75XL General Arrg."},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@company.com","displayValue":"n@company.com"}]},
{"id":6166492696864644,"rowNumber":8,"siblingId":7292392603707268,"expanded":true,"createdAt":"2017-01-10T14:54:30Z","modifiedAt":"2018-07-03T18:25:56Z","cells":[{"columnId":3898437847541636,"value":201024.0,"displayValue":"201024"},{"columnId":8402037474912132,"value":"JS75XL-S316 General Arrg.","displayValue":"JS75XL-S316 General Arrg."},{"columnId":1083688080435076},{"columnId":3796166086289284,"value":"2017-01-10T14:54:30Z"},{"columnId":8299765713659780,"value":"n@company.com","displayValue":"n@company.com"}]}
]}}
Well, we are getting closer...
The data I have was from the Parse JSON using the schema I originally gave.
Your revised schema did work for your scenario. However, when plugging in my source data instead of the trial variable, I still get the null.
I parsed my original parse with your revised schema as well. That did not change my result. The Parse actions succeed, but the apply to each fails.
By the way, the trial dataset yeilds this output.. Which is in dire need of formatting into an array, but I think I can sort that bit out.
@abm ,
Well... it seems to work with whatever data I paste in there. Unfortunately, I cant dump the entire chunk in there because of size limits. However, I chopped some out of the middle to see what it would do with the empty lines at the bottom. It actually handled that just fine. So, I'm not sure what is happening wrongly.
I also discovered that this method is entirely too slow. It looks like the nesting of the data and sorting it out with the loops is going to just be too long. I would have thought once the data grab was done (which actually was the fastest part) then things would fly quickly with all of it in some local register. However, its taking like 2sec a row. Multiply that by just over 2,000 rows, and this is just not happening. I will need to think of another way to handle this. If you have ideas, I'm open.
Doing this has confirmed that the Apply to each is what is failing. It does not even give the compose a chance to check it. Do you know of a way to check if it is NULL before it executes the apply to each? This is ridiculous, by the way...
Result:
Helpful resources
Microsoft Ignite
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
Meet the Power Automate Super Users!
Many congratulations to the Season 1 2021 Flownaut Crew!
Microsoft Power Platform: 2021 Release Wave 1 Plan
Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.
Power Automate Community Blog
Check out the community blog page where you can find valuable learning material from community and product team members!
