The key is the amount and length of the area that contacts the lands. As stated, large artillery rounds have short driving bands that provide the contact surface. The bands are typically narrower than the lands in the bore are wide. The grooves that are engraved by the lands, into the driving bands, are shorter than they are wide as well.

Typical small arms projectiles have much longer (relative to bore) contact surfaces.  What happens is that the bullet enters the throat/leade and the lands engrave the jacket. In a constant twist bore, the bullet spins happily, and consistently, down the bore with the contact remaining in the same grooves originally engraved. In a progressive or gain twist barrel, the initial twist is slower. The bullet is engraved as in a standard twist, but as it goes down the bore it is encounters a faster and faster twist so the lands continue to engrave grooves that are wider and wider until the bullet exits at the muzzle. This ends up deforming the jacket in an inconsistent manner as jackets are never perfect, and the gain twist puts engraving forces on the jacket in two directions at once. This can also cause barrels to copper foul very quickly, further degrading accuracy.

In pistols it often works for the same reason it works in big bores. The width of the lands versus the length of the jacket contact area is such that the problem of scrubbing the jacket is not seen. But, while there are a lot of very good gain twist pistol barrels made, there are a lot that don't group well at all, especially compared to conventionally rifled ones.