cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Parzival2307
Level: Powered On

Read Excel Stored in SharePoint

 Hello All

 

I have a requirement to read an excel file stored in a SharePoint Document Library through my Powerapp.

 

I created an excel online business connection in Powerapp but when I go to create an app based on this connection , this connector does not show up as an option.

 

Any idea what is wrong ? is it even possible to do it ?

 

 

Any help would be greatly appreciated !!

 

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Read Excel Stored in SharePoint

Hi @Parzival2307 ,

Do you want to create a connection to the Excel file stored within a SP Library?

 

If you want to create a connection to the Excel file stored within a SP Library, I afraid that there is no way to achieve your needs in PowerApps currently.

Currently, we could only create a connection to SP Library from a canvas app. We could not create an connection to a Excel file stored in a SP Library from PowerApps app.

 

If you would like this feature to be added in PowerApps, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

As an alternative solution, you could consider fire a flow (Microsoft Flow) from your PowerApps app to get records from the Excel file (whose data has been formatted as Table) stored in your SP Library, then return the retrieved Excel table records back to your canvas app.

I have made a test on my side, please take a try with the following workaround:

The flow's configuration as below:10.JPG

Within the "Response Body JSON Schema" field of the "Response" action, type the following JSON schema value:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Sno": {
                "type": "string"
            },
            "Sname": {
                "type": "string"
            },
            "Sage": {
                "type": "string"
            },
            "Sdept/Dept": {
                "type": "string"
            },
            "__PowerAppsId__": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Sno",
            "Sname",
            "Sage",
            "Sdept/Dept",
            "__PowerAppsId__"
        ]
    }
}

On your side, you may type following:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Column1": {
                "type": "string"
            },
            "Column2": {
                "type": "string"
            },
            "Column3": {
                "type": "string"
            },
            "Column4": {
                "type": "string"
            },
...
..., "__PowerAppsId__": { "type": "string" } }, "required": [ "@@odata.etag", "ItemInternalId", "Column1", "Column2", "Column3", "Column4",
...
... "__PowerAppsId__" ] } }

More details about getting the Response Body JSON Schema value, please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

The App's configuration as below:11.JPG

 

12.JPG

set the OnSelect property of the "Retrieve Excel Records" button to following:

ClearCollect(ExcelTableRecords,'20190726_case7'.Run())

On your side, you may type:

ClearCollect(ExcelTableRecords,'YourFlowName'.Run())

Add a Data Table, set the Items property to following:

ExcelTableRecords

and enable corresponding columns within this Data Table. When you press the "Retrieve Excel Records" button, it would fire the flow you defined above, then your Data Table would be populated with records from your Excel table (Excel file) stored in your SP Library.

 

More details about firing a flow (Microsoft Flow) from a PowerApps app, please check the following article or video:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/using-logic-flows

https://www.youtube.com/watch?v=1wl9AtxWdkg

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Read Excel Stored in SharePoint

Hi @Parzival2307 ,

Do you want to create a connection to the Excel file stored within a SP Library?

 

If you want to create a connection to the Excel file stored within a SP Library, I afraid that there is no way to achieve your needs in PowerApps currently.

Currently, we could only create a connection to SP Library from a canvas app. We could not create an connection to a Excel file stored in a SP Library from PowerApps app.

 

If you would like this feature to be added in PowerApps, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

As an alternative solution, you could consider fire a flow (Microsoft Flow) from your PowerApps app to get records from the Excel file (whose data has been formatted as Table) stored in your SP Library, then return the retrieved Excel table records back to your canvas app.

I have made a test on my side, please take a try with the following workaround:

The flow's configuration as below:10.JPG

Within the "Response Body JSON Schema" field of the "Response" action, type the following JSON schema value:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Sno": {
                "type": "string"
            },
            "Sname": {
                "type": "string"
            },
            "Sage": {
                "type": "string"
            },
            "Sdept/Dept": {
                "type": "string"
            },
            "__PowerAppsId__": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Sno",
            "Sname",
            "Sage",
            "Sdept/Dept",
            "__PowerAppsId__"
        ]
    }
}

On your side, you may type following:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Column1": {
                "type": "string"
            },
            "Column2": {
                "type": "string"
            },
            "Column3": {
                "type": "string"
            },
            "Column4": {
                "type": "string"
            },
...
..., "__PowerAppsId__": { "type": "string" } }, "required": [ "@@odata.etag", "ItemInternalId", "Column1", "Column2", "Column3", "Column4",
...
... "__PowerAppsId__" ] } }

More details about getting the Response Body JSON Schema value, please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

The App's configuration as below:11.JPG

 

12.JPG

set the OnSelect property of the "Retrieve Excel Records" button to following:

ClearCollect(ExcelTableRecords,'20190726_case7'.Run())

On your side, you may type:

ClearCollect(ExcelTableRecords,'YourFlowName'.Run())

Add a Data Table, set the Items property to following:

ExcelTableRecords

and enable corresponding columns within this Data Table. When you press the "Retrieve Excel Records" button, it would fire the flow you defined above, then your Data Table would be populated with records from your Excel table (Excel file) stored in your SP Library.

 

More details about firing a flow (Microsoft Flow) from a PowerApps app, please check the following article or video:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/using-logic-flows

https://www.youtube.com/watch?v=1wl9AtxWdkg

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Parzival2307
Level: Powered On

Re: Read Excel Stored in SharePoint

hello @v-xida-msft 

 

Thank you so much for your response.

 

I have set up my flow but having some issue on Powerapp side. The flow runs successfully and i can see the output sent back but in the Powerapp, the data does not get populated in the DataTable.

 

I noticed in your screenshot that your DataTable already has columns of excel.

See below screenshot : 

DataTable.JPG

 

Curious to know how did you achieve this ?

When I add my DataTable , it does not give me any option to add columns.

My DataTable is empty :

MyDatatable.JPG

 

Any help on this is appreciated !!

Thanks

Community Support Team
Community Support Team

Re: Read Excel Stored in SharePoint

Hi @Parzival2307 ,

Do you set the Items property of the Data Table to following collection?

ExcelTableRecords 

 

When you set the Items property of the Data Table to following collection?

ExcelTableRecords 

 you need to click "Fields" property within right panel as below:20.JPG

then enable corresponding columns within the Data Table control.

 

Please take a try with above solution, check if the issue is solved. Please also consider go to "File"-> "Collections", then check if the ExcelTableRecords collection populated with proper records.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Parzival2307
Level: Powered On

Re: Read Excel Stored in SharePoint

Hi @v-xida-msft 

 

I checked my "File"-> "Collections" as you had suggested and I believe that this is not fetching the correct data.

This is all i get :

Collection.JPG

 

This is the Response Body JSON Schema which I configured in the  advanced options of the Response action in the Flow :

JSON.JPG

 

I also checked the output that my flow is returning by checking the Flow run history and it looks something like this :

output.JPG

 

I can see all the columns and row values being returned properly.

 

Not sure why proper data is not reaching the Powerapp.

 

Thanks a lot for your help !!

Community Support Team
Community Support Team

Re: Read Excel Stored in SharePoint

Hi @Parzival2307 ,

Based on the issue that you mentioned, I think there is something wrong with your flow's configuration.

 

In order to get the JSON schema value within the "Response Body JSON Schema" field of the "Response" action in your flow, please check and see if the following blog could help:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

You could consider configure your flow based on above blog, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,970)