App for Excel on Sharepoint portal

[AmitY]

Hello from a novice Power Platformer who needs help with his first project,

I have an Excel file stored on our company Sharepoint portal, the file is being used by multiple users.

Now I want to create a Power App based on data stored in this file, and found that I have two options:

• Create the App that reads data from this Excel. But a few searches brought me to a dead end, I learnt that this is not possible.
  • I would love to hear that I am wrong and that it is possible, if so please guide me on how to achieve that.
• A few more searches revealed that it really was not a dead end, and that I can create a Power App based on a Sharepoint List. So I create a Sharepoint list from the excel, and created the App. But there I hit another bump (not calling it a dead end this time 🙂), in that the list is static since the time it was created.
  • So is it possible to update this list every time the Excel is updated?  If so, how?

Can someone help me out?

Thanks,

AmitY.

[v-qiaqi-msft]

Hi@AmitY,

Based on the issue that you mentioned, do you want to write data to your Excel table which stored within your SP library?

If you want to update the Excel table which stored within your SP library in PowerApps, I am afraid that there is no way to achieve your needs in PowerApps currently.

For now, we could only get the File name, created time, modified time, creator, etc properties (metadata of the file) of a file stored within a SP library, we could not retrieve file content of a file stored in a SP library.

Patching data into Excel table which stored within SP library is not supported within PowerApps. If you would like this feature to be added in PowerApps, here is an idea you can vote for:

Create App from Excel file stored on SharePoint - Power Platform Community (microsoft.com)

For now, we could only get data from the Excel table firing a flow from PowerApps, 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.

Here is how I configure my flow:

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"
            },
            "PurchaseOrder": {
                "type": "string"
            },
            "Country": {
                "type": "string"
            },
            "State": {
                "type": "string"
            },
            "City": {
                "type": "string"
            },
            "__PowerAppsId__": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "PurchaseOrder",
            "Country",
            "State",
            "City",
            "__PowerAppsId__"
        ]
    }
}

On your side, please write the formula as follows:

{
    "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__"
        ]
    }
}

Note: Replace "Column1","Column2",... with your real column name in the Excel file.

Then add this flow into your app as follows:

Insert a Button and set the OnSelect property as below:

ClearCollect(ExcelTableRecords,demo1.Run())

Insert a DataTable and connect to the collection, add the corresponding fields.

Hope it could help you.

Best Regards,

Qi

[AmitY]

Thanks for the detailed explanation Qi. I missed mentioning that this intended Power App will be a Read-Only app, the goal is to display the data that is in the Excel on Sharepoint. Users will make changes to Excel via the Sharepoint site, and a different set of users would display the data in Power App.

I tried your guide and got stuck at 'Action of Request Response' screen. Turns out out company does not have Premium licenses, and 'Response Request' is a premium feature.

[v-qiaqi-msft · Accepted solution]

Hi@AmitY,

Combine that with your description, you just want to extract data from the Excel table that it stored in the SharePoint site.

Based on the current situation, to sum up as below:

1)use sharepoint connector, can not get excel content

2)use excel online(without license for premiun connector), could only get excel content in one drive, please upload your excel to one drive

3)"imported from excel", could get static excel content. And you could not update the list once the Excel table is updated.

Since your company does not have the license for the premium connector, I want to confirm something with you that if you have to upload the Excel table to the SharePoint site? This will help me give the different workaround.

// If possible, I advise that upload the Excel table to the OneDrive which is a standard connector.

// If you insist on uploading it to the SharePoint site, your need that creating an app based on a SP list which is created directly based on the Excel data can be met, except that you can't synchronize the SharePoint list when the Excel table is updated.

Hope it could help you some degree.

Best Regards,

Qi

[AmitY]

Thanks Qi for all the pointers. I have decided that I will go the Onedrive route. I have uploaded the Excel file to Onedrive where user will make changes, and I have created an App that has read only access to the file. Works great and thanks again for your time and help, really appreciate it.

AmitY