This thread is full of very stark black and white views of gameplay and story elements. It's not often like that in many games. There's a lot of gray elements to take into account.
There is a place for a story and it should not be limited for books and films. Using an argument that the stories are often b-grade level afterthoughts is one of the excuses to back up a flawed argument. A story carries the rest of the disconnected parts of a game forward so that the player has a reason for the gameplay elements in it. If a developer fails in writing a good background and story for their game, it will reduce the quality of the overall game, but it won't necessarily make it bad.
Story however, is not necessary in every game that there is. Tetris, like said in the OP, doesn't need a story as the goal of the game is just to empty the blocks from the screen and get a high score while the game gets faster and faster. However, we could inject a story into a game of Tetris to change the goal of the game to include an end element. A WHY to the formula. As I said, it would not be necessary, but it could make the player be interested to see what happens with the story and for the game to have an end, instead of not really having one.
Thus as an example of a puzzle game with a great story I present Catherine. The game itself is a climbing puzzle game where the player character has to climb obstacles with traps, time limits and fighting enemies. The game mechanic is for players interested in mind-twisting block puzzles, but the developers decided to inject a story about relationships, cheating your partner and drinking alcohol in a bar between the puzzle sections. This itself created a backdrop for the gameplay and an end goal for the total game, so that players have an incentive to not quit when there is a difficult section in the game, and a will to see what happens next with the characters in the game. Thus, the game creates two sets of players; ones interested in gameplay and others for story. Both are treated with interesting elements, but both have different approaches to the game, and they get satisfaction from these two different offerings. Some might like both.
In a game like Super Mario, the story is just about saving the kingdom by saving the princess. That is the only story there is, and it is used only as a general end goal for the game and some mid game elements. When the princess is rescued, the story and thus the game ends. If there would not even be this simple story, ending the game would be a tougher sale for the player and they would be left wondering why the game ends on nothing. Even for a game like Mario, there has to be one as the game concept is not abstact like Tetris. The gameplay is great and a reward in itself, but removing even the tiny amount of story that there is would results in a disjointed set of gameplay sections.
Story in shooters like Fuse, Gears of War or Army of Two is generally b-grade stuff, that is just there to tie the shooting together and it shows. When the action is made the main point of the game, like platforming is for Mario, the story in shooters suffers from this oversight.
Now why is it that a platformer can have a barebones story but a shooter often needs one? It might be due to the realistic elements of the games. The closer you take a player to the real world, the more you have to use real world elements in the game. As the characters start to resemble actual human beings, the more you expect from them and the game itself to resemble our world.
I chose these three games because they have three different approaches to story telling, but they all have one element that is present in them all, co-operative gameplay.
Now, Fuse has a background story, something about a material that can do something, something, important. But the story is often told while you play and after an hour or two, the players have no idea why they are fighting and who.
The Army of two story is utter shit about a fallen mercenary and a mexican drug lord. The story is often told in cutscenes, so the player has a general idea why they are fighting, but actually don't care because the story is so bad. But as bad as the story is, at least is so bad that the player remembers the horseshit the writers are vomiting on screen. It's designed to be as simple as possible to tie the scenes together.
Gears of war at least tries and has a more larger story with interesting elements that are tied together with cutscenes. The player at least cares for the human race not being eradicated. But the characters written for the game are just buffed tanks wearing XXL sized armour and they solve everything by shooting things to pieces. Having a weaker story makes it all the more important for the gameplay to be better. Of course having a great story doesn't lessen the need for the gameplay to be up to par. There has to be a balance of these two elements.
The redeeming factors for these three games is the gameplay and the ability to play with friends. This creates a lesser need for the story to be present, as the smaller "stories" are created by the player interactions with the game world. How they tackle the gameplay and enemies attacking is now more valuable than the general story.
How many games often go around having a bad story in general, is to give players smaller goals that they have to tackle during gameplay e.g. Defend a point, reach a lever, escape a section. The gameplay to reach these small goals is key and the story around is oft forgotten as the mini-happenings created from interactions with the world take the center stage. "Did you see that! How the guy was blown to bits! Awesome" is what makes the players happy and the interactions between the players experiencing the happenings together is what matters.
Just to delve into The Last of Us for a bit. The game could survive without Ellie character, but her being there makes the game more involving for the player. It makes you care for the characters more and it creates an overall arc for the story you are following to the bitter end. Your general reason for fighting is for her, not for you. The story creates the tension in your mind when it presents enemies and situations that otherwise wouldn't be there without the story. You crawl through a snowstorm to rescue her and in your mind you think "I wanna kill them all! motherfuckers!". That's the story affecting the game. Then it's nicely tied with the game having a satisfying gameplay too. Without it the story, no matter how interesting, would be less fun. Fighting with controls often trumps any interesting story elements.
What I want to simply say is that there is no gameplay over story or vice versa. They both have to co-exist. There is just a need for most games to have a story with a beginning, middle an end. Otherwise the elements you want to present for the player are separate. The gameplay is the addictive interaction element and the story is the glue that ties them all together.
It's about balance of several elements. Not one or the other.
Sorry for the long post. I got carried away. Hope it makes sense for you.