The automatic "sign in with Google" prompt only shows up if you're already signed-in. So if you're not signed-in, it doesn't (and can't) display - because there's no logged-in session, there's no existing user.
If you use extensions like Privacy Badger or other ads/tracking blocking extensions, it blocks that by default so you may not have seen this on the web, though if you use certain apps on mobile, you may still have seen it.
I haven't been keeping as close an eye on this for the past few years, and apparently I am partially incorrect on this because my knowledge is not complete.
ITP 2.3 update a few years ago broke this feature, so on browsers with ITP - basically Safari and Firefox and Chrome on iOS - Google was essentially forced to change the UI, so that website owners implementing this feature now have the choice of either always displaying the unconditional dialog button like your screenshot, or not displaying at all on such browsers. Predictably, Google chose the default to be to display, and I would assume most website would not turn it off.
So both you and my original comment are conditionally correct. It works as I described on browsers without ITP 2.3, namely Chrome (except on iOS) and Edge (IIRC). On Safari and Firefox, it sounds like it would work as you described - I haven't tested this on those platforms yet myself but no reason not to trust the doc here.
In case you're misunderstanding what we're discussing here - the feature in question is https://developers.google.com/identity/gsi/web/guides/offeri... .
If you use extensions like Privacy Badger or other ads/tracking blocking extensions, it blocks that by default so you may not have seen this on the web, though if you use certain apps on mobile, you may still have seen it.