cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VD
Helper II
Helper II

Export data from two different galleries into one excel sheet

I am having two galleries, one is parent gallery and second is child gallery.

I need to export data from these two galleries into one excel i.e., when i click on export then,

data from parent gallery should be export in one sheet and data from child gallery should be export in another sheet of same excel.

6 REPLIES 6
CNT
Community Champion
Community Champion

@VD to interact with data in excel, you must have tables set up in excel. When you set up the connection to the excel file, power apps will look for all the tables and list them. Once you connect with these tables you can read/write to them. Hope this will help you! 

dyee
Resolver II
Resolver II

This is pretty easy. You want to submit the data to excel using a patch function.  It would look something like this

 

Patch(Datasource,Defaults(DataSource),

{ Column1 : Gallery1.selected.fieldname

Column2: Gallery1.selected.fieldname

Column3: Gallery2.selected.fieldname})

VD
Helper II
Helper II

@CNT , @dyee , my data source is Azure Sql for both the galleries, I can export the data from both galleries in excel file using power automate, but for two gallery data i am exporting two separate excel.

I want to export the data in a single excel but, in same excel, worksheets should be two for two gallery data.

dyee
Resolver II
Resolver II

@VD  There is no need to use flow.  Here is how you do it.  

 

Step 1 - Make sure your excel sheet has a table.  Name your table something.  In this example, I'll call it Data_Excel and each column will just be called column1, column2, column3.  

Step 2 - Upload your excel sheet into PowerApps.  I can send a link on how to do this if you get confused.

Step 3 - Created a button icon.  Go to the on select property and then your formula is the following:

 

Patch(Data_Excel,Defaults(Data_Excel),
{Column1: (First Item you want to patch) - Gallery1.Selected.Header

Column2: (Second item) Gallery1.Selected.ID

Column3: (Third Item) Gallery2.Selected.Details})

Step 4 - Press the button and confirm the data maps correctly. 

It doesn't matter what the datasource is for the galleries.   Let me know if you have any issues. 

@dyee, thanks for your help and for detailed solution.

could you please give solution on my below questions?

1. In gallery, I have checkbox to select a record, if user select single or multiple records then only those records should be export in excel.

2. If user not select any record and click on export that time all the data in gallery should be exported. Currently the count of data in gallery is 1000+ and it will keep increasing.

 

dyee
Resolver II
Resolver II

It's your project and you can do it however you want.  However, I would strongly recommend you do not do option #2.  It will take an extraordinarily long time and it will cause your userbase to sour on the app.  Here is what I would recommend doing instead.

 

#1 - When you add new records to your gallery, patch that to an excel sheet in OneDrive.  Add a button that says "Export All Items" and when they click it launches the excel sheet. 

 

To accomplish your first goal, I would have the oncheck property of your checkbox create a collection.  The collection should only have the exact same headers/fields as your excel sheet.  Make sure the onuncheck property will also remove the record from that collection.  The onsubmit field should use the code Patch(ExcelDataSource,Collection).  Note - This is a bit tricky so it might take a few tries to get this right.  The other way you can do this is a ForAll patch.  This would be ForAll(CollectionName,Patch(ExcelData,Defaults(ExcelData),{Column1: Field1,

Column2:Field2..etc})

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,988)