The first example of a sheaf that I have consciously come across is the sheaf of continuous (real) functions on some topological space. The fact it is a sheaf is equivalent to the pasting lemma, which says more generally that given two topological spaces $X,Y$, the presheaf $\bar h_Y:\mathcal O (X)^\text{op}\longrightarrow \mathsf{Set}$ on the locale of opens of $X$ defined by $U\mapsto \mathsf{Hom}_{\mathsf{Top}}(U,Y)$ is a sheaf. As a functor over the locale of opens of $X$, it is not representable unless $Y$ is an open subset of $X$.
This fact lifts to (locally) ringed spaces and says the presheaf $U\mapsto \mathsf{Hom}_\mathsf{LRS}(U,Y)$ is also a sheaf. Again, it is not, in general representable because our domain category is "wrong".
According to the functor of points approach to algebraic geometry, representability is really a very important thing. It seems we would like to work in a world where these assignment are sheaves. This, from what I understand, is a reasonable source of motivation for Grothendieck topologies.
For topological spaces it's clear that every object in $\mathsf{Top}$ comes with a locale of opens, and it's equally clear that $C^0$ is a sheaf over every topological space. This suggests replacing the locale of opens by $\mathsf{Top}$ itself, and asking for the sheaf axiom (in terms of an equalizer diagram) for all covers of every object. This would in particular solve the representability issues above.
This seems like a big difference from the usual concrete picture, in which sheaf means 'sheaf over a fixed space'. I guess we could get an equivalent formulation of a sheaf over a single object of a site by merely asking for the sheaf axiom to hold on each of its covers. And yet I can't think of an example where we fix our category of interest $\mathsf C$ and find that an interesting presheaf is a sheaf only over some objects.
Why is this? Is it simply saying that sheaves over topological spaces are functorial in the base space by $f\mapsto f_\ast$, or am I missing the picture completely?
Update: Well, an example would be bounded continuous (real) functions. If $X$ is finite, we do get a sheaf. This only strengthens the question of why the theory of sheaves over sites is so global.
If your goal is just to have sheaves be representable, there is an ideal site for this purpose: $\def\Sh{\mathrm{Sh}} \Sh(X)$ (the covers are the epimorphic families). This site has the nice property that $\Sh(X) \equiv \Sh(\Sh(X))$.
(I'm glossing over size issues)
There's another way to look at this example. Let $\def\LH{\mathbf{LH}} \LH \subseteq \def\Top{\mathbf{Top}} \Top$ be the subcategory whose arrows are the local homeomorphisms. Then, the fact you can identify sheaves with étale spaces means that there is an equivalence of categories
$$ \Sh(\LH / X) \equiv \Sh(X) \qquad \quad (\equiv \LH/X)$$
This particular example highlights the main trick I think you're missing; these kinds of "big sites" correspond to things over the terminal object. If you want to see how things look over some other object, you need to take the slice category.
While we can map topological spaces into $\Sh(X)$ by $Y \mapsto \hom(-, Y)$, this forgets a lot of their nature as topological spaces. For example, the étale space corresponding to $C^0$ doesn't resemble $\def\RR{\mathbb{R}} \RR$ at all.
That is why we might consider a big site such as $\Top / X$; the category $\Sh(\Top/X)$ not only knows about sheaves on $X$, but it also knows about bundles over $X$, and even about correctly gluing bundles. For example, $C^0$ is the trivial line bundle $X \times \mathbb{R} \to X$, which really does look like the real line!