I don't get the big deal about these weapons they are terrible from the looks of them no AP & you can't customize them!

I agree with you, when I got Rikku's weapon, I thought it would kill 9999 but I was disappointed once getting it.

What makes them so cool for people, I wonder? And has anyone completed a game without using them?

They ignore defence. You could make your own weapon with the same abilities as the celestials and they wouldn't be as strong, though they are also boosted (and conversely restricted) by your stats. As long as you keep your stats right they'll probably be better than the other weapons you have.

Tidus, Wakka, Rikku and Kimahri do more damage the closer their HP is to max.
Yuna and Lulu do more damage the closer their MP is to max.
Auron's does more damage the lower his HP is.

You really wouldn't need them in a normal playthrough but if you're gonna try the Dark Aeons and Penance they're pretty much essential as you won't do max damage without them.

I can't get masumanes full potential i got both the crest and sigil & its still no AP

You need the Celestial Mirror to boots the weapon's power beyone No AP.

I only got the ones for my three main characters. But that was punishment enough. :(

i still dont have lulus full up. dodging 200 in a ROW is just impossible for me.
but yes getting these weapons is pretty worth it

140 was how high I got with the Lightning bolts. Yuna's and Rikku's are about the only ones worth it to me. Once customized, the No Ap goes away and is better. For example, Rikku's Godhand, once fully powered up, has Gillionare, Double AP, First Strike (?), and something else I can't remember. Yuna's Nirvana has One MP, Break Damage limit, Double Ap, and something else I can't remember. The Murasame, once powered up, was way better for me than the Masamune. And the other's crests/sigils are just too hard to get. :(
I have to say, though, that once I got the Nirvana Yuna's stats started going through the roof! She kills all!! Yay, my worst character is now the best!

Lulu's Celestial is just plain impossible. i never tried to do it and i never will cuz i simply don't like lulu.
fortunately the characters i use all have weapons that are gotten rather easily. Auron's is easy just takes a little time. Yuna's is also easy but you need to know what you are doing because if not you might forget picking up treasures in Temples. Tidus' was easy for me as well although i'm sure many won't agree with me. the one i never figured out is Kimahri's. I just don't know what the fuck it needs. Ah who cares he's useless anyway.