How to Create CSV in SharePoint using PowerApps and Power Automate

In this PowerApps tutorial, we will discuss how to create CSV file in SharePoint using PowerApps and Power Automate. By taking a simple scenario, we will see how to export data to excel using PowerApps and Power Automate.

Also, We will discuss about how to export collection to CSV in PowerApps.

PowerApps Export to CSV

In this example, we will see how we can export PowerApps Data to a CSV file using SharePoint Online. Follow these below steps to do so:

I have a SharePoint list named “Patient Registration Details“. This list has below columns with different Data types as:

  • Patient Name (By default, this is a Title column, just I renamed it to Patient Name)
  • Age (Number Data type)
  • Date Of Birth (Date and Time Data type)
  • Gender (Choice Data type)
  • Mobile Number (Number Data type)
  • Email Address (Single line of text Data type)
  • Location (Choice Data type)

Refer the below screenshot.

PowerApps Create CSV in SharePoint
PowerApps Create CSV in SharePoint

Next coming to PowerApps, follow the below processes to export CSV.

  • Sign in the PowerApps app with your credentials.
  • Create a blank new Canvas app and choose any one Layout either Tablet or Phone.
  • On the Powerapps Blank screen, Connect the SharePoint List Data source (View -> Data sources -> Search SharePoint -> Add a new connection or existing connect -> Select specific SharePoint Site -> Choose specific SharePoint List [Patient Registration Details]) -> Hit on Connect button. Once you will add the SharePoint list, it will look as shown below.
PowerApps Create CSV
Create CSV in SharePoint using PowerApps
  • On the PowerApps Blank Screen, Insert a Vertical Gallery control (Insert -> Gallery -> Vertical) and set the SharePoint list [‘Patient Registration Details‘] to its Items property.
  • In this below screenshot, here the vertical Gallery control Layout is “Title, Subtitle, and Body” and the Field values are “Title“, “Email Address” and “Location“.
  • Select one item from the Gallery control and Insert a Check box control (Insert -> Input -> Check box) at the right side of an item as below.
PowerApps Create CSV in SharePoint online
How to create a csv in SharePoint using PowerApps

PowerApps Export Collection to CSV

Now we need to add a column as IsChosen which value will be false. For that, create a PowerApps Collection where you can easily add this column and set its value to false.

  • Add a Button (Insert -> Button) and set its Text property to “Insert Data to Collection“.
  • Select the Button and apply the below formula on its OnSelect property as:
OnSelect = ClearCollect(colPatientDetails, AddColumns('Patient Registration Details', "IsChosen", false))

Where,

  1. ClearCollect = This function helps to create a Powerapps collection where always it clears the previous value and stores the new one.
  2. colPatientDetails = Powerapps Collection name
  3. AddColumns = PowerApps AddColumns is a function that helps to add a column to a table or collection
  4. ‘Patient Registration Details’ = SharePoint List Name
  5. “IsChosen” = This is the column that I want to add and set its value to false.
PowerApps Export Collection to CSV
PowerApps Create CSV in SharePoint
  • Next, select the Check box control and apply this below formula on its OnCheck property as:
OnCheck = Patch(colPatientDetails, ThisItem, {IsChosen: true})

Where,

  1. Patch = Patch function is used to modify single or multiple records of a data source. If you are interested to know more about the Patch function, then refer to this link: Powerapps Patch function
  2. colPatientDetails = Powerapps Collection name

Here in the above code, I have set the IsChosen column to true.

PowerApps Export Collection to CSV file
PowerApps Export Collection to CSV
  • Similarly, select the Check box control and apply this below formula on its OnUncheck property as:
OnUncheck = Patch(colPatientDetails, ThisItem, {IsChosen: false})

Here in the above code, I have set the IsChosen column to false.

Power Apps Export Collection to CSV
PowerApps Export Collection to CSV
  • Once it is done, just Preview (F5) the app and click on the Button (Insert Data to Collection). Go to Collections (View -> Collections -> Collection name), there you can see all the records as shown in the below screenshot.
Export Collection to CSV in PowerApps
Create CSV in SharePoint using PowerApps and PowerAutomate

PowerApps Create CSV in SharePoint Document

Now we will see how to create a CSV file in Powerapps and store that file in a SharePoint Document Library. For this thing, we will use the Power Automate or PowerApps Flow.

  • On the PowerApps screen, Create a new flow (Action -> Power Automate -> + Create a new flow).
  • Sign in with Power Automate (if not) -> Go to My flows (in the left navigation) -> Click on + New dropdown -> Select + Instant–from blank option.
  • Enter the Flow name (PowerAppsCreateCSV) -> Choose PowerApps option to trigger the flow -> Hit on Create button as shown below.
PowerApps Create CSV in SharePoint Document
PowerApps Create CSV in SharePoint Document
  • Next, the Flow screen will appear with the PowerApps action. Below the PowerApps action, just add a new step and then add a Compose action. This action has one input field as:
  1. Inputs: Click on Dynamic content tab -> Ask in PowerApps as like below.
PowerApps Create CSV in SharePoint list
Create CSV in SharePoint using PowerApps and PowerAutomate
  • Now just save the flow and come back to the PowerApps Page. Insert a Button on the PowerApps page and set its Text property to Create CSV.
  • Select the Create CSV Button and add the flow (Go to Action -> Power Automate -> Click the flow [PowerAppsCreateCSV]) on it that you have created recently.
export CSV in SharePoint list using PowerApps
Create CSV in SharePoint using Power Automate
  • After adding the flow on PowerApps Button, it will look like “PowerAppsCreateCSV.Run()“. Before that, you need to create two variables with JSON Format as:
OnSelect = Set(
    varExportCSV,
    DropColumns(
        Filter(
            colPatientDetails,
            IsChosen
        ),
        "Author",
        "Created",
        "Editor",
        "Age"
    )
);
Set(
    varJson,
    JSON(
        varExportCSV,
        JSONFormat.IndentFour
    )
);
PowerAppsCreateCSV.Run(varJson)

Where,

  1. varExportCSV = Variable name
  2. DropColumns = PowerApps DropColumns function excludes columns from a table or collections
  3. colPatientDetails = PowerApps Collection name
  4. IsChosen = This is the column that you have added in the Powerapps Collections
  5. “Author”, “Created”, “Editor”, “Age” = These are the columns present in the SharePoint list
  6. varJson = Variable name
  7. PowerAppsCreateCSV = PowerApps flow name

You can refer to the below screenshot. Once it is done, just Save and Preview (F5) the app. Click on the Create CSV button and then the flow will trigger.

PowerApps export CSV in SharePoint list
Create CSV in SharePoint using Microsoft flow
  • If you will go to the Flow and check the Flow History, then you can see the flow has been triggered successfully. Open the recently succeded flow and expand the Compose action as shown below. Copy the whole things from the OUTPUTS section and paste it in a notepad. We will use this code in the next action.
powerapps create csv file
PowerApps Create CSV in SharePoint
  • Next, Edit the flow and add a new step below the Compose section. Insert Parse JSON action and enter these below input fields as:
  1. Content: Go to Dynamic content -> Select Outputs option under the Compose section.
  2. Schema: Click on the Generate from sample option and paste the code that you have copied in Notepad. Click on Done.
powerapps create csv file SharePoint
Microsoft flow create csv in sharepoint
  • Once you will add or paste the code, then the JSON schema will look like as the below screenshot.
powerapps create csv file SharePoint list
Microsoft flow create csv in sharepoint
  • Now again save and test the flow from the previous succeded run. Then the flow will succeed. Once you will expand the Parse JSON, you can see the correct JSON schema format in its OUTPUTS section as shown below.
How to export Powerapps csv in SharePoint
Microsoft flow create csv in sharepoint
  • Edit the flow and then add a new step under the Parse JSON action. Insert Create CSV table action and enter the value of this below field as:
  1. From: Select Dynamic content -> Select Body under the Parse JSON section.
How to export Powerapps csv in SharePoint document
PowerApps Create CSV in SharePoint
  • Next, add a new step and add the Create file (SharePoint) action [As we need to create the CSV file in the SharePoint Document Library]. Enter the below fields as:
  1. Site Address: Select the SharePoint Site address URL. Also, if the Site address is not there, then you need to enter the SharePoint Site address URL using the custom value.
  2. Folder Path: Select the folder path where you want to create the PowerApps CSV file. As I want to create in the SharePoint Document Library, So I have chosen Shared Documents from the show picker icon.
  3. File Name: Provide a name for the CSV file with extension as “.csv“.
  4. File Content: Select the Dynamic content -> Select Output under the Create CSV table action.

NOTE:

Not only you can create the file in SharePoint, but also you can create the file in OneDrive. If you want to create the file in OneDrive, then Select the action as Create file (OneDrive for Business).
How to create Powerapps csv in SharePoint document
Create CSV in SharePoint using PowerApps and PowerAutomate
  • That’s it for the flow. Below represents the whole structure of the Power Automate. Just Save the flow and go back to the PowerApps screen or apps.
Create Powerapps csv file in SharePoint document
How to create a csv in SharePoint using PowerApps
  • On the Powearpps screen what you will do is, just remove the flow (View -> Data sources -> Click … from the flow -> Remove) and again add it to the app.
powerapps export gallery data to excel
PowerApps Create CSV in SharePoint
  • In the same way, just select, copy the code from the Button’s OnSelect property and paste it in a notepad. Next, remove the code from OnSelect Property.
  • To add the flow again, just select the button (Create CSV) -> Go to Action tab -> Power Automate -> Add the flow (PowerAppsCreateCSV).
  • After adding the flow to Powerapps, just paste the same code on Button’s OnSelect property that you have copied from the notepad.
powerapps export collection to excel
powerapps export collection to excel
  • Save, Publish, and then Preview (F5) the PowerApps app. Select some records from the Gallery control by using a checkbox and then click on the Create CSV button as shown below.
powerapps generate csv file
powerapps generate csv file
  • Next, go to the SharePoint Document Library (Documents). You can see a new CSV file has been created with the given name as SampleCSVFile.csv as below.
powerapps generate csv file SharePoint
How to create a csv in SharePoint using PowerApps
  • Once you will click on the CSV file, then you can see all the record details of each person that you have selected from the PowerApps Gallery.
powerapps generate csv file SharePoint document
PowerApps Create CSV in SharePoint

Also, you may like these below PowerApps Tutorials:

In this PowerApps tutorial, we discussed PowerApps create a CSV file in SharePoint using Power Automate. Also, we saw how to export the PowerApps collection to CSV.

  • >