History suggests the US is pretty good at acting when it perceives existential threat.

I suspect that is a truism the current administration is attempting to leverage. Unfortunately / inconveniently for them, instead of focusing on actual existential threats (climate change), they've tried to rally people behind a pretend existential threat (immigration). The people smell the rat and it seems to be back-firing.

Why they don't do the obvious thing and co-opt the green energy initiative, get into a space-race equivalent with China on solar panels and wind turbines, is a mystery to me.

We were willing to spend money doing things. The whole moon landing program cost something like $300 billion adjusted for inflation. Artemis is on a relative shoestring.