Data sourceing -Ideas and best Practices

[90_percent]

Ok, I have searched through the posts, and I am still confused.

Here is the scenario, I have an app, that uses 7 excel spreadsheets, the spreadsheets are generated every evening.

they are shared on a OneDrive, all users have access to it.

when I update the spreadsheets, (they are overwritten with new data) the data doesn't get updated in the app. I have hit the refresh, and it says its successful, but it is almost like its cached. and nothing changes.

If I remove the DataSource and readd it, it updates. (not sustainable)

Now I am going to go to a SQL database, and use tables, but that is down the road, once I have it all datapoints, and structure set up.

My question after all that is this.

Is there a way to reload the data, or delete the cached data to make it do a call to the spreadsheets?

would it be better to put it into a SharePoint Doc Library? would that speed up the cache reload?

am i way out in left field?

any ideas and suggestions would be appreciated!

thanks

Guy (90_percent)

[RandyHayes]

@90_percent 

First - you are working with Excel.  You have to realize that it is treated as a database once you add the list to your app.  So, it should not be altered by other means unless it is really adding or changing data in the existing list.

You have mentioned that some process is replacing those files every evening.  This is not a good thing for PowerApps.  It is not going to work that way.

Your better process is to have the new data either added to the excel file as rows in the file thought a PowerAutomate flow, or have the data put into a more flexible datasource - like SharePoint (no additional license needed) or SQL (Premium license needed).

If you think about your Excel as a database once it is in your app, then it makes more sense that you would not replace it with something else.  Add to it or alter within the constraints of the rows and columns of it is fine, but replace it is bad.

With Excel, the only way to get PowerApps to recognize a major change to it like you describe is to re-add that file as a datasource.  Otherwise it is not going to work with it.

HOWEVER, that said - you also mention what sounds like another form of adding Excel data into your App.  You can add Excel tables as interactive (data can change) or static tables (it is read into the app and retained and not changeable).

So, make sure you are bringing it in as a datasource that you can alter and not static.

I hope this is helpful for you.

[90_percent · Accepted solution]

@RandyHayes Thank you!

a little more information.

I have a list of 3500 stocks (rows) with 30 different attributes (columns) the stocks rarely change, but the attributes change daily.

 The other tables are subsets of this table depending on attributes.

I don't need the historical data, just the recent data. 

You mentioned SharePoint, as in a SharePoint list? or SharePoint document library? and would I need to add to the bottom new rows, or can I just change the attributes of the rows?

i appreciate the input!