The only times I've used spies it's been in these situations:
1. Military Plans - I saved my gold and stole the plans of an enemy empire whose military was pretty close in strength (numerical AND unit type) to mine, to see if I could blitzkrieg the nearest cities and get them reinforced before he could get defense or his own reinforcements in position.
This was most useful because his empire was split between two continents, and I found out after the theft that 2/3'rds of his military was on the "wrong" continent
.
2. Propaganda - You know that really annoying AI thing where it will stick cities in any open space in the middle of your empire? Well, this is the one time I've found propaganda to be useful, especially if it's right after you've been fighting them. If you're trying to consolidate new territory that you captured from him, or from a 3rd party, and the enemy AI is bringing in settlers and new cities, propaganda works pretty well when that city is pop2 or 3. It's not too expensive, you'll have cities (preferably with at least one rushed temple/library for culture) nearby, and you can just build a settler/worker and abandon the city if it flips and you don't need it, since the AI won't have usually rushed its improvements.
3. Sabotage Production - I've only used this once, and it was a serious risk. I was in a neck-and-neck space race with the Persians once, we were both on the next-to-last pieces, and to get the money I needed for the sabotage I had to leave only my cap city in production while everything else was turned to wealth. If it hadn't worked I think the only way I could have won was to go to war with the Persians and get a historical vic. But I sabotaged production in his capitol successfully - wiped out 15 turns worth of production with only 13 turns left in the game, and Xerxes fell 1 piece short of Alpha Centauri
.
That's about it for me. Most of the stuff just isn't worth the money, and I have yet to see evidence I've been spied on by the AI.