I don't know about Stadia in particular. But, I am nearly certain that game streaming in some form will succeed within the next decade or so. The real appeal that I see is the ability to truly play console quality games on any device, and to carry that game between devices.
As a father of a young child, my ability to sit in front of the TV for a long period to play something is pretty limited. But, I find myself playing stupid mobile games much more often than I used to, as I frequently have 10-20 mins free while mom is feeding him, he's dozed off, etc. If I could jump into Assassins Creed and knock out a couple side missions on my phone or tablet, I probably would. That possibility is what has me really excited about streaming.
Of course, somebody has to make the technology work well. If it sucks, streaming won't take off, at least not in a big way. But, if Google, MS, or anybody else have really managed to make streaming work, I think it is a near certainty that it will succeed.
Note that the success of streaming does not necessarily mean the end for traditional console or PC gaming. It may mean that in the long run, or it may not. It certainly doesn't mean that in the short run.
I agree with that it will not affect much the traditional consoles. In the presentation Google made clear they are aiming for people who are currently not or seldomly play, because it is currently too inconvenient. Sure, they will take the current gamers who are interested, or people who played in the past but now have time constraint and can only play in short bursts and only if they can play wherever they are (as you describe). But their main target seem to be people usually not playing.
That's why I see the doubters n this thread as gamers bias. Here in the forums are current gamers that are OK with the current setup you need to play. This forum doesn't contain people who might play in the future if the tech is more accessible.