Closed cell-spray foam used as insulation, has been on the market since about 1979, and has gone through a number of evolutionary steps. The product is a solid plastic, formed by the chemical reaction of two primary components: an “A” side which is isocyanate, and a “B” side which is usually a mixture of oils, stabilizers, fire retardants, blowing agents, and colouring agents. The reaction is exothermic (ie, generates a lot of heat), and the materials form a solid within seconds. The blowing agents are the compounds which produce the low-conductivity gas that forms the bubbles in the foam, and thereby form the primary insulation.
When applied to a minimum thickness of 50mm (slightly under 2 inches), the material acts as insulation, vapour barrier, and an air barrier. Newly-installed foam has R-values of R-6 to R-7.5 per inch, but this diminishes to about R-5 per inch over time as the insulating gas dissipates. Compared to other insulation products like fiberglass bat or mineral wool bats, the product has more insulating value per inch, and resists the loss of heating value that sometimes occurs with loose insulation due to air convection with extreme temperature differences.
The ability to prevent air and vapor movement is generally a good thing, except in situations where moisture can enter a wood structure, and then cannot get out. Therefore the short answer to the question in the header is “spray foam is good when PROPERLY installed, and BAD when installed in inappropriate places or in an incorrect manner. There are also issues of the impact on the environment, and potential impact on the health of the people living in the homes where the product is used. The rest of the post touches on some aspects that inform whether the installation is good or not.