Greg Muller, in a post called Rational Homotopy Theory on the blog "The Everything Seminar" wrote
"I tend to think of homotopy theory a little bit like ‘The One That Got Away’ from mathematics as a whole. Its full of wistful fantasies about how awesome it would have been if things could only have worked out. Imagine if homotopy groups of spaces and homotopy classes of maps were as easy to compute as homology groups…"
What are the wonderful consequences that he is referring to?
Homology groups and homotopy groups are two sides of the same story. Homotopy groups tell us all the ways we can have a map Sn → X, and in particular describe all ways we can attach a new cell to our space. On the other side, the homology groups of a space change in a very understandable way each time we attach a new cell, and so they tell us all the ways that we could build a homotopy-equivalent CW-complex. In cases where we can understand both of them, we can get things like complete theorems about classification of spaces.
Here's an example where we can compute: classification of the homotopy types of compact, orientable, simply-connected 4-manifolds. (I originally saw this is Neil Strickland's bestiary of topological spaces.)
Poincare duality tells us that the homology groups are finitely generated free in degree 2, ℤ in degrees 0 and 4, and zero elsewhere. We can cut out a closed ball, and get an expression of the manifold as obtained from a manifold-with-boundary N by attaching a 4-cell. The Hurewicz theorem tells us that we can construct a map from a wedge of copies of S2 to N which induces an isomorphism on H*, and by the (homology) Whitehead theorem this is a homotopy equivalence. So our original manifold is obtained, up to homotopy equivalence, by attaching a 4-cell to $\bigvee S^2$.
How many ways are there to do this? It is governed by $\pi_3 (\bigvee S^2)$, which we can compute because it's low down enough. This homotopy group is naturally identified with the set of symmetric bilinear pairings $H^2(\bigvee S^2) \to \mathbb{Z}$, and this identification is given by seeing how the cup product acts after you attach a cell. So these 4-manifolds are classified up to homotopy equivalence by the nondegenerate symmetric bilinear pairing in their middle-dimensional cohomology.
Some of what we used here is general and well-understood machinery about homology, homotopy, and their relationships. Wouldn't it be nice if the standard tools were always so effective? But the real meat is that we have a complete understanding of homology and homotopy in the relevant ranges. It turns our questions about classification into questions about pure algebra. For questions that require specific knowledge about higher homotopy groups of spheres (or even lower homotopy groups of complicated spaces), it is much harder to get answers. There aren't a lot of spaces where we have complete understanding of both the homology groups and the homotopy. We have tools for reconstructing the former from the latter but their effectiveness wears down the farther out you try to go.
There are categories that are somewhat like the homotopy category of spaces where we can get an immediate and specific understanding of both sides of the coin.
One such example is the category of chain complexes over a ring R. There, our fundamental building block is R itself. The homology of any chain complex tells us both how R can be mapped in modulo chain homotopy, and how complicated any construction of the underlying chain complex must be. A more complicated example would be the category of differential graded modules over a DGA, where the divide between how things can be constructed and how things can have new cells attached is, at the very least, governed by the complexity of H* A as a ring, and then by the secondary algebraic operations if A is far from being anything like formal.
Another such example is the rational homotopy theory of simply-connected spaces you mentioned. There, homology and homotopy are roughly something like the difference between a ring's underlying abelian group structure and how you build it using generators and relations.
So you might think of the complexity of homotopy groups as telling us how much more complicated spaces are than chain complexes.
Those who reply that being able to compute homotopy groups is akin to being able to compute values of L-functions are perhaps unwittingly referring to a great parallel: stable homotopy groups of spheres are the "algebraic K-theory of finite sets" while, while the algebraic K-theory of number fields is conjecturally related to special values of L-functions (the Quillen-Lichtenbaum conjecture).
What Muller is almost certainly referring to is that homotopy is a powerful functor. For example, Freyd's generating hypothesis conjecture implies that the stable homotopy classes of maps between two finite CW complexes can be faithfully viewed through the module maps, over stable homotopy of spheres, of their stable homotopy.
Thus, if we were really good at understanding homotopy groups, then presumably we'd be that much closer to understanding various questions which can be reduced to or require significant input from homotopy theory. Classically, such applications included classifying manifolds up to diffeomorphism - Kervaire and Milnor reduced this question for spheres to one in homotopy. Because rational homotopy groups - really, rational homtopy theory - are more approachable (for example, one gets an upper bound on the homotopy groups of a simply-connected X through the Harrison homology of its cohomology ring), they have been widely-applied in geometry. See for example the book "Algebraic Models in Geometry" by Felix-Oprea-Tanre, which discusses topics ranging from group actions to symplectic manifolds to geodesics to toric topology to configuration spaces. One could presumably get much sharper results in all of these areas with homotopy over the integers.
But homotopy theorests are generally more excited these days by potential application to algebra itself. The sphere spectrum, whose coefficients are the stable homotopy groups of spheres, plays the same role in the world of $E_\infty$-algebra ("derived" commutative algebra) as the integers do in algebra - this is one philosophical starting point of Lurie's opus.
