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 · Accepted solution]

@AnonymousSo...lot's of questions in this one going on!!

First - The Date with Times.  You can delete the time portion from your Form.  You will need to unlock the DataCard and then either make the HourValue, MinuteValue and Separator not visible, or remove the MinuteValue and HourValue controls.  Keep in mind if you remove, you will need to adjust the values of the other items in the DataCard as they rely on those for some formulas. Just keep clicking on the red x's and fix.

(By the way...the minute control is one of my favorites, only because I will throw a form with one up sometimes JUST to get its items list...I hate typing them all out - look at it, you'll see what I mean)

Second:

Because you are a PowerApp that is on the Office 365 platform, your app will have access to the full Office365Users list out there in order to collect and/or feed values and lookups in your data.

Third:

Currently, using an Excel file from OneDrive will have the Excel file being in your OneDrive.  In order for others to access it, you will need to not only share the App with them, but also share the Excel file with them through your OneDrive.

Hope this fuels some thought.

[Anonymous]

@RandyHayes 

Hey there again, yeah, well, like I said the first time, totally new user of PowerApps 😄 Anyway, on to topic:

Q1:
Will look it up. In the edit/new one I'll put them in false (visible) then, the 'detail' view should have it too, no? Also, between not visible and deleting, better not visible or doesn't matter much?

Q2:

How do I make a search/combobox/list that lets me search for users? When I used it on Sharepoint didn't have problems since the column in SP was configured for 'users' in the organization thus, I suspect, the setting/configuration was exported to PowerApps. But, starting from PA, well, no clue there. Any help would be appreciated.

Q3:

When sharing the Excel spreadsheet though, the person has to have permissions to edit or just view? If I want the person to be able to add new entries to the list via the app.

[RandyHayes · Accepted solution]

@Anonymous 

Q1:
Will look it up. In the edit/new one I'll put them in false (visible) then, the 'detail' view should have it too, no? Also, between not visible and deleting, better not visible or doesn't matter much?

Yes, I would just make them not visible in all places you don't care about them.  

Q2:

How do I make a search/combobox/list that lets me search for users? When I used it on Sharepoint didn't have problems since the column in SP was configured for 'users' in the organization thus, I suspect, the setting/configuration was exported to PowerApps. But, starting from PA, well, no clue there. Any help would be appreciated.

Try this, in your App, add a new screen and choose Email Screen.  Take a look at the logic in that screen.  This will have very good examples of how to implement a people search for your Office365 users.

Q3:

When sharing the Excel spreadsheet though, the person has to have permissions to edit or just view? If I want the person to be able to add new entries to the list via the app.

---

If you want them to add new entries - that would be edit.  So, they would need the permission to view and edit.

[Anonymous]

@RandyHayes 

Q1:

Alright, thank you (:

Q2:

I did the test with one of those screens, the code is, however, in a Item property of a gallery. However, the field I want to have people input user/mail, is in a Datacard inside a Form. I tried the formula from mail above, changing "textSearchBox" for "datacardvalue9" (the corresponding one), however, the error it shows is: "property expects text values, but this rules produces incompatible Table values".

Addendum: the PowerApps version I am workin on the Excel App (different enviroment) has the , instead of ; and "UsuariosdeOffice365" gets replaced for "Office365Users", as additional info.

Addendum2: the code I tried in the datacard inside the form is:

If(!IsBlank(Trim(DataCardValue9.Text)), Office365Users.SearchUser({searchTerm: Trim(DataCardValue9.Text), top:15}))

Q3:
Neato, did a test to check it, and without invite, can't see data. After invite, all peachy 😛

EDIT/ADDENDUM:

I tried with adding a combobox like seen in (https://powerusers.microsoft.com/t5/General-Discussion/Edit-view-add-field-Select-or-add-new-one/td-...), however I think the problem with following that, would be the AllowedValues, no? Since, for example, my AllowedValues in this case would be:

RenameColumns(Distinct(Table1,Responsable),"Result","Value")

By reusing same code. HOWEVER, I don't need to select distinct from the 'responsable' column in Excel (even if that is the camp that will be filled), thus, I'd remove it, but it gives error: "Column 'result' does not exist'.

Any help on this?

[RandyHayes]

@Anonymous  looks like we're just down to Q2 now...

So, for the allowed values - If you don't need them, then you need to change the control type to just a standard dropdown.  You might recall in the linked posting, when we added the field to the Form, we had to change the control type to "allowed values" - that created the AllowedValues property for us to us.  If you don't need that, then when you add the field to the Form, just have its type as a dropdown.

However, I'm not entirely clear on what you will be "dropping down".  Did you want that dropdown to have everyone in it?  That might not be practical.  

In the Email screen you added, you will see that the mechanics of it are that they have an input for the name and then fill a gallery with potential names from a SearchUser call.  There is currently no way to just get all the names in O365 to populate a list (at least not with just a call to a function).  So, you have to provide a way to search them and then selected them and then fill them in.  As you see in the Email screen, they do that and then add them to a collection as you select them.  

Depending on your purpose on the DropDown, you could do something similar and then fill in the value for the DropDown.

BUT...again, not understanding the DropDown or where the real value comes from or what it is.  As I understand it, it is a column in an Excel spreadsheet, and thus it is just a text column.  So, having a dropdown might not be what you want.  

[Anonymous]

@RandyHayes 

The ideal thing would be that the box, when writting the name of the person, it would suggest which one and be able to select it, not having a dropdown with many, many users. And also, if possible, to be able to input more than one person.

For example, I'd have:

- Name project

- Name task

- Date start

- Date end

- In charge of

Of those, all are just text in Excel, and date have well, date format. However, when the person in the form can edit an entry or add a new one, of the previous columns, project would be like the one in the linked thread that we resolved last week; task just an input text -standard, so to speak-, and finally, in the 'in charge of' be able to, as said at start of the reply, start writing name and suggest which one, and select it, thus having users/email on it. Kinda like:

(which is from the email screen, but using as example).

Is this even possible on a form? If not, a way to "select" or "use" the values of a mail gallery (myContacts? I think it was) and use those values to fill that datacard?

As an extra question, text from Excel, either username or mail, is it possible for PowerApps to recognize it as such?

[Anonymous]

@RandyHayes 

Add:
Main reason I want this is I later want to be able to filter by users, like I did with the app I have with SharePoint list. Therefore, having an item/entry linked to user(s) will allow me to filter by users, so "user1" will only see the tasks "user1" has.

EDIT:
Have a random idea which I am not sure it is possible.

What if "OnSelect" of the "in charge of" lead to the "mail screen", where user can select persons there. And after that, the user clicks a "ticket" for being 'done' and it takes to the previous screen in which the box with "responsible" now has the selected persons name in it.

To try to clarify a bit:

- Select "responsable" box --> takes you to "email default view" and then user selects person responsible of task.

- Selected people are "stored" somewhere?

- Click done and takes back to edit/new screen, where the 'responsable' box now has the values of the 'stored' data

That way, later on, if I wanted to filter by username I could make filter of "User().FullName" and some string function of "contains this string", comparing both and showing only those who do contain the string, since it is an Excel file, would be string only

EDIT2:
Or, perhaps I'm trying too hard due to Excel scope and instead for later filter use, that box should have the user input the emails of the people responsible of, manually, and then later on the filter of the gallery, well, filter according to the mail column and compare it with User().Email?

[RandyHayes · Accepted solution]

@Anonymous  So, yes, this is all possible.  However you are entering the fringes of built-in functionality and crossing over into the "development" of that capability.  This just means that you will have to craft out how you want to do this and then choose the right controls and the right actions to make it happen.

I did an App recently where we needed to dynamically search lists while the user typed and then if any one matched, fill in fields from many different sources.  If not, then automatically put them into entry mode.  If multiple then have then select.  

All very doable, but it took a coordination of controls to make it happen.  Nothing out of the box would solve that.

You might be facing some of those same situations as you move forward with the design.

For some of the specifics on this, you are dealing with a text field in Excel.  There is no concept of a person field there like SharePoint.  So, name, email, whatever...it's just text.  If you're going to do lookups to the Office 365 users, you'll need to use the information from that field or from text input.  Either way, you can do so by SearchUsers or you can specifically get information if it is an email address.

On that note - I would almost always opt for storing the user information as email rather than display name.  Email is unique to each user - names are not always unique.

[Anonymous]

@RandyHayes 

Hm, I think I am by far too inexperienced/not enough background to start building custom 'stuff' in PowerApps, so I think I'll go by input mail.

For that case, user manually enters mail in the field, the filter/lookup/search would go on the showcolumn part of the gallery no? Like, in previous help you have, well, helped me with.