Exporting the data of a gallery into Excel & download to local desktop
Create a New screen in the PowerApps
Insert a gallery as per you require, I selected a blank vertical gallery from the Insert option and provide the data source from SharePoint list
On the items property of gallery provide the data source
Now create a header above the gallery using labels or text inputs for the rows in gallery
Now we need to fetch the data of the gallery and save as a excel into OneDrive with a required name for the Excel
#Now create a Power Automate workflow Integrating through Power Apps to get the gallery items into Excel and save into OneDrive
- Insert action Create file for OneDrive business, this would be 2nd action.
- As 1st action will be by default from PowerApps
Flow named as Export to Excel flow
Provide the Inputs as required
- Folder path – OneDrive location root
- File Name – File name from ask in PowerApps
- File Content – File content from ask in PowerApps
3. Third action would be Respond to PowerApp, Add an output inside the action as Output Complete passes when true
Getting back to PowerApps
–> create a button on the screen and provide the function on Onselect property as below
With({_ExportCSVVar:"First Name, FullName, Email, Country, CreatedBy"&Char(10)&Concat(Gallery2.AllItems,TitleLabel.Text&","&FNLabel.Text&", "&EmLabel.Text&", "&CnLabel.Text&", "&CrbLabel.Text&""&Char(10))},Set(sucessvar,ExporttoExcelflow.Run(Concatenate("ExcelCSV"&" "&Now()),_ExportCSVVar).complete));
Elaboration
- With – With function evaluates a formula for a single record. This function can calculate a value and/or perform actions
- _ExportCSVVar – creating the Excel file as a CSV
- First Name, FullName, Email, Country, CreatedBy – these are the headers which we require to be in the Excel
- Concat – it combines all the records, as a result of a formula applied to all the records
- Gallery2.AllItems,TitleLabel.Text&”,”&FNLabel.Text&”, “&EmLabel.Text&”, “&CnLabel.Text&”, “&CrbLabel.Text – these are the labels which has taken in the gallery [which I renamed all the label names in the app]
- Set(sucessvar,ExporttoExcelflow.Run – this is the set function, in this we provide the set variable and the integrated flow to run
- Concatenate(“ExcelCSV”&” “&Now()),_ExportCSVVar) – combining the name of the Excel along with date & time and exporting as a csv file
- .complete – providing the output from the automate flow, so when its completed the file have to start to download
>>>> so after the function, click on the button Export to Excel so that the Excel file would save in OneDrive
Click on the ellipses(3 dots) of the excel saved in OneDrive & select the option copy link
copy link of that excel & paste the link in Onselect property of the ‘Export to Excel’ button continuing along with the function provided previously by using ‘if’ condition
If(sucessvar="true",Launch("https://testdigitek-my.sharepoint.com/:x:/g/personal/testdigital_testdigitek_onmicrosoft_com/EXPpL-WndJlAmIVVFtiV1DIBImivY_qcf3aVRrBy7DiFSQ?e=YkmuBN&download=1"))- provide the Set variable = true within “” (double quotes)
- Launching the Excel by copying the link & providing it in the launch function
- &download=1 – it makes the file to download for a single time into the local folder/system
Now the Excel can get by clicking on the Export to Excel button and it will get download to local system