Why are spectral sequences so ubiquitous? I sort of understand the definition of a spectral sequence and am aware that it is an indispensable tool in modern algebraic geometry and topology.  But why is this the case, and what can one do with it?  In other words, if one were to try to do everything without spectral sequences and only using more elementary arguments, why would it make things more difficult?
 A: Whenever you have a sequence of maps of (perhaps graded) abelian groups
$$
\cdots \to A^0 \to A^1 \to A^2 \to \cdots
$$
and each pair $A^{p-1} \to A^p$ is involved in a long exact sequence with third term $C^p$, there is a spectral sequence whose terms are the groups $C^p$.  If the sequence eventually stabilizes on both ends (or many variants of weaker hypotheses), the spectral sequence detects the difference between the limit and the colimit.  For instance, if you have a space filtered by subspaces, or a chain complex filtered by subcomplexes, or some arbitrary sequence of composable maps in a triangulated category, this kind of structure arises.
The core tool introduced by homological algebra is the long exact sequence, and the spectral sequence has proved to be an extremely useful organizational tool whenever you have two or more long exact sequences that interlock; the alternative is often to simply work with the long exact sequences one by one.  It's led to a lot of the methodology in algebraic topology where you can take some difficult computation that looks approximately like a structure you can handle, and handle the "easy" portion first with the difficult issues encoded by the differentials and extensions in the spectral sequence.
A: *

*Let's say you have a resolution $0\to A\to J^0\to J^1\to\dots$ (of a module, a sheaf, etc.) If $J^n$ are acyclic (meaning, have trivial higher cohomology, resp. derived functors $R^nF$), you can use this resolution to compute the cohomologies of $A$ (resp. $R^nF(A)$). If $J^n$ are not acyclic, you get a spectral sequence instead, and that's the best you can do. 

*Let us say you have two functors $F:\mathcal A\to\mathcal B$ and $G:\mathcal B\to \mathcal C$. Let us say you know the derived functors for $F$ and $G$ and would like to compute them for the composition $GF$. Answer: Grothendieck's spectral sequence.
1 and 2 account for the vast majority of applications of spectral sequences, and provide plenty of motivation -- I am sure you will agree.
The reason for the spectral sequences in both cases is the same. Intuitively, $A$ in case 1 (resp. $F(A)$ in case 2) is made of parts which are not themselves elementary. Instead, they are made (via an appropriate filtration) from some other elementary, "acyclic" objects. 
So there is a 2-step process here. You can do the first step and the second step separately but they are not exactly independent of each other. Instead, they are entangled somehow. The spectral sequence gives you a way to deal with this situation.  
A: Qiaochu links to a really nice article by Timothy Chow that says a lot about the mechanics of how to go from a filtered complex to its spectral sequence.  Two questions that remain are, (1) why do filtered complexes show up so much, and (2) is there anything that you could do with a filtered complex other than compute its spectral sequence?
VA gives a very general motivation for why filtered complexes show up so much.  Probably it is just more general than what I was going to say.  To discuss things more concretely, (I am told that VA's interesting answer is not a generalization of this paragraph.)  For question (1), here are two general ways that filtered complexes show up: (a) you might get a filtered complex if you are interested in a chain complex that comes from a filtered object.  For instance it could be a stratified topological space.  A CW complex is stratified, but it has a special structure so that its chain complex doesn't need a spectral sequence.  (It is an acyclic resolution as VA says.)  If you have a stratification that you wish was CW but isn't, then you just have a general filtered complex.  (b) There are some cases where just a single map or similar gives you a filtered complex.  These may all be special cases of what Grothendieck described.  For example, suppose that you are interested in the de Rham cohomology of a manifold.  (This is then a certain resolution of the sheaf of smooth constant functions on the manifold.)  Suppose further that the manifold is fibered over another manifold, or maybe is just foliated.  Then differential forms are filtered by how parallel they are to the foliation.
Concerning question (2), there is an interesting result for filtered complexes over a field coming from representation theory.  (I learned this some years ago in a discussion with Michael Khovanov.)  The theorem in this case is that there is no more information in a filtered complex than in its spectral sequence.  For simplicity, let's look at filtrations of length $n$.  Then a filtered vector space is a representation of the $A_n$ quiver with all arrows in the same direction.  (Each term of the filtration is assigned to a vertex of the quiver, and the arrows correspond to the inclusions.)  The quiver also has other representations, but filtered vector spaces are the projective modules.  A filtered complex is thus a projective complex over the $A_n$ quiver algebra.  It is just like homological algebra over any other ring; you usually look at projective complexes.  Since the $A_n$ quiver has projective dimension 1, it is easy to identify the indecomposable chain complexes of filtered vector spaces.  There are two types, with two terms and with one terms:
$$0 \to k^{(i)} \to k^{(j)} \to 0 \qquad\qquad 0 \to k^{(i)} \to 0.$$
In this notation, $k$ is the ground field and $k^{(i)}$ is $k$ in degree $i$.  The first type of complex is valid if $i \ge j$.  If you compute the spectral sequence of an indecomposable complex, you will see that detects the first type of term at the $(i-j)$th page, and kills it on the next page.  The second type of complex is of course the surviving homology.  You can also go backwards and reconstruct the filtered complex from its spectral sequence.
Of course it is simplistic to only discuss filtered complexes and spectral sequences over a field.  Nonetheless, roughly speaking spectral sequences are no more than a framework for analyzing filtered complexes.

VA asked for more details about the indecomposable modules, which is a fair request because I was quite cryptic about the relation.  In particular, I use non-standard indexing in my own thinking about this. Unfortunately, I'm not sure that I can convert to standard indexing without making a mistake, so I won't convert.  But I did change one thing above:  I fixed the filtration degrees so that they are correct.
Suppose that $C = (C_n)$ is a complex of filtered vector spaces.  Say that the filtration is increasing and indexed by $\mathbb{Z}_\ge 0$.  The complex has two degrees:  The chain degree $n$ and the filtration degree $k$.  Suppose that $\partial$ is a differential with chain degree $-1$ and filtration degree $0$.  (This is where the numbering begins to be non-standard, although it makes sense from the point of view of quiver representations.)  Then the theorem is that over a field, the two kinds of indecomposable complexes are those listed above, where the term $k^{(i)}$ has chain degree $n$, and the term $k^{(j)}$ (if present) has chain degree $n-1$.  To be precise about what $k^{(i)}$ means, it is a filtration of the field $k$ in which the degree $j$ subspace is $0$ when $j < i$ and $k$ when $j \ge i$.
The page $E^0$ is the associated graded complex.  The page $E^r$ has a differential of degree $(-1,-r)$.  When $r = i-j$, the differential $\partial^r$ of the first type of indecomposable complex connects the "tip" of $k^{(i)}$ to the "tip" of $k^{(j)}$ and kills them both on the next page.  The other kind of indecomposable complex has a vanishing differential, so the induced differential on every page also vanishes.
A: Let me first try to answer a simpler question:

Why are long exact sequences so ubiquitous?

Almost anything that is written as a capital letter, followed by a subscript i or superscript -i, i an integer, and finally some stuff in parentheses, can be interpreted as πi of some spectrum (or sometimes space, as in nonabelian group cohomology, or maybe a sheaf of spectra or spaces...).  And almost any long exact sequence which involves three similar terms in a cycle, with i decreasing by 1 every three terms, comes from the long exact sequence of homotopy groups of a fiber sequence of spectra or spaces.  For instance, the long exact sequence for the cohomology of a group G with coefficients in a short exact sequence of G-modules A → B → C corresponds to (HA)hG → (HB)hG → (HC)hG, since H-i(G, M) = πi((HG)hM) (which is nonzero only for nonpositive i), which is a fiber sequence because HA → HB → HC is one (since A → B → C is a SES) and (–)hG is a homotopy limit, so it preserves fiber sequences.  Or, I could draw a square with these three terms in it and 0 in the lower left, which is both a pullback and pushout square (since spectra form a stable (∞,1)-category).
Long exact sequences are actually a special case of spectral sequences—those whose E1 page has only two columns.  If you have never done this before, you should check for yourself that the d1 and the extension problems exactly tell you that there is a long exact sequence formed by the two columns and whatever the spectral sequence converges to.  This suggests that we could try to generalize the picture with a pushout/pullback square of spectra to find something to which a more general spectral sequence corresponds.  Two possibilites are: we could extend the top row of the square to a directed sequence of spectra, and take the homotopy cofiber of each map; or we could extend the right column of the square to an inverse sequence of spectra and take the fiber of each map.  These are the homotopy version of filtered and cofiltered objects, respectively; however, there is no condition on the maps (the notion of "inclusion" does not make much sense in the homotopy-theoretic world).  Associated to each is a spectral sequence, though there are convergence issues when the sequence of spectra is infinite.  It could be that most spectral sequences encountered in practice can be viewed as arising from an underlying sequence of spectra, though I have not attempted to convince myself of this fact.
Edit: Clark Barwick suggests that one may indeed view all "natural" spectral sequences as arising from filtered spectra.  He and I would like to know whether there are any convincing counterexamples, so please let me know if you have any!  Note however that 1 and 2 from VA's answer are not counterexamples.
A: I still don't feel as though I understand at an intuitive level what a spectral sequence "is" or what it "means."  But here is one nice explanation I heard about how one particular spectral sequence (the Serre spectral sequence) arises in topology.


*

*Homotopy groups behave nicely with respect to products: $\pi_n(X\times Y) \cong \pi_n(X) \times \pi_n(Y)$.

*Homotopy groups behave not quite so nicely with respect to fibrations (= "twisted products"): instead of an isomorphism you get a long exact sequence.


Homotopy groups are great, but they are often hard to compute.  (Co)homology is easier to compute, but it also doesn't behave as nicely on products and fibrations.


*

*Homology doesn't quite preserve products (i.e. take them to tensor products)--it almost does, but in general there's the Künneth theorem that relates them by an SES.

*Thus, when you apply homology to a fibration, you should expect to get something that has both the problems of a long exact sequence and and the problems of the Künneth theorem.  This "something" is a spectral sequence.

