I don't know for sure exactly how Fourier realized that you could represent any periodic function as the countable sum of sines and cosines, but I strongly suspect the realization that this might be the case came from studying solutions to simple, separable PDEs (most first PDE courses will cover this in some depth).
A Partial Differential Equation (PDE) describes the rate of change of a quantity that depends on more than one independent variable, for example if we consider the heat equation in one dimension:
\begin{equation}
u_t(t,x) = ku_{xx}u(t,x), \quad k \in \mathbb{R},
\end{equation}
we have that $u:\mathbb{R_+}\times(-L,L) \to \mathbb{R}$ is a scalar quantity corresponding to temperature that depends on time $t$ and one spatial variable $x$. This is a simple model for how the distribution of heat in an iron bar of length $2L$ changes with time. The equation says that the rate of change of temperature at one point $x$, with respect to time is proportional to the second spatial derivative of heat at that point (intuitively, the second spatial derivative can be interpreted as a measure of the nonuniformity of the heat distribution - if the temperature gradients are very large, the temperature will change more quickly. This hopefully aligns with your intuition about how heat conduction works).
One of the nice things about the heat equation is that it's a $\textit{separable}$ equation, that is if we suppose the solution $u(t,x)$ can be written as the product of two terms that depend only on one of each of the independent variables, so that $u(t,x) = T(t)X(x)$ for two unknown functions $T$ and $X$ of time and space respectively, then we actually can work it through, get an answer and justify that our assumption was correct. Plugging this into the heat equation, we get that
\begin{equation}
T'(t)X(x) = kT(t)X''(x) \implies \frac{X''(x)}{X(x)} = \frac{T'(t)}{kT(t)}.
\end{equation}
Now, $\frac{X''(x)}{X(x)}$ is a function of $x$ only and $\frac{T'(t)}{kT(t)}$ is a function of $t$ only. Since $t$ and $x$ are independent, the only way this equality can hold for all $t$ and $x$ is if both ratios are constant (this is kind of tricky until you see it, think carefully about it for a while). I'll call this constant $-\lambda^2$ for reasons that will become clear.
We then have that
\begin{equation}
X''(x) = -\lambda^2X(x),
\end{equation}
which has general solution $X(x) = A\cos(\lambda x) + B\sin(\lambda x)$. Starting to look promising? If we put boundary conditions on the bar so that the temperature at each end is fixed at zero, then the corresponding conditions on $X$ are $X(-L) = X(L) = 0$. What does this mean for our general solution?
\begin{equation}
A\cos(\lambda(-L)) + B\sin(\lambda(-L)) = 0 \,\, \text{and}\,\, A\cos(\lambda(L)) + B\sin(\lambda(L)) = 0 \implies \cos(\lambda L) = 0
\end{equation}
By adding together using the evenness/oddness of cos and sin. This implies $\lambda = (\pi/2 + n\pi)/L$ for any $n \in \mathbb{N}$ as these are all zeroes of the cosine function. Now, by linearity, if any such $n$ gives a solution, so must the sum of all such $n$, and so with a little trig rearrangement and relabelling to remove the phase shift, this gives
\begin{equation}
X(x) = \sum_{n=1}^{\infty}\left(A_n\sin\left(\frac{n\pi x}{L}\right) + B_n\cos\left(\frac{n\pi x}{L}\right)\right)
\end{equation}
Where obviously I'm playing a bit fast and loose but hopefully it gives you the idea. Notice I haven't addressed anything to do with $t$, that's a story for a different time, but I do think that given describing an unknown function (a spatial solution of the heat equation) as a sum of sines and cosines drops very easily out of the model, it's not then such a great leap to extend this idea to any unknown functions (either periodic or bounded such that they admit periodic extension), and so represents a possible path that Fourier and his contemporaries might have taken.