The plan is for 7000 units, a lot of office space, and a bunch of other things. It needs to be midrise apartments to fit that many people. Which is exactly what they should be building.
You would be hard-pressed to afford an SFH near Mountain View on one $300k/year income, unless you've worked for at least 5 years (probably more) to save up a down payment. Even then, that's the low end - any house you can buy with that income will probably be small (1500 sq ft max), built in 1960s at the latest, and in need of $100k-$200k of renovations to bring it up to modern standards. You might be able to buy a townhouse that's decently modern, but that will charge you $500/month in HOA for very minimal amenities (not even a pool in many cases).
With these options, unless you're starting a family and need the space, these apartments seem like a good deal for younger people working for Google. It will be priced to be within a Google engineer's budget and will have much better amenities than any SFH for sale that costs below $3m.
I would prefer literally anything else over living in a McMansion in a liminal suburb. Only thing they could improve is putting some mixed use/retain in there.
It probably won't make you more extrovert. In studies, behavior has been pretty consistently found to be linked to personality and vice versa, so it's always tricky to differentiate the two.
I think it is all about what you can afford close to your work. If you are early on in your career in the bay area you are very likely living in an apartment building. Maybe you get a few friends together and rent a house.
I've lived in both poorly built apartments and very nice new concrete walled ones where you can't hear your neighbors at all.
To your point, under engineered buildings are a serious concern. Ex: I lived in library gardens in Berkeley before they had the balcony collapse in 2015 which killed 6 students.
https://en.wikipedia.org/wiki/Berkeley_balcony_collapse
Not a virtue in itself. But if you live in the bay area you are priced out of living less densely. You can of course move away to a more affordable area. And now with remote work being more accepted that is a viable option for white collar workers, definitely not for blue collar though.
But if you have kids, family, friends, in the bay area then that is a difficult decision to say the least.