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. derived functors of $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)$) F(A)$ in case 2) is made of parts which are not themselves elementary. Instead, they are made of (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.
