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.

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.

• 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 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.

• 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.

• 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.

• 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.

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.

• 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.

• 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.

• 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.

• 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.

• 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.

• Once you will add or paste the code, then the JSON schema will look like as the below screenshot.

• 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.

• 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.

• 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).

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

Also, you may like these below PowerApps Tutorials:

• PowerApps Email Attachment Control – How to Use
• PowerApps Microphone Control – How to use
• How to Create Login Screen in PowerApps
• PowerApps Popup message Box with Examples
• Get users from SharePoint Group in PowerApps
• PowerApps camera control + Save captured image in SharePoint
• PowerApps role based security SharePoint example (SharePoint Groups)
• PowerApps: Submit data to two SharePoint Lists
• PowerApps set field value based on another field
• Embed PowerApps in SharePoint modern page
• How to use date time picker in PowerApps
• PowerApps Cascading Dropdown
• PowerApps Value Function – How to use

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.