Excel on OneDrive for business not updating

[NamitaBadwal]

I have created four different surveys (Yes/no or selecting from radio buttons or dropdowns are major inputs) on a single application and it pushes the survey responses to an Excel file in OneDrive. Each survey generates a score and final score is calculated as an average of score of each survey. All the calculations are done in the app and nothing in the Excel Table. This final score is pushed back to the app for users to view their final score.

My challenge is the OneDrive excel is not getting updated at all. I used the same Excel on Google Drive and it was updating in real time. Though after sometime it also stopped updating after few users used the app. Previously on OneDrive, the Excel updated with inputs from two users when they used the app but after that no other user inputs were updating. So I removed the Excel and uploaded a new copy of the Excel but it is not updating at all.

I do not have a premium account so cannot use CDS and for company privacy purpose can't use Google and have to use OneDrive for Business. I do not have sharepoint rights as well so OneDrive is the only solutions I have.   

I am using Collect function to update the Excel data tables. Is there any work around or any explanation how to update the Excel in OneDrive in real time. I have been working on this for 2 days and searched around a lot and nothing is helpful. 

I am expecting 1000s of people to use it. 

[NamitaBadwal]

Hi @WarrenBelz,
It is the one I have shared above. That one calculates the total score. 

For one of the other survey pieces, code is below:

Survey 1 Response:

Set(a7,Q7YesButton.Text);

If(a2="YES",If(a3="YES",If(a4="YES",Set(result,60);Navigate(Result_3,Fade),If(a7=Q7YesButton.Text,Set(result,60);Navigate(Result_3,Fade),Set(result,40);Navigate(Result_4,Fade))),If(a4="NO",If(a7=Q7YesButton.Text,Set(result,60);Navigate(Result_3,Fade),Set(result,40);Navigate(Result_4,Fade)),Set(result,60);Navigate(Result_3,Fade))),If(a4="NO",If(a7=Q7NoButton.Text,Set(result,0);Navigate(Result_6,Fade),Set(result,40);Navigate(Result_4,Fade)),Set(result,80);Navigate(Result_2,Fade)));

Collect(SelfAssessment,{EmployeeName:User().FullName,Latitude:Location.Latitude,Longitude:Location.Longitude,Date:Now(),Q1:a1,Q2:a2,Q3:a3,Q4:a4,Q5:a5,Q6:a6,Q7:a7,Result:result});

A Submit button is associated and based on different responses to survey answers are captured as global variables and once the survey is completed and button is pressed Collect() is used to push the data back to the table. 

[WarrenBelz]

Hi @NamitaBadwal ,

Firstly I am assuming that that massive collection of nested If statements produces the Variable Result consistently - you could reduce the size of that considerably with some brackets around And() && or Or() || statements.

So that leaves the Collect Statement. It should work, but you can try this - also ensure all column names and types line up with what you are sending.

Patch(
   SelfAssessment,
   Defaults(SelfAssessment),
   {
      EmployeeName:User().FullName,
      Latitude:Location.Latitude,
      Longitude:Location.Longitude,
      Date:Now(),
      Q1:a1,
      Q2:a2,
      Q3:a3,
      Q4:a4,
      Q5:a5,
      Q6:a6,
      Q7:a7,
      Result:result
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @NamitaBadwal ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[NamitaBadwal]

Hi @WarrenBelz ,

I tested with multiple approaches. Yours including. The one thing which came to my attention was if I was creating a collection and then pushing the data to the drive, it would sync in real time. Previously, I was sending the data directly to the drive. I am not sure if this should be the case that if I send any data to excel drive it wouldn't update just because a local collection is not created because the previous code ran successfully for Google Drive. 

Also, I wanted to ask if there is any way around to sharing of the OneDrive excel with users, because the plan is to collect few personal info which we would not want others to access. If we do not share the file then their survey results do not get collected in the excel. If we do the same survey in MS Forms then it is not required to share the excel access with them. Am I missing something here? 

[WarrenBelz]

Hi @NamitaBadwal ,

Firstly updating from a collection or directly from control values will both update the data source immediately. You do have to refresh the data source in PowerApps at times to see it. The Collect function you were using to directly create new records should not be confused with creating a local Collection. You can also (as per my example) use Patch for this.

To your question on data source. No matter what the source, it has to allow users to be able to write to it and in the case of Excel, this means sharing the file. I have been told that it may be visible to some users in "Shared with me", however my testing has revealed this does not happen (at least in our case) if the file is shared only in the Manage Access > Advanced Settings > Grant Access (and do not provide the sharing link).

If you have access to SharePoint however, I would urge you to consider using this as a data source as a lot of the data limitations and read-write Excel issues simply go away. You can also easily "hide" a list by not putting in the site navigation or (as I have) put on a sub-site that users would not know the name of and share at List level.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[NamitaBadwal]

Hi @WarrenBelz ,

Thank you for clarifying about my doubts. I will try the solution you provided as well.