This sort of issue is significant when we're trying to get a grip on the analysis of automorphic forms, but/and, already non-automorphic situations provide much insight. I can't help but comment that adding "automorphic" in any discussion creates enough cognitive dissonance (at some level) that otherwise-classical examples and counterexamples often get lost.
Yes, one should think in terms of automorphic spectral decompositions, and note that eigen-cuspforms are of rapid decay, and that a not-vanishing-at-infinity but vanishing-constant-term function must necessarily have unpleasantly-behaving spectral decomposition coefficients.
That a (very nicely convergent, at least uniformly pointwise on compacts) sum of rapidly-decreasing functions need not be decreasing at all, etc., (yes, I know this is a somewhat different issue) is illustrated by $\sum x^n e^{-nx}/n! = 1$. This suggests something about the spectral analysis.
Similarly, more directly, (but, yes, I know, somewhat differently), an $L^2$ function on the real line can have ever-narrower spikes parading out to infinity, so not go to $0$ at infinity. (Of course, if it had any limit at all, it would have to be 0, on the real line... though this is not true for automorphic forms, because of finite volume at infinity.)
In the automorphic case, take $f(x+iy)$ to be $0$ for $y<2$, and for $y\ge 2$ let $f$ be $e^{2\pi ix}h(y)$ with $h(y)=y^{1/3}$. Can smooth this out, too. And can make the lim sup be $+\infty$ by spikes.
In particular, here, note that dropping the eigenfunction condition gives us license to look at the much simpler "tapering cone" that is the image of a high-up Siegel set under the quotient map.
In summary, it is (with hindsight, sure) boringly easy to make a "cuspidal" automorphic form, in $L^2$, that does not go to $0$ at infinity. But, when we see what the possibilities are, they do not disprove other important principles.
Edit: should have said that the lim sup can grow arbitrarily fast by (narrow) spikes.
Edit: That is, a natural argument based on a spectral expansion is incomplete without further information on the rapidly decreasing functions (here, cuspforms), since a sum of rapidly decreasing functions need not be decreasing at all. Of course, some sums of rapidly-decreasing are rapidly decreasing... but if the sups occur further and further out, this need not be so, as in $\sum {2^n\over n!}\,x^ne^{-x} = e^x$.
Although, for example, being in $L^2(\mathbb R)$ does not imply a function goes to $0$
at infinity pointwise, if it is in a Sobolev space (has an $L^2$ derivative) then by the fundamental theorem of calculus it has a bound something like $\sqrt{x}$. To actually have decay requires more.
Sums of cuspforms that are in $L^2$ can easily map to non-$L^2$ "cuspidal" things under $\Delta$, or even under "first derivatives" coming from the Lie algebra acting on the right.
This does not necessarily contravene "going to $0$", but it shows that a simple argument fails, as in the sum of $x^n e^{-x}$'s.
In fact, I think Iwaniec' "spectral theory of afms" book has some remarks in it about the sups of cuspforms occurring further and further out, which creates the danger alluded to above. There was a paper of Iwaniec-Sarnak in which an infelicity about something of this sort occurred, remarked upon in Sarnak's letter to Morawetz. The extent to which this enables wild-ish growth at infinity of (smooth?) $L^2$ "cuspidal" things would require computation, at least, and sharp answers may depend on serious unproven things, now that I think about it...
Edit again: yes, some sort of smoothness hypothesis presumably makes a spectral argument work, even with "relatively easy" estimates on sups of an orthonormal basis of cuspforms. An easy sort of smoothness assumption is not merely smoothness, but that $\Delta f$ is in $L^2$, and/or $\Delta^\ell f$ is in $L^2$ for some sufficient $\ell$. A Sobolev-ish condition. Already on the real line the analogous issue is present: smooth functions in $L^2$ without constraints on integrability of derivatives need not decay.