Here is a simple counterexample: let $R$ be a Dedekind domain with $\operatorname{Cl}(R) \neq 0$, and let $I \subseteq R$ be a non-principal ideal. Note that $I$ is generated by $2$ elements and $I_{\mathfrak p} \subseteq R_{\mathfrak p}$ is principal for all nonzero primes $\mathfrak p \subseteq R$.
Thus we may choose a surjection $R^2 \twoheadrightarrow I$, which admits a splitting since $I$ is projective. This gives an idempotent $A \colon R^2 \to R^2$ whose image is isomorphic to $I$. Then $A_{\mathfrak p}$ is diagonalisable in $R_{\mathfrak p}$ for all $\mathfrak p \in \operatorname{Spec} R$: the map $\ker(A) \oplus \ker(\mathbf{I}-A) \to R^2$ is an isomorphism, and both $\ker(A)$ and $\ker(\mathbf{I}-A)$ are free of rank $1$.
But $A$ is not diagonalisable since $I$ is not principal: if $A = SDS^{-1}$ for some diagonal matrix $D$ and some invertible matrix $S$, then $S$ induces an isomorphism $S \colon \operatorname{im}(D) \stackrel\sim\to \operatorname{im}(A)$ (and likewise for the kernels). But $\operatorname{im}(D) \cong R$ whereas $\operatorname{im}(A) \cong I \ncong R$.
Example. Carrying this out for $R = \mathbf Z[\sqrt{-5}]$ and $I = (2,1+\sqrt{-5})$ produces the matrix $$A = \begin{pmatrix} -2 & -1-\sqrt{-5} \\ 1-\sqrt{-5} & 3 \end{pmatrix}.$$ The image contains the vectors $\left(\begin{smallmatrix} -2 \\ 1-\sqrt{-5} \end{smallmatrix}\right)$ and $\left(\begin{smallmatrix} -1-\sqrt{-5} \\ 3\end{smallmatrix}\right)$, which are linearly dependent but not common multiples of some other vector in $R^2$.