Export to Excel - Excel file data issue

(0) Share

Report

Posted on by SamanthaLavoie

161

Hello,

I have a gallery with options to sort the gallery data. The data comes from a collection made from a sharepoint list. After the data is sorted I have made a button to export the sorted data to excel. When I click the Export to Excel button, it generates an excel file in a specified folder (using flow) but the information within the exported file is very difficult to read making the file almost useless.

Problem:

1) All columns that are not assigned as single lines of text in my SharePoint list, the cell values shown in the exported excel file are shown as {"Value":"JUN 23"} instead of JUN 23. This makes the file very difficult to read and use.

How can I fix this issue in order to get usable data in the excel file that is exported? I have included screen shots of my flow and what the excel file data looks like in my exported file. The code I'm using on the Export to Excel button On Select property is:

ClearCollect(colContractData, ShowColumns('Main Data 5-24', "Title", "field_1", "DIVISIONASSIGNMENT","field_10","ONMCR_x003f_","CMName","COSSPECIALIST1","COSLEAD1", "field_15","field_24","field_25"));

'ExportToExcel'.Run(JSON(colContractData,JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes));

Clear(colContractData);

Collect(colContractData,'Main Data 5-24');

I would really, really, appreciate help fixing this issue. Thank you.

Excel file data.png

Flow 1.png

Flow 2.png

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Michael E. Gernaey 53,442 Super User 2025 Season 2 on at

Like (0)

Report

Hi @SamanthaLavoie

Firstly, love you, than you for sharing your information very clearly.

I am actually that all of the fields aren't real JSON.

I am thinking if we can fixup the JSON so that you can do a Select on the columns, you will for sure then get clean as we can make it an advanced select to do that, I would really need to see the content from your Content action. Not all of it, just a single full row

Because I would prefer really that you use a Parse JSON, verus a json expression, but maybe you dont know the schema well enough to do that.

But first, I would really need to see the format of the content.

If we can get the data in the correct format then it will be easy to resolve, just want to see one row of the content, before and after you json it.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
SamanthaLavoie 161 on at

Like (0)

Report

Thank you for your quick response! My data is from a SharePoint List. I have included a screenshot here of the list after exporting it to excel from within the SharePoint application. I am only showing the columns of data that I want exported from the SharePoint list, as it has many other columns that I don't want exported. I have labeled each column with the column type assigned in the SP list so you can see the format of the data. If this isn't the information you need to see, please clarify and I'll get that info to you.

Thanks so much,
Samantha

Was this reply helpful? Yes No
Michael E. Gernaey 53,442 Super User 2025 Season 2 on at

Like (0)

Report

Hi @SamanthaLavoie

Thanks this does help the follow up I had was in the Choice columns are they multi-single select? Or can you label them depending please

Now what I maybe poorly explained was, if you go into a previous RUN. Under History (on the details page).
And you see these actions (in the history run)..
you should be able to see the RAW INPUT and RAW OUTPUT (as well as the regular input and output) for each action
I was hoping to see (not the whole dataset) but a single full row, so I can absolutely help.

BUT aside from that I have another question. Since this data comes from SharePoint. Is it just a subset of the data? And if so, instead of passing the data as JSON or passing it at all, can you pass the filters you need to get the data you want ON SharePoint?

This way, you can get the data from within the Flow AND it will be much much much much much, did I say, much easier to do what you want.

Now, as for why you see Mini-JSON, its because they are choices, and whether they are multi-select or single, its going to give you JSON because thats what you get from the Canvas App unfortunately. This is why if you keep passing the data in, I wanted to see a complete line of the content so that I can tell you how to filter it properly.

But if instead you simply pass the filters for the data, OR if its the whole dataset, then we can use the Get Items action and iterate through and create your csv

Ok 🙂 hope that helps

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
SamanthaLavoie 161 on at

Like (0)

Report

Hi Michael,

My drop downs are all single selection drop downs.

I hope this is what you need. I opened my flow (screenshot) and copied and pasted the Input text and output text (only one row of data), and pasted it below after removing any identifying info.

Flow - 1 Row of Content Inputs:

[{"CMName":{"Id":379,"Value":"Jow Blow"},"COSLEAD1":{"Id":3,"Value":"Anne Smith"},"COSSPECIALIST1":{"Id":4,"Value":"Sam Something"},"DIVISIONASSIGNMENT":{"Id":10,"Value":"DLD"},"ONMCR_x003f_":{"Value":"No"},"Title":"0000000000000000000000123","field_1":"CONTRACTOR NAME","field_10":{"Value":"Moderate"},"field_15":"Purchase Type","field_24":{"Value":"Compliant"},"field_25":{"Value":"JUN 23"}},

Flow - 1 Row of Content Outputs:

[{"CMName":{"Id":379,"Value":"Joe Blow"},"COSLEAD1":{"Id":3,"Value":"Anne SMith"},"COSSPECIALIST1":{"Id":4,"Value":"Sam Something"},"DIVISIONASSIGNMENT":{"Id":10,"Value":"DIV"},"ONMCR_x003f_":{"Value":"No"},"Title":"0000000000000000000000123","field_1":"CONTRACTOR NAME","field_10":{"Value":"Moderate"},"field_15":"Purchase Type","field_24":{"Value":"Compliant"},"field_25":{"Value":"JUN 23"}},

As for your other question about printing reports directly from Sharepoint. I want my users be able to generate a report directly through power apps, I don't want them in my sharepoint data lists. I want them to use the export to excel button. As long as this is accomplished, I'll be happy. What ever is the easiest method to accomplish that will work.

Thank you for your help!
Samantha

Was this reply helpful? Yes No
Michael E. Gernaey 53,442 Super User 2025 Season 2 on at

Like (0)

Report

Hi @SamanthaLavoie

You forgot to mention if all the Choices are single select or multi-select or a combination of that
They look single but it could just be you only selected a single value.

And I wasnt saying I wanted them in the SharePoint itself, but its too much to explain again.

Cheers,
-Michael

Was this reply helpful? Yes No
SamanthaLavoie 161 on at

Like (0)

Report

Hi Michael, all the dropdowns are single-selection dropdowns.

Thank you,
Samantha

Was this reply helpful? Yes No
SamanthaLavoie 161 on at

Like (0)

Report

I haven't heard from Michael since 10/26. Can anyone help with this issue?

Was this reply helpful? Yes No