I actually have a very in-depth theory on this. As I see it, the appeal of FPS sniping is largely due to the following factors:
--> One-shot kill > multiple-shot kill. It's just much more fun to take down an opponent with a single well aimed shot than a spray bullets. Since most weapons in the typical FPS cannot kill with a single shot (unlike real life) and the sniper rifles usually being the only weapons powerful enough to pull this off, the sniper rifle tends to draw a large following. This is one of the things that really helped to create the popularity of Counter-Strike, the fact that you could take down an opponent with a single shot with pretty much any gun, given your aim was good enough.
--> Stealth kill > regular kill. The only thing more fun than taking down an opponent with a single well aimed shot, is to do it from a hidden location where he/she had no clue what hit them. This happens much more often if you're a mile away sitting in a pile of rubbish than if you're at a closer proximity. Obviously the sniper rifle tends to be better at these ranges.
--> No scoping. People love to use a weapon/tool/object in ways it wasn't mean to be used as a way to show off their skill.
--> Coolness factor. Sniping and snipers have a stereotypical "coolness" factor based on misinformation and Hollywood glorification, along with some very legitimate true-life stories about snipers who were incredibly talented or placed in difficult situations and prevailed.
Most of the above could be prevented by including more realistic weapons in the games, hence creating more realistic game play. In the more realistic games, sniping is much more difficult and a lot less glamorous. This just isn't the intent in most games though. If you'd pay attention, you'd be surprised just how unrealistic even the supposedly "realistic" games like R6:V are.