Even though many of the games are dubious in quality, I have a soft spot for this series.
Anything pre-Goldeneye sucked. I picked up 007 The Duel for Genesis and could not play for more than a few minutes.
N64 Goldeneye was amazing when I was younger, but nearly unplayable now... so sad
I loved the World is Not Enough for PS1 as a kid, even though in retrospect, it's a pretty horrible game. N64 version is soooooo much better, wish I had played that version instead growing up. One of the best Bond games ever made. Tomorrow Never Dies for PS1 is a stinker too. Still played them both for many many hours. 007 Racing was pretty blah.
I loved Agent Under Fire and Nightfire on PS2 growing up, decent FPS games. Both had incredible multiplayer modes, played a ton of Nightfire deathmatch as a teenager.
Everything for Nothing was my personal favorite, a really fun game with a lot of replay value and a really awesome co-op mode. I spent so much time trying to get the highest scores on every level. Back when I actually had time to really deep dive into a game... I've heard From Russia with Love is actually decent, I have it but never played it. Goldeneye Rogue Agent was dull/boring.
The PS3/360 gen dipped in quality. I thought Blood Stone was pretty solid, but a step down from Everything or Nothing. That one is probably still worth playing. The rest mostly suck. Quantum of Solace was meh. Once Activision turned them into Call of Duty clones I quit playing them. 007 Legends is terrible and really weird. It adapts the plots of classic movies but treats them as if Daniel Craig is Bond and that it takes place in present day. It's honestly confusing, and then it plays like Call of Duty Modern Warfare. The Goldeneye remake is similar, but slightly more fun than Legends.
We probably won't ever get a decent Bond game again, which is really sad.