We'll talk about open and closed-cell foam. Where would you and where wouldn't you use it and the most pressing question is would either one of them soak up water? Now open-cell foam is usually big and puffy whereas closed-cell foam isn't. If you've used them before you'd know that they have very different insulating properties.
Open-cell foams have a typically much lower R-value, say somewhere around 3.2 per inch, and closed-cell foam is much higher, usually somewhere between the R-value of 6 or 7 depending on the manufacturer or the brand.
So open-cell foam has a lighter density compared to closed cell, so it happens to be all the time that someone would love to poke their finger into the open cell foam which is quite possible. But not in case of the closed-cell foam, its densely packed molecules prevent you from piercing almost anything. This is also why closed-cell foam is said to add structural strength to the house or any kind of building foundation.
And now to address the actual question in mind – how water-resistant or waterproof is closed and open-cell spray foam?
A small test was conducted where water was poured over open-cell foam and closed-cell foam separately and kept overnight. The next morning when the open and closed cell foam was being analyzed you notice that the open-cell foam absorbed the water whereas no water gets absorbed by the closed-cell foam.
There you have it, you'd want to use closed-cell glass wool insulation sheet in damp and wet areas and open cells in relatively dry areas.