Foam is expensive, and thus for the same profit margin (expressed as a percent) there is more money. Foam thus puts a lot of money into shows like that for advertisement.

Closed cell foam is the best insulation - if it is installed correctly. You should want it as it is the best insulation. However the payoff is several decades vs much cheaper insulation and so most people find it isn't worth the costs.

In general, for most houses, putting in the most cheap insulation you can, and then investing in a heat pump is the most bang for the buck and better for the earth than the most foam you can and then using a much less efficient HVAC system (in a new house this is typically what code requires, but there are a lot of old houses with minimal insulation and a terrible furnace).

I have an interest in metal boats. I read a book written in the 1970s by a British author who went to the netherlands to get up-to-date on the best metal boatbuilding methods used by the world's experts. He enthusiastically championed sprayfoam insulation which was being used nearly universally by the dutch at the time. The boats that got sprayfoamed invariably had short useful lives and horrible corrosion problems. It is no longer considered a good insulation method in boatbuilding.

Yeah, reading this from the US, it looks weird. Our house is insulated using closed-cell spray foam installed in the late 1970's, and it has held up perfectly in the time since, in the generally wet and humid northeast U.S. We have continued using spray foam when we make additions and changes, both for consistency and because it seems to work very well.