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

pull excel content based up on the user login to the App

Hello Everyone,

 

DId anyone tried below scenario before? Is it even possible?

 

we have a PowerApp with whole bunch of screen. However on landing screen we can to show 4 fields form the Excel file based up on the user login to this APP. it can be based up on user email.

 

Excel field names: Goals, answers, question3,question4.

 

I tried to import or add as an data source of the excel file called "test" however it is throuwing an error that there is no table found. But the thing is that I made the content as "Tabel" and named it as "testtable" and saved it to OneDrive too.

 

Not sure what I am doing wrong.

 

I would like to achievce two things

1. import excel data file as a data source to the form

2. on the landing screen based up on the user login/email ID it should lookup the excel file and publish/pull those four columns in read only mode.

 

ANy help would be much appreciated!

11 REPLIES 11
Super User
Super User

Re: pull excel content based up on the user login to the App

HI @rajkumar88 

 

Firstly, please remove the connection to your excel sheet on one drive, now close the app and clear the cache of your browser and do a re-login. 
 
When you are logged in, try to create a new connection to the one drive and since your excel table is formatted as table, it should pick the table. Please make sure the excel sheet is not opened anywhere else in edit mode.  If you are still not able to see the table, please create a new file and insert a table inside of it with all the data and then try to create a connection.
 
Once the connection is created successfully, now you can add a Display Form and select the attributes that you want to show by selecting Edit Fields in the right menu.
 
Now to show the details corresponding to current user, please modify the "Item" property as:
LookUp(testtable, emailcol = User().Email)
 
Here, testtable is the name of table in excel sheet and emailcol is the column in which we want to search current user. User().EMail, gets the email id of current user, in case you have mapped it with the full name, please use User().FullName
 
Hope This Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
rajkumar88
Level: Powered On

Re: pull excel content based up on the user login to the App

@yashag2255,

 

Thansk for the solution. What is use Email is in multiple rows? For example if UserA has multiple rows with different information, can I display on the screen of all these information? as per below screen shot, I wasnt to display objective and description of three rows for UserA on the form.

 

ex: power.PNG

rajkumar88
Level: Powered On

Re: pull excel content based up on the user login to the App

and also do Ineed to provide read only access to each and every user on "'ONEDrive" location so that they can be able to view on the scree? or it doesn't require that level of permission? I don't want users to see the other's data from those columns by accessing that excel file. Please advise.

Super User
Super User

Re: pull excel content based up on the user login to the App

@rajkumar88 

 

If you want to pull multiple records at a time, please use the filter query instead of lookup.
 
Expression: 
Filter(testtable, emailcol = User().Email)
 
Please make sure that you are using a Gallery/ Data Table control to show these results, since Filter return a table consisting of all the rows.
 
Also, there is no need to provide any permission to the excel until you are doing a patch or submitting an edit form. You are just filtering the records in your apps using the Filter/ Lookup formulas.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

rajkumar88
Level: Powered On

Re: pull excel content based up on the user login to the App

@yashag2255,

 

I was able to pull data with dummy excel file. However I have an excel file which is more than 3MB and PowerApps will allow only 2MB of content for each tabel. Can I create two excel files and lookup from bothe files and then display the information? Can you please let me know how can tweak abbove functiont hat you have mentioned?

Super User
Super User

Re: pull excel content based up on the user login to the App

Hi @rajkumar88 

 

 
Yes, you can create two excel sheets and to make it a bit convenient you can collect both excel's data into the local collection at app start, this way you will have all the data at a single place and you can reference the same collection throughout your app.
 
You can use this expression at the app start as:
ClearCollect(MyCollection,ExcelDataSource1);Collect(MyCollection,ExcelDataSource2)
This combines data from both the sources.
 
Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
rajkumar88
Level: Powered On

Re: pull excel content based up on the user login to the App

@yashag2255,

 

can you please explain  me a bit more? I am bit confused here. So I need to use clearcollect on "App start" right?

 

And then how can I display content based up on this?

 

I created a Gallery on a screen and a display form on the same screen. EX: Gallery will be on the left hand side and on the right hand side there will be a display form. The reaSON why I did like that is for user to view his/here lin items on same screen itself. IF they select from the gallery "using enxtarrowicon" then on the right hand side it should show detail information.    Not sure how to achieve this?

rajkumar88
Level: Powered On

Re: pull excel content based up on the user login to the App

@yashag2255,

 

I have added clear collect on "App start", it does show the information. However it shows information of email starting letter from K only. I have emails starting from letter A.

 

My code: ClearCollect(Mycollection,Goals_final);ClearCollect(Mycollection,Goals_final2)

 

Goal_final: it has columns "Email", "Objective" , Objective_description"

Goal_final2:  it has columns "Email", "Objective" , Objective_description"

 

Both the excel sheets have similar column names.

 

What Ihave noticed is that if append "Goal_final" to mycollection before goals_final2 as per my above code, then it is showing results only from 'Goal_final".

Not sure how to fix this now.

Super User
Super User

Re: pull excel content based up on the user login to the App

@rajkumar88 

 

So basically instead of referring to two data sources, we are collecting the results in one collection.
 
eg. ExcelSheet1 has 2000 records and ExcelSheet2 has 1500 records, so we are creating a collection at app start, that will have all the 3500 records at one place, i.e. inside the same collection.
 
Now you can use this collection in your gallery items directly.
 
Regarding the issue where you are seeing only goals_final2 records, it is because of second clearcollect, clearcollect removes all the existing data and fill in the new records, you need to modify your expression on app start as:
 
ClearCollect(Mycollection,Goals_final);Collect(Mycollection,Goals_final2)
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 80 members 4,639 guests
Please welcome our newest community members: