How poorly written does an essay have to be to be the reason for not being accepted?

At schools that require them (usually schools that will say they practice "holistic" admissions), essays are the final stage in the process of deciding who to admit or reject. At that point, every student still in consideration has passed the cutoff for academics and usually has a pretty solid EC profile, so essays do actually matter a lot in differentiating between different applicants. If school is really selective, an essay is going to need to be actively good to stand out from the crowd, and a poorly written essay—lots of grammar and spelling mistakes, generally bland or bad writing, or one that implies negative traits in the writer like racism or violence or even just something like being inattentive or lazy—can be really damaging to someone's chances.

Because of that, it's hard to give essays a real weight, but in practice they're about as equally important as someone's academic stats. If either of those are lower than what a particular school is expecting, that person likely won't get into that school, and at really selective "holistic" schools strong academics will not make up for a weak essay. (Though this also means that, if your academics are good enough to get across the cutoff, a strong essay can actually help make you more competitive versus people with higher grades/scores.)

For holistic schools it matters more but it mostly just provides insight to the writers passion. it would also give insight to your personality ex arrogant racist etc