Yes, this game can be extremely frustrating when your teammates aren't cooperating, when you have a Widowmaker on your team who can't shoot anything but keeps feeding the enemy, or a D.Va on your team who just stays in the spawn room to throw the game. It takes an insane amount of knowledge, experience, and cooperation from all players on the team to get the most out of this game.
First of all, I think people have to accept that when playing with randos, you will always get some bad eggs but the bad eggs are also equally likely to show up on the enemy team. When playing against real people, you will always lose some games, just like everybody else. I don't think this part is specific to Overwatch.
Second, it is definitely more fun with friends than playing alone. I am so passionate when talking about this game that I managed to convince a friend (who only played it on PC) to buy the PS4 version, and whether we play well or badly, win or lose, it's still more fun to play together.
The depth of the game is also what makes it so rewarding. Watching Overwatch League (or just pro games with commentary in general) really helped me learn broad strategies, as well as hero-specific and map-specific ones. There are plenty of Youtube videos out there for learning about this game and you could spend a lifetime on it if you want to master every single hero and every single map.
And I haven't even mentioned how much I love the characters: among all the FPSes and MOBAs ever made, the Overwatch heroes are some of the most fleshed-out and you can tell they were created with loving care. I had never played any shooters before Overwatch, but the heroes and the team aspect of the game (and watching my boyfriend play it constantly on Xbox) are what made me decide to jump in. I am a Tracer main and decent D.Va and Moira, and even got my bf to start playing Tracer (he thought she was too difficult to play at first).
Definitely worth the money.