After the http action to get all users, take and use the "create file" action directly to create a new file in sharepoint somewhere. Name the filename something.json and in the data add the body output from the http action containing all results.
Run the powerautomate flow and the file should be created with all information within the file.
Now download the json file from Sharepoint (not mandatory but easier to explain).
Open excel
- Choose "Data" - "Get Data" - From File (Json)
- point to the json file
- now powerquery will open.
- Choose "into table"
- Expand the values
- Expand the value.results list
- Expand the value.result records
Now all columns for all the users will be available in powerquery, you should see the resulting table. To get it to excel now click top right button "Close & Load" - now it should be in excel.
Extra: If you connect directly to the json file in SharePoint instead and use all steps above you can just hit the refresh button in excel and then every time your powerautomate overwrites the json you will get the table with all users without more job.
was this at all understandable? 😉
//Claudio
