The Bay Area is basically the epitome is car-based planning gone horribly, horribly wrong. I am told you have similar circumstances in Atlanta and Houston, but I haven't seen them first hand.
I think it varies, actually. Nearly everything on the peninsula, and everything in the East Bay south of Oakland is pure car dependence. But SF, Oakland, and Berkeley grew up in the streetcar era and are still walkable with decent rail and bus service that can be readily improved when the cheap oil interval finally ends. The string of little towns from Concord to Walnut Creek to Alamo and Danville have walkable nuggets at the core of their sprawls and these can easily be connected by streetcar on the old Santa Fe rights of way if anyone wants to. Livermore also has a proper downtown and even mainline rail service to San Jose. Plus Livermore is dead level and you can bike all over it in no time.
When driving becomes too expensive there is definitely going to be a lot of abandoned sprawl but there's also plenty of decent places to live. By definition, few people live in the sprawl. It's a shame that they wasted so much of their and your money building out those subdivisions in the middle of nowhere, but that's a sunk cost now.
The Valley sort of is a transit problem, more than SF. Although SF's lack of a crosstown subway doesn't help: the N-Judah streetcar plus the Geary bus, between them add up to a pretty poor crosstown transit situation. But the Valley explicitly opted out of BART and used the money to build county expressways instead (San Tomas, etc.), which is one of several reasons that it's a sprawling mess.
NIMBY-ism is also a problem in the few places of the Valley that do have decent transport, though I think it's the secondary problem. One place it's noticeable is Palo Alto: why isn't there high density housing near the Palo Alto Caltrain station (which is also conveniently near Stanford and a number of tech companies)? Because Palo Alto homeowners don't want anyone with less than a $1m house to live there. However afaik this is a minority situation in the Valley, and most of it just doesn't have decent transit that could attract high-density housing in the first place. San Jose has also been more development-friendly, although it's too bad there that the VTA light rail is so near-useless, or that area could plausibly have a more urban feel.
Isn't a big problem in the Bay Area is that a lot of people living in SF don't work there? In that case, if everyone in google lived next to the googleplex that would free up their appartments for the guy working at the deli downtown.
The daytime population of San Francisco is much higher than the actual number of residents, so no, this isn't the problem. If the daytime population was lower then you'd be on to something.
Google has repeatedly tried to stimulate residential construction in Mountain View but the city of Mountain View just doesn't want it. We're all waiting for windshield perspective baby boomers to just hurry up and die so we can have proper urban forms on the peninsula. And I'm not talking about high rise, I'm talking about 3-5 stories over street level retail. Mountain View currently has lots of single-story and surface parking lots, even in their "downtown" which I reckon to be at Castro and Villa.
You better fix the housing situation first in some areas (cough Bay Area cough).