The Nuggets led by as many as 25 points in the second period, but then had to stage a late comeback to close out their nine-game homestand with a victory. Denver will play its next seven games on the road.
Detroit, fueled by a stunning 9-of-9 from 3-point range and 45 points overall from Ben Gordon, took a 115-111 lead with 2:02 remaining but did not score again.
A free throw by Al Harrington cut the Nuggets' deficit to three with a minute left and Denver trailed by three as the game entered its final 10 seconds.
Afflalo then scored on a layup as he was being fouled by Gordon. Afflalo missed the free throw attempt that would have tied the game but McGee was there to grab the offensive rebound and his dunk with 5 seconds left put the Nuggets in front.
Gordon then missed a jumper for the Pistons and Afflalo seized the game-clinching rebound.
Denver outscored Detroit 40-18 in the opening period but the Pistons came back to tie it going into the fourth quarter.
Ty Lawson led Denver with 25 points and Afflalo had 22. The Nuggets went 5-4 on their long homestand.
Gordon was 13-of-22 overall in his big game and made 10-of-11 from the foul line.
2014: The Year in Fashion [PHOTOS]
LeBron James reveals his new Nike shoes