In Dunnington, G. W., Gray, J., & Dohse, F. E. (2004). Carl Friedrich Gauss: Titan of Science. MAA. p. 218 one finds the following:
Archimedes was the man of antiquity whom Gauss esteemed most highly.
He imagined him as a worthy old man of noble appearance. The only
thing for which he could not pardon him was the fact that in his "sand
calculating" he did not discover the arithmetic of position or decimal
system of numbers. Gauss said, "How could he overlook that, and at
what a pinnacle science would now be if Archimedes had made that
discovery."
(emphasis is mine)
Johnny Ball's version is on some level a sensible paraphrasing of the above translation by Dunnington of 1955. Given that Dunnington is a widely read English text on Gauss it might well be that Ball has it from this passage.
Sadly, Dunnington does not provide a direct citation, so I'm still looking where he might have translated this from. I'll edit this once I find it. EDIT: Mauricio has discovered that the source of Dunnington's passage is likely Wolfgang Sartorius von Waltershausen (1856) Gauss zum Gedächtnis p. 84. This source is known to be hagiographic and contains apocryphal material. So it may well be that this is not an actual Gauss quote. So unless a primary passage can be found it is probably best to consider it unconfirmed.
I take it as virtually certain that the "idiot" part is just flair injected by Ball.
Also note the mathematical context of von Walthershausen, that Dunningworth mentioned and compare it to Ball's exposition. In von Waltershausen it's the "decadische Zahlensystem" (system of decadic numbers, my translation), in Dunnington it is "arithmetic of position or decimal system of numbers" while in Ball's exposition it is calculus. In this sense this type of story telling fits many criteria of the kind of historically false or problematic folklore discussed by many other examples.