hiya o/ while i completely respect your disagreement, i feel there's a few things you should note. forgive me if this is lengthier than usual as i grew up primarily on spinoffs (alongside the main series games) so i've played a fair few spinoffs so i'm pretty opinionated on this kind of stuff, haha.
so! first and foremost. it's undeniable that since generation 3, the main series has had a repetitive formula. i suppose you can extend this to since generation 1 really, but gen 3 is really when the repetitive "beat the evil team, become the hero" plot became the norm for pokemon gaming to this very day. the problem here is that game freak doesn't really... change much from this formula, with their biggest deviation being the gen 7 alola games. that said though, i'm sure you're well aware enough that while people give credit where credit is due for game freak for trying something different, some felt the change didn't go far enough. trial captains and kahunas were basically redressed gym leaders, and there was still an elite four. team skull shook things up a bit by being a self-parody of sorts of an evil team, but the aether foundation was your typical cookie-cutter evil team that we've seen in past generations. in short, sm/usum were cut from the same kind of cloth other past generation games were, and while there was some change to make things exciting (z-moves, rotom-dex, rotom powers, mantine surfing in the case of usum etc) in general, most of everything was kept the same. the battle tree was just a redesigned battle tower exactly like the old battle maison, so there wasn't a huge leap there. i guess old trainers like cynthia, red, green etc make a comeback, which is exciting, but that's about it.
anyway, what does this all have to do with spinoffs? that's what i'm about to get to right now, actually!
you mentioned that pokemon snap couldn't be compared to the main series games because it'd be an apple to oranges comparison. and i mean, you'd be largely right. pokemon snap is a different game than like... every main series game. but here is where your argument falls short -- you mentioned that it tried something unique an different, and honestly imo that's all there is to it -- where the main series games played it safe by being essentially the same (minus gen 7 if you count that as different enough but that's heavily debatable), spinoffs of the past offered a fresher take on pokemon gaming. sometimes that fresh take doesn't always work out. for example, i detest pokemon dash, because i thought it was just super boring. i respect the approach, but i feel that the execution wasn't really fun for me nor did i enjoy the games. still, it was different and unique, and that may be enough for someone to enjoy it more than the constant repetitive nature of the main series games.
if you want more of a apples to apples comparison, then take pokemon colosseum, pokemon XD, or even pokemon battle revolution. you can raise a team and catch pokemon in the former two games and battle just as you would the main series games in pokemon battle revolution, but the plot is executed so much better than what the main series games can even dream of reaching. yes, much like the main series games, in pokemon colosseum/XD, there's an evil team and you have to defeat them to be the hero, but there's more to it than that. since the plot revolves so heavily around shadow pokemon, you start to begin to learn about what shadow pokemon are, why cipher is doing what they're doing, and your journey doesn't even stop when you beat the evil team, because you still have to unlock the hearts of all of the shadow pokemon that you've caught on your journey so far. cipher isn't even the only criminal organization the player has to deal with. team snagem and gonzap are others that the player has to face. in short, while the goal is simple to beat the evil team and become the hero much like the main series games, the paths you take to get there is much more fulfilling than the plot of the main series games.
pokemon battle revolution had pretty much zero plot and, although basic, introduced character customization which at the time was completely nuts and keep in mind this didn't exist in the main series games until years later. as someone who cares a lot more about the battling side of things than trading/breeding, pbr was about the most fun i've had in a pokemon game in a long time. to this day i'm still saddened that genius sonority (the company that developed colosseum/XD/pbr) didn't bother to make a fourth game on the switch because i bet it would've been super cool.
and then we get to another example. pokemon conquest. i'm not sure if you've played conquest, but again it's another example on a different perspective on pokemon gaming done well. it's more of a strategy-based game than the main series games are but you still raise pokemon and iirc recruit (it's been years since i've played but i vaguely remember certainly details) pokemon in a similar manner to catching them in the main series. plot was different enough to be enjoyable, and about the only thing i personally didn't like about pokemon conquest was the post-game, but that's a personal thing.
and pokemon mystery dungeon speaks more or less for itself, as you've already noted =P
but yeah i mean... it's opinion anyways, but i still maintain my own opinion that spinoffs are largely more enjoyable than the main series games because unlike the main series games, spinoffs have that freedom to take a different direction and take different risks, for better or worse. i could honestly go on and on about other spinoffs that made my childhood so great (Hey You! Pikachu was a notable part of it), and while the main series games are great, don't get me wrong, game freak has dropped the ball many times by playing it safe when the main series games have much more potential to be more exciting and different than they are.