Updating Data in Document Library Files

[JoshShutts]

Hello. I have an app that connects to an Excel file within a document library. I am able to update that file but what I am trying to do is, every month I want to replace that current file, or add rows to the table, in the document library without having to redo the app or manually refresh the connection. I can't seem to figure out either of these options without having to redo the entire app. Is there a best practice for what I am trying to accomplish? For context, I built this app so teammates can use the app to make updates to our system of record based off reporting and want to just upload the new report for them to work every month. Thanks in advance.

[BrianS]

PowerApps will work with Excel as a data source, but it really not something you want to rely on. I would explore using a Sharepoint list which will be much more performant and reliable. You can also add formating to your SP list to make it easier to glean data from.

[JoshShutts]

Some of my lists would be rather large in some cases and I am trying to make this as automated as possible for myself and the end user, without me having to upload the data manually into lists every month. I was hoping to find a solution to where I can just drop a new file every month and let Power Apps do the work. As a note, our company does not allow syncing to One Box yet. If you have other suggestions, I am all ears. Thanks!

[BrianS]

Usually in an "automated" solution you want to gather the data and upload it at the same time. How is the data being added to the Excel sheet? Could that not be done directly thru the app. You would avoid having "current data" in an Excel sheet which needs to be maintained and then later uploaded. 

I do no know of a way to have PowerApps switch the Excel file automatically. You may be able to use PowerQuery to aggregate several files uploaded into a directory and then update the query monthly and have PowerApps always use the sheet with the query table.

[JoshShutts]

Unfortunately I am dependent on an external source to grab my reports every month. I pull it down from another resource convert what I need to a table then upload to my SharePoint library. Every month there will be new data to update by the assigned to end user and I was hoping that I could just save over the old file in the doc library, with the same file name, or even just append the new data to the already completed rows on that file.

[BrianS · Accepted solution]

You should be able to add data to the end of the table. You would not be able to add columns. You also can't mess with the extra columns that PowerApps adds.

[JoshShutts]

Ok thanks. I did try to add data at the end of my table and received an error in my app that the table did not exist. So maybe that's my solution but I am just doing it wrong. I'll keep trying today now that I know it's possible.

[BrianS]

PowerApps only reads tables, so you have to make sure the data is added to the table. You will need to refresh the data if the app is running or you are editing it.

[JoshShutts]

So what you're saying is that as I add data to the table in the excel file, I need to go to the app and refresh the data source every time?

[BrianS]

When you start the app it reads the current data. If the app is running or you are editing it when the data is changed then you need to refresh the data. PowerApps does not know when the data is changed externally.