The point of it isn't really to be appealing, and not everyone has the same experience - supposedly with ayahuasca you can get feelings of community, but I've never had the privilege of communing with it.
It's not fun, per se. At least, it's not fun for me.
If you don't mind me asking then, what is the point?
Your consciousness exists in a brain that is constantly kept in balance by a thousand negative feed back loops. Some of these loops govern your perception, some govern your reactions, heart rate, etc. etc. Psychedelics act on the loops that govern your senses and the way your mind actually perceives the world, which is by no means a straightforward process.
Which is kind of the point - everything you see, smell, taste, touch and think seems static and normal because your brain has always operated in this narrow band (Unless you're dreaming). These drugs go into specific loops or groups of loops and push them further out than they normally go, allowing your entire conception of the way the world works to shift. Often people imagine it as if you're derailing your train of thought, as if something destructive is happening, as in alcohol. What I believe is happening is you're changing the rail that your train of thought is going down. Your mind continues to function, but in an altered way.
To me, curiosity and knowledge that these are not harmful drugs is enough for me to try it. To look further, it's clear from evidence that there are beneficial effects to be gained from certain hallucinogenics. LSD has been used in treating addiction to cigarettes, and is probably beneficial in treating other kinds of addictions as well. Psilocybin has been successfully used to treat depression. What's been found is that these substances have potent lasting neurological effects - they increase neuroplasticity. This allows your brain to make more new neural connections, which has implications in terms of your ability to learn new things and change your behavior. To this point, studies have mostly only been done on the substances that can be synthesized such as the aforementioned and DMT, but newer studies are researching more complex compounds such as ayahuasca.
It's very exciting.
It's mind-expanding in a way that nothing else i've ever experienced is.