Answered

Exporting gallery to excel using a collection

(0) Share

Report

Posted on by kingy61422

I have a flow built in my app that will allow the user to export the filtered gallery to excel. I have tested the function with ignoring unsupported types using the code below:

ClearCollect(
 colOrderExport,
 OTT_Gallery.AllItems
);
Set(
 varFileLink,
 'PowerApp->OrdersTask'.Run(
 JSON(
 colOrderExport,
 JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes
 )
 )
)

But there are data that needs to be included and when I do not ignore unsupported types I get the error of "The JSON function cannot serialize tables / objects with a nested property called 'Icon' of type 'Control'.

I've tried a different method to get the columns I want but the column name references a 'Control'.

ClearCollect(
 colOrderExport,ShowColumns(
 OTT_Gallery.AllItems,
 "Task#"
 )
);
Set(
 varFileLink,
 'PowerApp->OrdersTask'.Run(
 JSON(
 colOrderExport,
 JSONFormat.IncludeBinaryData
 )
 )
)

There are controls that have data in them that I would like to have in the excel sheet.

Is there a way I can include the data in the controls in my excel sheet when I export?

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

M_Ali_SZ365 1,110 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @kingy61422 ,
To export gallery data to Excel, including control data without unsupported types, you can create a collection that only contains the necessary data fields, not the controls themselves. Here’s a step-by-step method to do this:
Collect Data: Use ForAll with OTT_Gallery.AllItems to iterate over the items and construct a new table with only the fields you need.
Remove Controls: If a column references a control, you’ll need to reference the properties of the control that contain the data, not the control itself.
Export to Excel: Use the PowerApp->OrdersTask flow to run the export with the cleaned collection.
Here is an example of how you might collect and export the data:
ClearCollect( colOrderExport, ForAll( OTT_Gallery.AllItems, { TaskNumber: 'Task#'.Text, // Replace 'Task#' with the actual name of the control property that holds the data // ... Add other necessary fields here } ) ); Set( varFileLink, 'PowerApp->OrdersTask'.Run( JSON( colOrderExport, JSONFormat.IncludeBinaryData ) ) );
Replace 'Task#'.Text with the actual property of the control that holds the text or value you want to export. Repeat this pattern for any other fields that reference controls. This way, you're only collecting the data itself, not the control properties that JSON cannot serialize, avoiding the 'Icon' of type 'Control' error.
Please accept this solution if it resolves the issue. ✅
Best regards,
Muhammad Ali

Was this reply helpful? Yes No
kingy61422 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you for the help. Is there a way for me to rearrange the columns in the order I want? I thought the order in which I typed them would be the easy fix but that is not the case.

Was this reply helpful? Yes No