It appears to be a per-device issue that then applies to all apps accessed on that device.
While working on an app today, before anything got weird, I cleared my browser cache a couple of times to try logging in with different email accounts - I hadn’t yet realized how to add the menu-based logout button.
At some point I started getting the same error as OP described.
If I try logging in on a different phone or browser it work fine.
If I try any email address on any (?) app on my “bad state” phone, it says “not allowed”. To test that I made a new app with a brand new spreadsheet backing it and immediately encountered the same issue.
My apps are set to “Public with email” privacy.
And like OP, it does send me a PIN each time before giving me the error message.
The device having trouble is an iPhone X. I’ve tried clearing Safari web site date, closing all tabs, killing Safari, and rebooting the phone. Nothing helps.