The most powerful option that should work with an Optiplex 780 SFF is the MSI GTX 750 Ti Low Profile (Amazon: https://www.amazon.co.uk/MSI-NVIDIA-750Ti-Dvi-D-Graphics/dp/B00SOXMPJY/ref=sr_1_1?ie=UTF8&qid=1502144483&sr=8-1&keywords=gtx+750+ti+low+profile). It can actually handle modern AAA games at usually low-medium settings, but your CPU probably a C2D or C2Q will be the primary bottleneck. I have done this upgrade myself on my own Optiplex 780 SFF.
With this 750 Ti, I could play CS:GO with over 100 FPS maxed out, Dirt Rally at 90 on high settings, Insurgency at 90 maxed out and War Thunder at 90 on a mixture of medium and high settings. The card itself is capable of much more, but my CPU (C2Q Q9650) was the primary limiting factor in performance.
Alternatively you could go for one of the many GT 1030s that are available as long as it is low profile. The performance-per-pound is possibly superior, and thermals should be better, however I can testify to the 750 Ti working.
On a side note, to improve your gaming performance in general you may want to invest in a C2Q if you are currently using a C2D. The Q6600 is available for dirt cheap on Ebay used.