The question reduces to a question about sheaves of sets, so I will just work with those (instead of modules or whatever).
The point is that we have the following commutative diagram of right adjoint functors,
$$\require{AMScd}
\begin{CD}
\mathbf{Sh}(X) @>>> \mathbf{Sh}(Y) \\
@VVV @VVV \\
\mathbf{Psh}(X) @>>> \mathbf{Psh}(Y)
\end{CD}$$
and you are asking what happens when you take the left adjoints of only the vertical arrows. Well, in that case, we get a canonical natural transformation as below,
$$\begin{CD}
\mathbf{Sh}(X) @>>> \mathbf{Sh}(Y) \\
@AAA \Uparrow @AAA \\
\mathbf{Psh}(X) @>>> \mathbf{Psh}(Y)
\end{CD}$$
whose component at a presheaf $\mathscr{F}$ on $X$ is the morphism $a f_* \mathscr{F} \to f_* a \mathscr{F}$ induced by the universal property of $a f_* \mathscr{F}$ applied to the direct image of the universal morphism $\mathscr{F} \to a \mathscr{F}$. In particular, $a f_* \mathscr{F} \to f_* a \mathscr{F}$ is automatically an isomorphism if $\mathscr{F}$ is a sheaf on $X$, as you would expect.
Now, consider a sieve $R$ in $X$, i.e. a collection of open subspaces of $X$ that is downward-closed, i.e. if $U' \subseteq U$ and $U \in R$ then $U' \in \mathfrak{U}$ as well. Let $\hat{U} = \bigcup_{U \in R} U$. We can think of $R$ as a presheaf on $X$: $R (U) = 1$ if $U \in R$ and $R (U) = \emptyset$ otherwise. The sheafification of $R$ is easy to compute: it is the sheaf $a R$ such that $(a R) (U) = 1$ if $U \subseteq \hat{U}$ and $(a R) (U) = \emptyset$ otherwise. The direct image $f_* R$ is also a sieve, namely the collection of all open subspaces $V \subseteq Y$ such that $f^{-1} V \in R$. Let $\hat{V} = \bigcup_{V \in f_* R} V$.
Notice that $f^{-1} \hat{V} = \bigcup_{V \in f_* R} f^{-1} V \subseteq \bigcup_{U \in R} U = \hat{U}$, so $a f_* R \to f_* a R$ is an isomorphism if and only if the following condition is satisfied:
- For every open subspace $V \subseteq Y$, $f^{-1} V \subseteq \hat{U}$ if and only if $V \subseteq \hat{V}$.
The above condition being satisfied for all sieves $R$ in $X$ is something like the topology of $X$ being induced by the topology of $Y$, but it is not really the same. Certainly, if $f : X \to Y$ is the inclusion of a subspace (not necessarily open or closed), then $a f_* R \to f_* a R$ is an isomorphism for all sieves $R$. This also happens if $X$ is the spectrum of a discrete valuation ring and $Y$ is the point – even though the topology of $X$ is not induced by the topology of $Y$ in this case. And, for example, if $f : X \to Y$ is the codiagonal/fold map $Y \amalg Y \to Y$, then one can easily find a sieve $R$ such that $a f_* R \to f_* a R$ is not an isomorphism.
We still haven't really addressed the general case of a presheaf instead of a sieve. Things are more complicated here, but what is still true is that if $f : X \to Y$ is the inclusion of an open subspace then $a f_* \mathscr{F} \to f_* a \mathscr{F}$ is always an isomorphism – this is more or less obvious. On the other hand, bad things can happen if $f : X \to Y$ is the inclusion of a non-open subspace: for example, if $f : X \to Y$ is the inclusion of a point and $\mathscr{F}$ is a constant presheaf on $X$, then $a f_* \mathscr{F}$ is the sheafification of a constant presheaf on $Y$ while $f_* a \mathscr{F}$ is a skyscraper sheaf.
The "high-level" argument would be related to such topics as the Yoneda lemma, the Yoneda embedding, representable functors, etc. in category theory.
At this level, the argument would go roughly along the lines of:
\begin{align*}
\operatorname{Hom}_{\mathcal{O}_X}(f^* \mathcal{G}, \mathcal{F}) & \simeq
\operatorname{Hom}_{\mathcal{O}_Y}(\mathcal{G}, f_* \mathcal{F}) \\
& \simeq \operatorname{Hom}_{\mathcal{O}_Y, psh}(\mathcal{G}, f_* \mathcal{F}) \\
& \simeq \operatorname{Hom}_{f^+ \mathcal{O}_Y, psh}(f^+ \mathcal{G}, \mathcal{F}) \\
& \simeq \operatorname{Hom}_{\mathcal{O}_X, psh}(f^+ \mathcal{G} \hat\otimes_{f^+ \mathcal{O}_Y} \mathcal{O}_X, \mathcal{F}) \\
& \simeq \operatorname{Hom}_{\mathcal{O}_x}([f^+ \mathcal{G} \hat\otimes_{f^+ \mathcal{O}_Y} \mathcal{O}_X]^+, \mathcal{F}).
\end{align*}
(Here for example, $\operatorname{Hom}_{\mathcal{O}_Y, psh}(-, -)$ represents the presheaf homomorphisms which are $\mathcal{O}_Y$-linear; and for a presheaf of $\mathcal{O}_X$-modules $\mathcal{F}$ then $\mathcal{F}^+$ is the sheafification as a sheaf of $\mathcal{O}_X$-modules.)
In this sequence, once you have established each isomorphism, and you have also verified that each is functorial in $\mathcal{F}$, then by Yoneda's lemma, it follows that the composite isomorphism of functors $\mathcal{O}_X{-}\mathrm{Mod} \to \mathrm{Set}$ is induced by a unique isomorphism of $\mathcal{O}_X$-modules $f^* \mathcal{G} \simeq [f^+ \mathcal{G} \hat\otimes_{f^+ \mathcal{O}_Y} \mathcal{O}_X]^+$.
(Another possible approach, which essentially boils down to the same thing in the end, is to show that both sides are left adjoints to the same functor, just expressed as different compositions on the "right adjoint side".)
Best Answer
I will give the unit and counit for the first adjunction $f^{-1} \dashv f_*$. Given a continuous map $f: X \to Y$ of spaces, let $f^{\dagger}$ denote the inverse image of presheaves, and $f^{-1}$ the inverse image for sheaves.
The unit map $\eta: 1 \Rightarrow f_*f^\dagger$ for presheaves has components given by the canonical "insertion" maps into the colimit: $$F(W) \to \mathrm{colim}_{U \supseteq f(f^{-1}W)} F(U)=f^\dagger F(f^{-1} W) = f_*f^\dagger F(W).$$ Note that these exist since $f(f^{-1} W) \subseteq W$. Since sheafification is left adjoint to the inclusion of sheaves into presheaves, we can get the unit for the sheaf case as follows. To be more precise, let $\iota$ denote the inclusion of sheaves into presheaves, and $a$ its left adjoint (sheafification). Then take the unit map of the sheafification adjunction $f^{\dagger} \iota F \to \iota a f^\dagger \iota F$, apply the direct image functor $f_*$, and then precompose with $\eta$ to get the correct unit: $$\iota F \xrightarrow{\eta_{\iota F}} f_* f^\dagger \iota F \rightarrow f_* ia f^\dagger \iota F =f_* \iota f^{-1} F = \iota f_* f^{-1} F.$$
Now, notice that if $f(V) \subseteq U$ then $V \subseteq f^{-1}f(V) \subseteq f^{-1} U $, and hence we get a restriction map $G(f^{-1}U) \to G(V)$. By the universal property of the colimit, this gives us a unique map $$f^{\dagger}f_* G(V)=\mathrm{colim}_{U \supseteq f(V)} G(f^{-1}U) \to G(V).$$ These canonical maps give us the components of the counit $\varepsilon: f^\dagger f_* \Rightarrow 1$. If you want the counit for the adjunction in the case of sheaves, just transpose $\varepsilon$ using the sheafification adjunction : $$\mathrm{Hom}_{\mathrm{Sh}(X)}(f^{-1}f_* F, G) \cong \mathrm{Hom}_{\mathrm{PSh}(X)}(f^{\dagger}f_* F, G).$$