People like to watch fiction to live vicariously through the characters. Most people in real life have no illusions about ever being able to do the sort of things within the movies. Whether it be the story of an athlete on his way to the top, life as the president, or Stuff Blowing Up, these are stories that the average Joe won't find himself partaking in. Then there are things such as superpowers, magic, and high technology. However, even a story with fantastic (or just merely highly improbable) elements has to make it believable in order to allow Willing Suspension of Disbelief.
But God Mode Sue takes that Willing Suspension of Disbelief and tosses it out the window. He or she note doesn't so much work within the plot as the plot works for them.
God Mode Sue exists purely to show up how pathetically weak the rest of the world is, and how badly they need his or her help. If there's anybody else that is even capable of standing up for themselves, they may lose their abilities for some reason when the character comes into the equation, or become completely incompetent, or both. They'll probably get captured or find something that they just can't handle. Then the God Mode Sue shows up, saves the day on his or her own at least twice as easily as they usually do when working as a team, and doesn't get his or her ass kicked at all. Then he or she stands around and wallows in their praise a bit.
God Mode Sue is so powerful because he or she often gets New Powers as the Plot Demands, and without any explanation or any of the normal limitations that a good writer will insert. In most balanced fiction, we generally know the hero's going to win, Downer Ending aside (Status Quo Is God, after all), but their abilities may fail them (or something like that) and so we watch to find out HOW they do it. God Mode Sue's powers never fail, nor is there any point where you think, "I KNOW the hero's gotta win, but how are they going to get out of this mess?" The plot and the fight is so blatantly biased towards the God Mode Sue that it becomes rather boring (and sometimes insulting).
It's worthy to note that there is a correlation between badass and this trope. A proper badass will frequently break the Willing Suspension of Disbelief, but they generally sell it through either Refuge in Audacity or just generally being convincing as something that could naturally come up with the character. However, they are still treated as normal characters by the plot and have to face potential repercussions to their actions like anybody else. God Mode Sue... doesn't.
God Mode Sue can overlap with pretty much anything, but generally doesn't overlap with Purity Sue (whose overwhelming specialness is its own superpower) or Sympathetic Sue. It can overlap with a Possession Sue if a writer wants to show what (they feel) a canon character can really do. If it overlaps with Villain Sue, then our heroes had better hope that he or she has a HeelFace Turn coming up, or else Only the Author Can Save Them Now.
The trope name comes from the term "God Mode" (or, more specifically, its use in the context of "godmoding"), which often gets used to denote when a player refuses to allow any random chance to negatively impact their character. In this case, the author is pretty much doing just that.
The Comically Invincible Hero is frequently as powerful as a God Mode Sue, but is played for comedy due to Refuge in Audacity. Compare and contrast with the Showy Invincible Hero, who dances on the line but avoids crossing it due to Rule of Cool. The Invincible Hero is the first stage of this trope. If an Infinity +1 Sword is involved, it may be a sign that a transition from Invincible Hero into God Mode Sue is taking place, when it becomes impossible for a character to get separated from the item granting him his powers or have their connection cut in any way. If that is obvious from the start, it's safe to assume character starts as a full-blown God Mode Sue. The final stage of this trope, after the Rock Bottom has fallen out from under the story, is an all-devouring Black Hole Sue.
The Memetic Badass is essentially what happens when a character is portrayed as a God Mode Sue by the fandom, whether it be an already badass character having their capabilities exaggerated to absurd levels, or one who might as well be a Joke Character being elevated to outright godhood.