Copy entire rows from one sheet to another if certain cell in first sheet does NOT contain certain text value

Question

I have two sheets.
The first one has incoming data that dynamically updates. The fields are Name, Last Name, Email.
I want to duplicate the rows in a second sheet but only if the email field in sheet 1 does NOT end with or contain "@gmail.com".

Any idea on how can I accomplish this?

Answer 1

With importrange + regexmatch

Don't forget to allow access to connect the two sheets.

=filter(
IMPORTRANGE(SHEET URL,"Sheet2!A1:A3"),
not(arrayformula(regexmatch(IMPORTRANGE(SHEET URL,"Sheet2!A1:A3"),"@gmail\.com")))
)

Answer 2

...The fields are Name, Last Name, Email. I want to duplicate the rows...

Please try the following formula (where Col3 is the Email one):

=QUERY(IMPORTRANGE("spreadsheet_url","range_string"),"where NOT Col3 contains '@gmail.com'")

(Do not forget to adjust the formula ranges accordingly)