User Email authentication error

[Sarah_Keown]

Hi all, trying to add a new user to the app. He has an apostrophe in the email that I think is preventing login? At the moment I have no other explanation for this.

I went through the normal process of adding a user, at first I thought the email wasnt correctly pasted into the field but after re-adding it was clear it was. The user sent me the screenshot from below. (I edited out full email/appsheet id) Just for the purpose of revealing the error message and email.

Is this a common issue? Or what can I do to resolve. Thanks

[Steve]

Email addresses may not contain apostrophes.

[LeventK]

@Steve
@Sarah_Keown
According to RFC 3696 apostrophes are valid as long as they come before the @ symbol. However the latter is, Google rejects any emails containing an apostrophes because they are considered as SQL injection devices. You may also read this:

en.wikipedia.org

Email address

An email address identifies an email box to which email messages are delivered. A wide variety of formats were used in early email systems, but only a single format is used today, following the specifications[a] developed for Internet mail systems since the 1980s. This article uses the term email address to refer to the addr-spec defined in RFC 5322, not to the address that is commonly used; the difference is that an address may contain a display name, a comment, or both. An email address such a...

[Henry_Scott]

I also have challenges with typos in Email Addresses.
According to me reading up here’s a list of characters that cannot be used in an email address - like two “@” signs. Also other characters like "!, @ # $ % ^ & * ( ) ? ~ ’ " cannot be used in an email address… basically only alphanumeric and numeric and “@” and “_” and “-” and “.”
And an Email address should also not end with “.”

@Steve and @LeventK How would one put this in a Valid_If expression?

[Steve]

There’s no good way to do the complex data validation you’d need for this using expressions. Your best bet is to capture the email address into a column of type Email, which does some validation.

[Henry_Scott]

@Steve it is already captured in an Email Field and this only Tests for the “@” and “.” character as far as I know

If I use this formula in the Valid_If,
IFS(CONTAINS([_THIS], “@”), CONTAINS([_THIS], “.”), TRUE, FALSE) ,
and add all the characters like "!, @ # $ % ^ & * ( ) ? ~ ’ " in there, will it work?

With a Validation Error of,
IFS(NOT(CONTAINS([_THIS], “@”)), “The Email format must contain a ‘@’ sign”, NOT(CONTAINS([_THIS], “.”)), "The Email format must contain a ‘.’ sign " )

[Henry_Scott]

@Steve I tried IFS(CONTAINS([VisitorEmail], “@”), CONTAINS([VisitorEmail], “.”), TRUE, FALSE) and it Validates, but as soon as I add another CONTAINS in, E.g. CONTAINS([VisitorEmail], “!”), then I get a syntax error that IFS is used incorrectly

My formula —> IFS(CONTAINS([VisitorEmail], “@”), CONTAINS([VisitorEmail], “.”), CONTAINS([VisitorEmail], “!”),TRUE, FALSE) — gives a syntax error —> “IFS function is used incorrectly:Inputs to IFS() must be one or more condition-value pairs.”

[Henry_Scott]

Henry_Scott:

@Steve - ignore my syntax error. Formula should be —> IFS(CONTAINS([VisitorEmail], “@”), TRUE, CONTAINS([VisitorEmail], “.”),TRUE, CONTAINS([VisitorEmail], “!”), TRUE, CONTAINS([VisitorEmail], “#”), TRUE)

[Steve]

What are you trying to accomplish with that validation?

1. If the email address contains @, the address is valid.

2. Only if (1) fails and the address contains ., the address is valid. But it’s not: it doesn’t contain @.

3. Only if (1) and (2) fail and the address contains !, the address is valid. No, it’s not.

4. Only if (1), (2), and (3) fail and the address contains #, the address is valid. Nope.

So I’m confused.

[Henry_Scott]

@Steve I want to validate if the Email Contains Invalid Characters like "!, @ # $ % ^ & * ( ) ? ~ ’ " and also ensure that it contains at least the “@” and the ". "
My application of the solution using CONTAINS() is confusing you, apologies, but I am not a developer and skilled in coding logic

[Steve]

Try:

AND(
  CONTAINS([VisitorEmail], "@"),
  CONTAINS([VisitorEmail], "."),
  NOT(CONTAINS([VisitorEmail], "!")),
  NOT(CONTAINS([VisitorEmail], "#")),
  NOT(CONTAINS([VisitorEmail], "$")),
  NOT(CONTAINS([VisitorEmail], "%")),
  NOT(CONTAINS([VisitorEmail], "^")),
  NOT(CONTAINS([VisitorEmail], "*")),
  NOT(CONTAINS([VisitorEmail], "(")),
  NOT(CONTAINS([VisitorEmail], ")")),
  NOT(CONTAINS([VisitorEmail], "?")),
  NOT(CONTAINS([VisitorEmail], "`"))
)

[Henry_Scott]

Steve:

AND( CONTAINS([VisitorEmail], “@”), CONTAINS([VisitorEmail], “!”), NOT(CONTAINS([VisitorEmail], “!”)), NOT(CONTAINS([VisitorEmail], “#”)), NOT(CONTAINS([VisitorEmail], “$”)), NOT(CONTAINS([VisitorEmail], “%”)), NOT(CONTAINS([VisitorEmail], “^”)), NOT(CONTAINS([VisitorEmail], “*”)), NOT(CONTAINS([VisitorEmail], “(”)), NOT(CONTAINS([VisitorEmail], “)”)), NOT(CONTAINS([VisitorEmail], “?”)), NOT(CONTAINS([VisitorEmail], “`”)) )

Works like a charm @Steve. Maybe add it under Tips&Tricks
Thanks