Nice question. For a prime $p$ let $\mathbb{Z}_{(p)} = \lbrace \frac{a}{b}\in \mathbb{Q}\mid p \nmid b\rbrace$ and $\mathbb{Z}[p^{-1}] = \lbrace \frac{a}{p^n}\in \mathbb{Q}\mid n \ge 0 \rbrace$. Then
$$A := \lbrace (x,y) \in \mathbb{Q} \times \mathbb{Z}[p^{-1}] \mid x-y \in \mathbb{Z}_{(p)} \rbrace$$
has the desired non-split extension. Informal $A$ consists of all pairs $(x,y) \in \mathbb{Q} \times \mathbb{Q}$ where $y$ is the $p$-part in the partial fraction decomposition of $x$ (up to an integer summand).
Proof: Let $\rho: A \to \mathbb{Q}$ be projection onto the first factor and $i: \mathbb{Z} \hookrightarrow A$ inclusion into the second factor. By partial fraction decomposition of the rationals, $\rho$ is surjective and $$\ker(\rho)=0 \times \big(\mathbb{Z}[p^{-1}] \cap \mathbb{Z}_{(p)}\big) = 0 \times \mathbb{Z} = \operatorname{im}(i).$$
Next, let $j: \mathbb{Q} \to A$ be a splitting hom. of $\rho$. Composing $j$ with the projection onto the second factor yields a hom. $f: \mathbb{Q} \to \mathbb{Z}[p^{-1}] \le \mathbb{Q}$. Since each endomorphism of $\mathbb{Q}$ is multiplication with some $q \in \mathbb{Q}$ we have $f(x)=qx \in \mathbb{Z}[p^{-1}]$ for all $x \in \mathbb{Q}$ which is only possible for $q=0$. Hence $j(x)=(x,0) \in A$ for all $x \in \mathbb{Q}$. Setting $x=1/p$ we obtain $1/p \in \mathbb{Z}_{(p)}$ which is the desired contradiction. QED
The answer is yes if you assume enough things. In particular, the notion of a left flat object of $\mathcal A$ comes up :
Definition: An object $L\in\mathcal A$ is left flat if $-\otimes L$ is exact.
My assumption will be that $\mathcal A$ has enough left flat objects. This allows you to even define $\mathbb L(P\otimes_Q -)$ in terms of left flat resolutions as I will explain below.
(Note : Here I'm using a definition of $\mathbb L$ which is slightly more general than "take projective resolutions", namely I'm using the one using "left defomations", see Riehl's book on homotopical algebra. If you want to use projective resolutions, the assumption is that $\mathcal A$ has enough left flat projectives)
Warning: Note that if $\mathcal A$ has projectives that are not left flat, then the answer is no even for $Q=*$, as is easy to convince yourself of. In particular, if $\mathcal A$ has enough projectives (to define $\mathbb L$ in the classical homological algebra sense), the assumption is that enough (equivalently all) projectives are left flat.
In this case, the answer is yes, and basically the proof is the same as in the ordinary case. Let me work in the bounded below case because there are the same subtleties as in the ordinary case for the unbounded case.
In particular, up to shifting, I will work in the connective case.
Let me sketch a proof below (convention : my functor categories are categories of $Ab$-enriched functors, I'm assuming this is what you meant - otherwise, replace $\hom_Q$ with $\mathbb Z[\hom_Q]$):
Definition: $P$ is right flat if $P\otimes_Q -$ is exact, and same for $X$ being left flat.
Lemma: Projectives in $Fun(Q^{op},Ab)$ are right flat, and there are enough left flats in $Fun(Q,\mathcal A)$.
Proof: By the enriched Yoneda lemma and smallness of $Q$, every projective is a summand of $\bigoplus_i \hom_Q(-,q_i)$ for some family of $q_i$'s. Therefore it suffices to prove it for those ones, because flatness is stable under retracts. Flatness is also stable under direct sums because they are exact ($\mathcal A$ is Grothendieck, in particular AB5), so it suffices to prove it for $\hom_Q(-,q)$. But now by the enriched Yoneda lemma again (the "canonical colimit of representable presheaves" version), we have $\hom_Q(-,q)\otimes_Q X \cong X(q)$, which is manifestly an exact functor.
The dual case is dual, you simply need to allow $\hom_Q(q,-)\otimes L$ for enough flat objects $L$ of $\mathcal A$.
In particular this lemma tells you that the two things you want to compare are well-defined.
Corollary: If $C$ is a chain complex of right flat functors $Q^{op}\to Ab$, $C\otimes_Q -$ preserves quasi-isomorphisms (where you define $\otimes_Q$ on chain complexes in the obvious way); and dually.
Proof : Let $n\in \mathbb N$, and let $C_{\leq n}$ denote the so-called stupid truncation of $C$, i.e. $0\to C_n\to ... \to C_0$. Because $-\otimes_Q -$ manifestly preserves filtered colimits in each variable, because filtered colimits are exact ($\mathcal A$ is Grothendieck, in particular AB5), and finally because $C= \mathrm{colim}_n C_{\leq n}$, it suffices to prove that each $C_{\leq n}\otimes_Q -$ preserves quasi-isomorphisms.
Now we have a short exact sequence $0\to C_{\leq n-1}\to C_{\leq n}\to C_n\to 0$ which is split as a sequence of graded objects. Note that the underlying graded object of $A\otimes_Q B$ only depends on the underlying graded objects of $A,B$ respectively. It follows that this exact sequence remains exact after tensoring (over $Q$) with anything.
In particular, by the long exact sequence in homology, and by induction, we reduce to proving that $C_n\otimes_Q-$ preserves quasi-isomorphisms for each $n$. But this is by the assumption that each $C_n$ is right flat.
The dual case is completely dual.
Note : I am implicitly using the fact that $-\otimes -: Ab\times\mathcal A\to\mathcal A$ preserves colimits in each variable. This is an exercise I'll leave to you :)
Note : This lemma is essentially what allows you to define $\mathbb L$ using left/right flat resolutions.
Note: This lemma is often packaged as a spectral sequence argument, but it's really about the filtration.
Corollary: $\mathbb L(P\otimes_Q -)(X)\simeq \mathbb L(-\otimes_Q X)(P)$.
Proof : Pick a right flat resolution $\tilde P$ of $P$, and a left flat resolution $\tilde X$ of $X$ (those exist by the first lemma).
You have a zigzag $$\mathbb L(P\otimes_Q -)(X) =P\otimes_Q \tilde X \to \tilde P\otimes_Q \tilde X \leftarrow \tilde P\otimes_Q X = \mathbb L(-\otimes_Q X)(\tilde P)$$
The extreme equalities are by definition, and the two middle arrows are quasi-isomorphisms by the previous corollary.
Note : this quasi-isomorphism can be made as natural as left flat resolutions in $\mathcal A$.
Best Answer
I'll start by describing the notation that I'll use.
I'll think of elements of $\mathbb{Z}^\mathbb{N}$ as infinite column vectors $${\bf x}=\begin{pmatrix} x_0\\x_1\\x_2\\\vdots \end{pmatrix}$$ of integers, with rows indexed by $\mathbb{N}$.
The $i$th "unit vector" will be denoted by ${\bf e(i)}$. I.e., $e(i)_i=1$ and $e(i)_j=0$ for $j\neq i$.
As described in the question, Specker's result implies that endomorphisms of $\mathbb{Z}^\mathbb{N}$ are of the form ${\bf x}\mapsto A{\bf x}$ where $$A=\begin{pmatrix} a_{00}&a_{01}&a_{02}&\cdots\\ a_{10}&a_{11}&a_{12}&\cdots\\ a_{20}&a_{21}&a_{22}&\cdots\\ \vdots&\vdots&\vdots&\ddots \end{pmatrix}$$ is an infinite matrix of integers, with rows and columns indexed by $\mathbb{N}$, that is row-finite (i.e., each row has only finitely many non-zero entries).
Now suppose that $\alpha:\mathbb{Z}^\mathbb{N}\to\mathbb{Z}^\mathbb{N}$ is a surjective group homomorphism, given by a row-finite matrix $A$.
To prove that $\alpha$ is split, we need to find a right inverse. In fact, it suffices to find another row-finite matrix $B$ such that $AB$ is lower unitriangular (i.e., diagonal entries are all $1$ and entries above the diagonal are all $0$), since such a matrix is invertible.
Fix $n\geq0$, and decompose $\mathbb{Z}^\mathbb{N}=G_n\oplus H_n$, where $G_n\cong\mathbb{Z}^n$ is the subgroup consisting of those ${\bf a}$ with $a_i=0$ for $i\geq n$, and $H_n\cong\mathbb{Z}^\mathbb{N}$ is the subgroup consisting of those ${\bf a}$ with $a_i=0$ for $i<n$.
Since $\alpha$ is surjective, $\mathbb{Z}^\mathbb{N}=\alpha\left(G_n\right)+\alpha\left(H_n\right)$.
Consider the quotient maps $$\mathbb{Z}^\mathbb{N}\stackrel{\theta}{\to} \mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\stackrel{\varphi}{\to} \left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right)/T\left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right),$$ where $T(X)$ denotes the torsion subgroup of an abelian group $X$.
Since $\left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right)/T\left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right)$ is a finite rank free abelian group, using Specker's result again implies that $\varphi\theta({\bf e(i)})=0$, or equivalently $\theta({\bf e(i)})\in T\left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right)$, for all but finitely many $i$.
Since $T\left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right)$ is finite, for all but finitely many $i$ with $\theta({\bf e(i)})\in T\left(\mathbb{Z}^\mathbb{N}/\alpha\left(H_n\right)\right)$, there is $i'>i$ with $\theta({\bf e(i')})=\theta({\bf e(i)})$, or equivalently ${\bf e(i)}-{\bf e(i')}\in\alpha(H_n)$.
So we can choose a sequence $0=t_0<t_1<t_2<\dots$ of integers such that for every $i\geq t_n$ there is some $i'>i$ and some ${\bf b(i)}\in H(n)$ with ${\bf e(i)}-{\bf e(i')}=\alpha\left({\bf b(i)}\right)$. For each $i$, do this with the largest $n$ such that $i\geq t_n$, and let $B$ be the matrix whose $i$th column is ${\bf b(i)}$.
Then $B$ is row-finite, and $AB$ is lower unitriangular.