Activision's titles, except maybe Hidden Evil, are all classics and are great for both Trekkies and fans of the respective game genres themselves. Of other publishers' games, Starfleet Command 1/2/OP and Birth of the Federation are highlights. While the games' storylines are non-canon, they were made with a lot of respect to the source material, so from a fan's perspective you shouldn't be disappointed with the use of the license.

Of those that are on GOG:

- Bridge Commander is the closest thing to a "definitive" Trek game in its idea - it combines a narrative close to the source material with a very good space sim, meaning you don't get only diplomatic/exploration nor only combat missions - you get them interchanging. The campaign is decently long and quite engaging, just don't get turned away from the notoriously annoying first officer. You can change between first-person bridge view and third-person space view at will, and even directly control the ship (you may need remapping the controls) instead of handing it over to your tactical officer and helmswoman. There was also an official extension for a contemporary voice recognition system that I'm not sure works anymore.
Protip: don't download non-visual mods before finishing the main storyline - every single mod out there unbalances the values so badly that some missions are straight-up unbeatable unless you play the unmodded game.

- The Starfleet Command series was the turning point in Trek games stopping being "just another licensed tie-in". It came before Bridge Commander and is more of a tactical space combat game. I'd strongly suggest starting with SFC1, as SFC3 is overly simplified. If you're on Windows 10 or 11, the first post of this thread is a must-read for setting it up, otherwise it'll crash almost instantly.

- Armada 1 had a lot going for it as an RTS, as it had unique crew and subsystem mechanics along with the ability to capture/assimilate enemy ships/stations in an extent that only Homeworld had at that time. No ship type was useless, especially when you got upgrades going. As an RTS fan, I look back at it and I'm impressed at how many then-new mechanics they managed to combine successfully, even back when 3D strategy games were a rarity. The campaign storyline is not complex but it's presented in a very good way (a "journey" over "end result" sort of deal, if that makes sense), despite the game's intro spoiling an unprecedented 80% of the campaign XD

- Armada 2 tried to expand on the original but ended up watering down the experience - there's now too many ship classes, the introduction of the Z-axis in movement and station placement was more of a hassle than it was useful, they tried to accelerate game pace but cost balancing and unit longevity is worsened, even voiceovers are half-assed. It may sound harsher than it actually is since the game is not bad by any means (it's a LOT more stable and technologically advanced than the original, if nothing else), but it had the disadvantage of coming after the excellent original and not being above that bar. It did, however, spawn an excellent and quite talented modding community.

- Elite Force 1 and 2 - just get both. The first one is, for all intents and purposes, a Trek-coated Quake 3 but with an excellent campaign and alternate fire (down-times taking place on Voyager and being able to move and interact with it was a highlight too), it was even liked by people who enjoyed shooters in general but weren't Trekkies. The second one is also good, if with less inspired enemies and environments but technologically superior. Considering they were made by Raven Software and Ritual Entertainment, respectively, you can expect a good time.