I hava never heard of such an occasion. Absence of evidence is of course not evidence of absence, but in this case I think we could say this is a myth.

The German helmet of 1914 was not made of metal, but of boiled leather. The spike was of metal, and was fastened by metal decorations in the leather. Trying to charge someone head on with the top of your head at their direction would not only expose your neck, shoulders and back to any kind of counter-blow, it would also render you unable to see your enemy and react to any of his movements.

The helmet itself would not be able to take such punishment - it was a decorative headgear, not a steel helmet designed to protect the wearer from blows or shrapnel like the later M1916 Stahlhelm was. Charing to drive the spike of the pickelhaube into something would probably drive it into your own skull as much as it drove it into anything else.

German soldiers used their long bayonets, their rifle buts, bayonets fixed to their rifles, sharpened trench tools (pickaxes and shovels) and trench bludgeons and clubs for close quarter fighting, all of which would be much more effective than using the pickelhaube.

It is worthy of note that the German artillerymen of the period wore helmets with a cannonball where others wore a spike. So, no the spike was not a weapon.

It did have several purposes used to disguise the fact it just looked really cool. The earliest versions may have served as means to attach streamers and other fancy stuff to your helmet. The spike also made the wearer look taller, which is common in lots of military headgear.

I have always thought its best explanation was it could serve as a deflector of a sword aimed that the dome of the head. (The WW1 French helmet had a ridge to serve the same purpose, but to deflect shell fragments)

In any case, the question addresses a matter of fashion and I suspect it is best not to over-think it. It looked neat and that was probably the main idea.