Power Apps Export Large Data to Email, CSV or PDF File | SharePoint

hello everyone reza here in this video i will show you a technique through which we can export data from powerapps by leveraging power automate the data source can have large data purely because the query will be performed dynamically in power automate and in flow we can get all that data generate an html table send it out in an email create a csv file with that data converted into a pdf and a lot more so let's check it out in action [Music] i have a power app the home screen for which has a gallery that showcases data from a sharepoint list the sharepoint list has over 2000 records it is exceeding the delegation limit for power apps which can be set to a maximum of 2000. the scenario here is to export data from powerapps and the data that will be exported should be dynamic in nature the user can leverage the filters that are provided in powerapps to further refine the data so let's filter the data where the progress of the task is blocked i've also showcased how many items are currently being held in this gallery and here as well i can further filter it down based on the priority and if i click on export this will call a power automate flow that will in turn go ahead and trigger the process of exporting that data and sending it out to the current logged in user in an email the email that i have received contains all those four tasks that have the progress blocked and the priority critical the data is exported in tabular format in rich html manner directly inside the email as a table plus the data export has also been provided in a csv format and the data export has also been provided in pdf powerapps runs into a delegation challenge or a scenario in which your query is delegable in which case a gallery will load data only in batches of hundred let's filter the tasks where the progress is in progress and the priority let's say is high now if you look at the gallery count it's a hundred the query is delegable against sharepoint now if i scroll down to the hundredth record only then it will load the next hundred so if i try and export the data directly from my gallery i would run into the scenario wherein my gallery may not have all the data loaded plus if i load all the data in a collection in powerapps i would run into the delegation scenario and if i do get data in powerapps let's say up to the delhi gable limit as well which is 2000 max to send all that data from power apps to flow there would be a performance bottleneck purely because powerapps will have to transfer all that data to power automate power automate will have to read all that information and then perform the export in this scenario if i hit export i am actually performing the query dynamically inside of power automate and not in power apps powerapps is only sending the information of the query to be performed to power automate in progress priority high if i look at the email that i have received for this i have way more than 200 items this actually includes the entire result set from my entire data source of 2000 plus items in sharepoint the same thing is true for my csv file that's exported the same thing is true for my pdf file as well that gets generated in my home screen i have a gallery the items property for which is my sharepoint list called work progress tracker that is connected in my power app and this list has over 2000 records now to provide options for the user to filter the data directly in power apps let's say based on the title of the task i will insert a text input control set its default value to empty and set the hint text to search for task title i will add a label right above this called task title and this text input control i will rename it to txt task title i would like to filter this gallery where the title of the task starts with the text that the user enters in this text box filter my data source where i will use the start with function the text to be checked is from the title column in my sharepoint list and the starting string is that text box controls text property this completes my filter condition if i preview the app the text box is empty so it's loading all the data from my data source it's a deli gable query so it gets it in batches of 100 as i start typing in the text it will start querying my entire data set to search for tasks starting with that specific task title and there are a total of 11 items in my entire data set that have that specific task title however if i search for task number four as you can see the gallery is showing a hundred items that means there are more tasks where the title begins with task four now in powerapps the user can see all the tasks by navigating through the entire data set in this gallery however if the user was to perform a query where the data is 100 and the user does not scroll to the bottom how do i perform the export of all that data and that's why the idea is to perform the actual export operation the actual query operation in power automate and not let power apps do all that work now to start a flow from powerapps make sure that you head over to settings go to upcoming features search for power automate there should be a feature called power automate pane make sure you enable this this will give you an option in the left navigation for power automate i can select that i can click on create new flow i will create a flow from blank will give this flow a name i've called it export tasks the trigger for this flow go ahead and delete this from the connectors pick power apps and choose the powerapps v2 trigger this has a nice feature wherein we can add input parameters directly through the trigger action itself i will add two parameters the first one type text i will call this my query the query that i want to perform in flow and for the second input i'll again pick type text i will call it mail2 who do you want to send the exported data to i will add a new step and use the get items action from the sharepoint connector here i will connect to my sharepoint site and then pick my sharepoint list which is work progress tracker in here under advanced options we can add odata filter queries which allows us to filter our data in sharepoint i have done a full video on how to perform odata filter query on sharepoint lists or libraries for all column types i will highly recommend you to watch that i'll place the link for that video in the description of this video so do check it out now the query that i am trying to perform here is the same query that i'm performing in my powerapp which is whether the title column starts with a text that the user entered in my power app and typically the way you would write it here is starts with title column and then in single quotes you'll put the text and this will perform the filter query in sharepoint now bear in mind if your list is large the top count by default is 100 so make sure you plug this to 5000 that's the threshold limit for a sharepoint list and to ensure that it fetches all the data make sure you go to settings and turn on pagination the number here as well i will set it to 5000 so this will perform the filter query for me directly in flow however this query is what needs to be made dynamic and it needs to come directly from my powerapp and that i would be passing as a parameter from powerapp to flow and that parameter is called query so instead of hard coding the query here i will read the query from dynamic content and i will pick query input parameter from the powerapps trigger the next step is for me to create my html table that i would like to send out in an email so for that i will add a new step search for html there is a data operation action called create html table so i will select this the from property needs to be an array of data and that i will get from dynamic content the value property from the get items action and this will give me all the items based upon that query from my sharepoint list so i will select this under show advanced options by default the columns is set to automatic meaning it will read all the columns from that sharepoint list including the internal columns and create an html table now many a times you will not want all those columns including the internal columns to be a part of that html output that you're sending in the email you have two options here one is the get items action itself you can limit the columns that are returned by view so you can create a view and share point for that specific list or library and accordingly only have the columns that you're interested in exporting or instead of using columns automatic i can change this to custom and define my own key value properties here for example task title the value would be the title coming in from dynamic content of get items action the id of the task that will be my id column the progress of the task progress in my case is a choice column so i can pick progress value and who the task is assigned to assigned to in my case is a person type column so i can pick assign 2 display name and so and so forth once i have this html table generated i can then go ahead and use the send and email v2 action from the office 365 outlook connector who do you want to send this email to i want to send this to the input parameter that is being passed from powerapps to flow to plug that in here i will switch to advanced mode and from dynamic content i will pick male 2. the subject i will plug in task data export in the body of the email i simply need to add the output of the create html table action you can add style as well for that make sure you switch to the code view and right here i'll add my style you can go to show advanced options well i have additional properties that i can define for that email one of them being the importance of the email maybe i'll set the importance of the email to hi this completes my flow i will click on save this will save my flow and connect it directly in my power app on select of my button i would like to call that flow my flow is called export tasks i will use export tasks dot call the run method to run that flow and that flow expects two parameters the first one is the query that i would like to perform my query i need to pass as a text so in double quotes i will be defining my query which is starts with the title of the task for now i'm just hard coding the query but we'll make this dynamic let's say i'm searching for task 4 in the title of that task and that completes my first parameter the second parameter is who do you want to send this email to here i will send the email to the current user who is running the power app user.email will give me that information and after the flow is called i will use the notify function to notify the user that the export process has started and you will receive an email once completed the text that i have hardcoded here called task 4 this needs to come dynamically from this text box control this text box control i will copy its name go back to the on select function remove the hard coded text plug in a couple of double quotes a couple of ampersands within it and right in between the two ampersands i will use that text box control dot text now if i preview my app get me all the tasks where the title begins with task three i'll click export this should go ahead and trigger my flow if i head over to my mailbox and here is the email that i have received this shows me all the tasks that begin with the title task 3 from my entire data source if you would like to also showcase the total count of the items you can go back and edit the flow and right above the html table i can put count rows use the expression length and the parameter i will pick from dynamic content the value from get items action i'll click ok i'll introduce a couple of line breaks save my flow close it it will refresh the connection to power automate and now if i click export here is the email that i have received and this also includes the total count and here there are no limits you can add as many filter criteria conditions you want i will insert a drop down control the items for this i will use choices function because i have a choice column in sharepoint for progress from my sharepoint list i will get the progress column choices so this has loaded all my choices in here i have renamed this drop down control to drp progress i would like to apply this as well to filter the data in my gallery so right here i will add an and condition my column and sharepoint is progress it's a choice column so progress dot value is equal to my drop down control called drp progress dot selected dot value so if i preview this app currently i have no items that begin with the task title task 3 and the progress has not started let's change it to in progress i do have over 100 items let's change it to blocked now this is a dynamic query that the user is performing on the fly and power apps let's pass this to flow and the beauty here is i don't have to change anything in flow all i have to do is when i'm running the flow i need to change my filter criteria and here i will use and since i'm performing an and operation with another query here which is progress equal to in single quotes the value and that value will come in dynamically from powerapps so same as before two double quotes two ampersands and right in between drp progress dot selected dot value if i preview the app and if i click on export my flow is triggered this time the email that i receive only has two rows and those are the ones that begin with task title task 3 and the progress is blocked we'll go back to power automate inside powerapps and edit the flow here just like i created the html table there is another action called create csv table it's a data operation very similar to what i did for create html table from the value from the get items action show advanced options change this to custom and i can pick any of my column values that i would like to add to my csv table to attach the csv table data in the email under show advanced options we have attachments here i will give my attachment a name task export.csv the attachment content would be the output of create csv table i will save my flow i will close my flow if i click export this time the email that i receive will also include that attachment which is the csv file export of that dynamic data to create a pdf file as well i'll edit the flow i will add a new step after create html table add an action use the create file action from the onedrive for business connector the folder path i will create it at the root of the user's onedrive this is the user who will be triggering the flow from powerapps which is the current user who's running the app the name of the file here it has to be unique so i will use an expression i'll use the good function dot html and the content of the file would be the output of create html table next i will add an action convert file from onedrive for business the unique identifier of the file this i will pick the id dynamic content property from the create file action and the target type here i'll pick pdf so i have the pdf file content that i would like to send as an attachment in the email i already have my csv attachment i'll add another item here this would be taskreport.pdf that's the name of my file and the content would come from the file content dynamic property of the convert file action i will save my flow i will close the flow if i preview the app let's change the query task 2 in progress i'll click export here is the email that i have received in this scenario i have a lot of tasks that begin with the title task 2 and the status is in progress a total of 223. if i look at the csv data export here is all that data if i look at the pdf file that was exported here is all that data if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 50,799

Keywords: power apps export data, power apps, powerapps, power apps export to pdf, power apps export to csv, export data, export, power apps export large dataset, sharepoint, powerapps sharepoint, powerapps call power automate flow with parameters, flow odata sharepoint, flow filter, sharepoint get items filter query, powerapps export data to email, powerapps large data, powerapps large data sets, powerapps large sharepoint list, reza dorrani, powerapps call flow, power automate, flow

Id: UZdngrwWsqA

Length: 21min 45sec (1305 seconds)

Published: Mon Jul 18 2022