User email casing is inconsistent

Please make email casing consistent between the auto populated Users sheet and the Form special value “User’s email address”. Currently when a user logins in to a Public with Email app as “John.Smith@gmail.com” these are the results for each:

Users Sheet: john.smith@gmail.com
Forms Special Value: John.Smith@gmail.com

This casing mismatch makes it difficult to create Relations between Users and other related sheets that use the Form special value. Either they both should use the lowercase email or they both should use the original casing to prevent a lot of unnecessary headaches and workarounds.

For example, my user logs in to my app, automatically creating a ‘Users’ entry. Then they complete a ‘Report Incident’ form that uses special value ‘User’s email address’ to populate a ‘Created By’ field. I now want everyone (not just the user that created the entry) to see this new item and be able to view the creators profile. I create a relation between ‘Incident: Created By’ & ‘Users: Email’ but won’t get a result because the email casing doesn’t match.

One of the most frustrating aspects of this inconsistency is that creates intermittent problems that are easily missed when testing. I might impersonate a user using an all lowercase email so the relation will work but when the user logs in with slightly different casing it won’t work for them. I had to learn about this problem the hard way after deploying.

As a workaround, I have to create ArrayColumns with LOWER functions on every sheet that uses the form special value:
= { “Email (Lower)”; ARRAYFORMULA( LOWER(A2:A) ) }

3 Likes