The Reds led 26-7 early in the second half of Saturday's match in Auckland, but Moana piled on the next three tries to take the lead as a boilover loomed.
Down by five points and with the clock ticking over to the 79th minute, the Reds made the most of a scrum by swinging the ball wide before cutting it back inside to fullback Campbell for the levelling try.
Ben Volavola made no mistake with the conversion from straight in front, with the Reds holding firm for victory.
"We're really chuffed about how we held our nerve and got the win," Reds coach Les Kiss said.
"But we've got to look at some things to make sure we keep improving.
"I believe in these boys deeply. I believe there's a real core strength of the group that comes up when it matters, and we've just got to make it more consistent throughout the game."
The Western Force's finals hopes went up in smoke later on Saturday night when they failed to snare a bonus point in a 19-15 win over the Drua, leaving them six points behind Queensland with one round remaining.
The seventh-placed NSW Waratahs (27 points) can still finish equal on points with the Reds (32 points) if they post a bonus-point win over the Force in Perth next Saturday and Queensland lose to Fijian Drua.
But given the Reds (7-6) are guaranteed to finish with more wins than the Waratahs (5-8), Queensland will finish above NSW if they end up tied on competition points, even if the Waratahs end up with a better for-and-against differential.
The Reds (7-6) can finish as high as fourth, but they will need the Crusaders and Brumbies to slip up next week.
Moana have now slumped to 12 straight losses, with their only win this season coming in round one when they defeated Fijian Drua.
On paper, Saturday's match shaped as an easy win for the Reds.
But given it was Moana's last home game if plans to disband them at the end of the season go ahead, Queensland were on red alert for a tough outing.
"Their spirit came out didn't it in the way they played. Direct, skilful. Their tackles were monstrous at times," Kiss said.
"It certainly had you worried for sure. But we found a way."
Moana performed an emotion-charged Tau Moana before the match, with the haka-style performance getting the juices flowing from the outset.
Reds players barely had time to settle before Moana scrumhalf Augustine Pulu crossed for the opening try inside 90 seconds.
The Reds hit back in the 11th minute when Campbell initiated a break and then combined for a series of passes with Tim Ryan to send the winger over the line.
Josh Canham's pure determination led to a second try in the 22nd minute, with the Reds lock barging over in a tackle.
Treyvon Pritchard's 45th-minute try gave the Reds a 26-7 advantage.
But when Semisi Tupou Ta'eiloa hit back four minutes later and then Moana were gifted a penalty try in the 55th minute, the margin was back to just five points, and with Reds flanker Joe Brial in the bin.
Moana hit the lead in the 58th minute when Israel Leota broke through three tackles on the wing to touch down for a dazzling try.
But the home side were left heartbroken when Campbell found a gap to produce the match-winning moment.
