How much water would it take for drywall to get soft enough to pierce a hole through it with a finger or screwdriver? If it was a leak?

Not much.
More important is how long the drywall has been wet.
It takes awhile for water to soak through (hours if there is a LOT of water, a few days if there is less).
Once it's soaked through, you have the situation you are describing.

Just a tiny, pinhole leak will soften the drywall - where it is wet - in less than a week
UNLESS you have water-resistant drywall (which normally is only installed in bathrooms).

multiple leaks over a year or more is likely -- unless you had a flood for days

100% DRY drywall can be punctured with a screwdriver.

your drywall is our plasterboards ..leak behind ?  10 minutes ..its a very soft material wet