Excel sheet from OneDrive: Permissions, date format & users from office365

[Anonymous]

Hello all,

Some might remember that I've been working an app with database source: SharePoint list. However, due to Tableau having problems connecting to SP, am currently trying with OneDrive+Excel sheet. Thus having an Excel sheet with different fields, two of them being date. In the spreadsheet, the dates are formatted, inside Excel, as day/month/year; however, in PowerApps it shows both in detail view of the entry & adding new one view, as dd/mm/yyyy plus hour and minutes.

The question is: is there any way to 'remove' the option to add hour/minute AND remove it from the detail view?

--

The second question is regarding user/userlist from office365. In SharePoint list I could add a column defined as person/group and it would allow me to type person name/email and it would suggest the one I'm looking for, able to select it. As far I know, this in Excel is impossible to do. Therefore, it is written manually in the spreadsheet (the examples, at least).

So, if I want to have the same option I have in my app that uses SP List as datasource, what can I do?

• Should I have a second table with all the name and emails of the people in the workplace?
• Or can PowerApps 'refer' so to speak, to the place it is "hosted", like, (business) OneDrive and from it get the users of the place?

--

The last part of the topic is regarding the permissions needed for the users when you share/publish the app. With SharePoint as datasource, I had to add them to the SP List as members in order to be able to edit, add entries, remove them, and so on. In OneDrive/Excel file, how would it work? Or is it "automatic" when you share it to people (the app)? Meaning, I create app with Excel table as datasource, I make the app, and then publish it. When I email link /add people to app, they can, without any change, add/edit/view the app content?

Any help would be greatly appreciated.

Thanks in advance,

[RandyHayes]

@Anonymous  walk before running!  You might want to build a separate app to just thrash out your ideas.  I have many "playground" apps just for that.

In this case, yes, a simple answer would be to have them enter the email.  You could then look that up and use it for any searches, filters or lookups you want.  The key is the email.  That is unique.  If you have that, it's pretty easy to go to the source and get what else you need.  Nice aspect of the SharePoint column is that you have most of that already in a People column.  No such joy on Excel.

I do recommend that you consider using SharePoint over Excel.  You will have much better results with it.

[Anonymous]

@RandyHayes

what about jogging?

as a sideq, how do you go make a custom thing? Is there a part of PA I am not seeing?

Back on track, as an idea I am kind of forming: to avoid caps and whatnot, I should probably do some text function that has it all on caps (or not), grab the whole mail and compare it to User().Mail.

Regarding the recommendation, tho, I absolutely wish I could continue using an SP list, but, a requirement is to have a connection of database (Excel/SP) to Tableau... and SP to Tableau has a problem in connection regarding some either security issues or that the login is not supported (going by what I read somewhere). So, Excel it is, sadly for me

[RandyHayes]

@Anonymousdon't forget a lot of falling in between 

So when I refer to custom - I just simply mean that you will have to "wire" together, sometimes multiple, controls and will need to put your own logic in place to make them all work the way you want.  There is nothing special about doing so in regard to creating something like that, you just need to have a clear mission.

Although - now we do have custom components available, so some of that can be put into those.

Back on topic - yes, you'll always want to use Upper and/or Lower functions on text when comparing values.  You never know what people will put in.  Lower(MyFieldValue)=Lower(User().Email)

Understood you go with what is requested on the Excel.  However, surprised to hear Excel over SharePoint in regard to security...that's a bit backward.  Anyway - be that as it may.

[Anonymous]

@RandyHayes 

Am having some problem with the code. In Excel, I have the table "DBTable", which has the columns proyect name, task name, start date, end date, real end date, in charge of mail ("Correo encargado"). I think I am not making a correct order of the filter/search for filtering by user (ie: mail). What I currently have is:

Distinct(SortByColumns(Filter(DBTable, Search(DBTable,User().Email,"Correo_x0020_encargado")),"Nombre_x0020_Proyecto",If(SortDescending1,Descending,Ascending)),'Nombre Proyecto')

(Yes, this other computer uses "," instead of my usual ";").

Can you point out what I am doing wrong there?

thanks-

[Anonymous]

@RandyHayes 

I have on start of the App:

Set(correoUsuario,User().Email)

Then, on the browseScreen1 I have a droplist with the following code (which returns empty even when it shouldn't):

Distinct(Filter(DBTable, correoUsuario='Correo encargado'),'Nombre Proyecto')

Which should give back unique 'proyect names' (nombre proyecto), and filter by using the starting variable, comparing it to the data in the column 'correo encargado' from Excel.

Afterwards, in the gallery Item code, I have:

SortByColumns(Filter(DBTable,'Nombre Proyecto'=listaProyectos.Selected.Value && correoUsuario='Correo encargado'),"Fecha_x0020_Termino",If(SortDescending1,Descending,Ascending))

Which -should- give me items that my username has, however, nothing is showing.

[RandyHayes · Accepted solution]

@Anonymousso are you dealing with case?  If so, you might want to revise as follows:

Set(correoUsuario,Lower(User().Email))

Distinct(Filter(DBTable, correoUsuario=Lower('Correo encargado')),'Nombre Proyecto')

SortByColumns(Filter(DBTable,'Nombre Proyecto'=listaProyectos.Selected.Value && correoUsuario=Lower('Correo encargado')),"Fecha_x0020_Termino",If(SortDescending1,Descending,Ascending))

If that's not working, then try to throw a label up and set the Text to correoUsario then look at your source data and make sure you are comparing EXACT instances. 

[Anonymous]

For some reason, after I saved, quit and reopen app, now it shows the data. It worked with:

Droplist:

Distinct(Filter(DBTable,correoUsuario='Corroe encargado'),'Nombre Proyecto')

Gallery:

SortByColumns(Filter(DBTable,'Nombre Proyecto'=listaProyectos.Selected.Value && correoUsuario='Correo encargado'),"Fecha_x0020_Termino",If(SortDescending1,Descending,Ascending))

I think, however, that adding the lower case will help me in the long run. Still not sure why I need to save, close and re open in order to see changes/see data. (FYI: working on a win7 HP elitebook).

As an additional note, it seems that if I use the "view online" of the Excel file, the connection kinda fails or something, because it stops showing data.

[RandyHayes · Accepted solution]

@Anonymous  ah yes...the ol' gotta save and get out and back in routine!  

When things don't seem quite right, I usually take these steps:

1) Refresh data source

2) Throw a label on the screen and set the text to something I know is a correct result.

3) Retype the variable or other value in the place that it seems to fail (yes, believe it or not, this happens often - same exact thing, one fails one works).

4) Am I relying on something that has to fire in an OnVisible or OnStart? Go there and fire it off.

5) Save

6) Exit

7) Edit again.

The "View online" should work for you...I would be suspicious. 

[Anonymous]

Not entirely sure what do you mean by a label? @RandyHayes 

Also, the F5/play to see it kinda doesn't work right, like, I use the (refresh) symbol I have and nope. Hence, save, quit, open again.

Also, help again plox.

I have my gallery which when opening an entry leads me to my detail screen. However, when I use the "go to edit" from detail to edit, it shows no element. Does detail have to have same fields than edit view? Dont know what else could be. It does take me to the proper screen, however shows no data.

[RandyHayes]

@Anonymous  A label, just a standard control.  In your example, you have a variable you are setting.  Want to see quickly if it is the value you want? Just insert a label control and set the Text property to your variable.

Your Gallery, Detail screen and Edit screen should both be set to the same data source.  Your Item property should be set to something that will return an item for it to either display or edit.

In this case, most likely your Galleryxx.Selected

Which actual fields are shown on the display or edit is up to you.  They don't have to be the same at all, but the underlying datasource does for them to both work off of your Gallery.