Story was the sole reason I bothered playing Pokemon Mystery Dungeon 2 after the first five minutes, since the gameplay was "meh" at the best of times and "why the hell am I even playing this" at the worst. I can put up with a game that has poor gameplay if the story is strong enough.
It really depends on the game. The Ace Attorney series, for instance, is rather light on gameplay. The story basically is the game. Everything you do in-game is based on the story, and your knowledge and memory of the story actually apply to the gameplay. Tetris, on the other hand, has no story and yet is insanely fun. The balance necessary for a given game really just depends on the game and the skill of the staff working on the game. I don't see the team who creates Mario games, for instance, coming up with a deep, complex plot beyond "save the princess" or "kill the dark star". Enjoyable plots, yes, but nobody plays Mario games for the plot. If you do, you really need to try some new games or read a book or something (this excludes the Mario RPGs which actually have a decent plot). Meanwhile, I don't see the Ace Attorney people coming out with an insanely gameplay-heavy platformer anytime soon.
In other words, it depends on the game and how skillfully each aspect is pulled off. If the balance tips too far without good reason, it's not a good game. Ace Attorney skillfully balances very light gameplay with a deep plot. Meteos, on the other hand, puts in a slight, amusing plot interspersed with intense gameplay. I'd play either way, as long as the method used fits.