I use the “Unique” function quite often to get a list of users from the App:Login sheet. The issue I’ve been having is that the Unique function is “too unique” haha. Sometimes a user will log in more than once and sometimes users log in and the first letter of their email address is capitalized. This shows as a unique value to my other sheets.
I’m curious what others do to prevent this behaviour. I can’t ensure that users always log in with the same case in their email addresses, so I’m looking for a way to take that possibility out of the equation.
Use =unique(lower())
This will convert all values to lowercase before applying the unique filter
1 Like
Wow that’s so simple. Thank you!!
Edit @Robert_Petitto : doesn’t seem to be working for me. It isn’t populating all the values, just the first row.
My current formula: =UNIQUE(‘App: Logins’!B2:B)
I’ve tried =lower(unique('App:Logins!B2:B) to no avail.
I’ve tried =unique(lower('App:Logins!B2:B)) to no avail.
Try this:
=UNIQUE(ARRAYFORMULA(LOWER(D2:D)))
1 Like
Ah…ya. Probably need to throw an arrayformula in there.
1 Like
That seems to be the trick! Thank guys, much appreciated.
1 Like
My concern now is that filtering where @User is “signed-in user” may be off as that too relies on case-sensitive-ness. In my opinion, that filtering option shouldn’t care about case sensitivity, like how actual email addresses don’t worry about it.
I don’t think it will be a problem. I think Glide takes care of that. I opened an incognito window and logged into my app with a mix of upper and lower case and the filters still recognize it’s me and matches everything that only has lower case.
Okay good to know. The editor is a different story though (which is why I assumed this was an issue). If you use a different case in the “preview as” section of the editor, it doesn’t give correct results. Maybe this is the only issue.
I’m not seeing the same issue with the Preview As. I can use mixed case and it still pick up my records.
Hmm weird it’s working fine for me too. I know this was happening though (or else I wouldn’t have made this post! Haha). I’ll keep an eye on it and post again if it happens again.
I used =sort(unique(lower(D2:D))) for mine. Sort handles the array for you.
2 Likes
They are watching us…
Maybe they secretly fixed it since you last saw the issue.
1 Like
Just be careful when using sort it unique for creating records from app:logins. If you delete records in app:logins or a new email causes it to sort differently, then you can get unsynced records in the sheet you are using the unique formula.
For example, if you are creating automatic per user profile records based on unique from the logins sheet…and then add additional columns to that profile sheet…then you run the risk of the emails not lining up with previously entered data when a new login occurs, or you clear out old records in the logins sheet.
1 Like
Good point. In my case, I was only getting a count of unique email addresses that logged in to our app. I needed to separate the whitelist email addresses from the non-whitelist addresses. it was simply a data mining exercise.
1 Like
Yeah…can’t wait for an enhancement to logins/profiles. I experience the same issue if I wanted to delete a user. Currently, I have to 1. Delete the row in the Profile Sheet and then 2. delete the user in App: Logins
1 Like