The accepted answer was not easy for me to see and it took me a while to do it in a more step-by-step manner:
The spherical harmonics are orthonormal by definition:
$$\int_{\theta=0}^{\pi} \int_{\varphi=0}^{2 \pi} Y_{\ell}^{m} Y_{\ell^{\prime}}^{m^{\prime} *} d \Omega=\delta_{\ell \ell^{\prime}} \delta_{m m^{\prime}}$$
where $d \Omega=\sin (\theta) d \varphi d \theta$ and $\delta$ is the Kronecker delta and is 1 if the indices are the same and 0 otherwise. We can now set $m^\prime = \ell^\prime =0$. If you insert this into the definition of the spherical harmonic, $Y_{l^\prime}^{m^\prime}(\theta, \phi)=\sqrt{\frac{2 l^\prime+1}{4 \pi} \frac{(l^\prime-m^\prime) !}{(l^\prime+m^\prime) !}} P_{l^\prime}^{m^\prime}(\cos (\theta)) \exp (\mathrm{i} m^\prime \phi)$ you can see that it yields $1/\sqrt{4 \pi}$. We substitute this back into the equation above to obtain
$$
\int_{\theta=0}^{\pi} \int_{\varphi=0}^{2 \pi} Y_{\ell}^{m} 1/\sqrt{4 \pi} d \Omega=\delta_{\ell 0} \delta_{m 0}
$$
and multiply by $\sqrt{4 \pi}$ to see that the result to your desired integral is
$$
\int_{S^{2}} Y_{l}^{m} \mathrm{~d} S^{2} = \sqrt{4 \pi} \delta_{\ell 0} \delta_{m 0}
= \left\{\begin{array}{ll}
\sqrt{4 \pi} & \text { if } l=0 \text { and } m=0 \\
0 & \text { otherwise }
\end{array}\right.
$$
What does "functions defined on the surface of a sphere" mean?
You can literally define the spherical harmonics on a sphere:
In your article, the following formula is given:
$$Y_l^m(\theta,\phi) = \sqrt{\frac{(2l+1)}{4\pi}\frac{(l-m)!}{(l+m)!}} P_l^m(\cos \theta) e^{im\phi}$$
Clearly, $Y(\theta,\phi)$ is well defined for all $(\theta,\phi)\in\mathbb R\times\mathbb R$. On the other hand\begin{align}F\colon[0,\pi]\times\mathbb R&\to S\\(\theta,\phi)&\mapsto\begin{pmatrix}\sin\theta\cos\phi\\\sin\theta\sin\phi\\\cos\theta\end{pmatrix}\end{align} is a surjective function to the sphere. Thus, if a spherical harmonic $Y$ is constant on the level sets of $F$, we can define $Y$ on the sphere by requiring that $$(Y\circ F)(\theta,\phi)=Y(\theta,\phi)$$for all $(\theta,\phi)\in[0,\pi]\times\mathbb R$ (note the slight abuse of notation).
Well, it turns out that spherical harmonics are indeed constant on level sets of $F$: For the north pole and the south pole, you can find the explanation here and for the other points on the sphere - i.e. the points with $0<\theta<\pi$ - this is easy to prove.
Best Answer
I think the point that was confusing me/missing link was that spherical harmonics functions are the solution of the Laplace's differential equation:
$$\frac{\partial^2u}{\partial x^2}+\frac{\partial^2u}{\partial y^2}+\frac{\partial^2u}{\partial z^2}=0$$
Orthogonal means the functions "pull in different directions". Like in linear algebra, orthogonal vectors "pull" in completely "distinct" directions in n-space, it turns out that orthogonal functions "help you reach completely distinct values", where the resultant value (sum of functions) is again a function.
SH are based on the associated Legendre polynomials, (which are a tad more funky than Legendre polynomials, namely each band has more distinct functions defined for it for the associated ones.)
The Legendre polynomials themselves, like SH, are orthogonal functions. So if you take any 2 functions from the Legendre polynomial set, they're going to be orthogonal to each other (integral on $[-1,1]$ is $0$), and if you add scaled copies of one to the other, you're going to be able to reach an entirely distinct set of functions/values than you could with just one of those basis functions alone.
Now the sphere comes from the idea that, SH functions, use the Legendre polynomials (but Legendre polynomials are 1D functions), and the specification of spherical harmonics is a function value for every $\phi \theta$. There is no "sphere" per se.. it's like if you say "there is a value for every point on the unit circle", it means you trace a circle around the origin and give each point a value.
What is meant is every point on a unit sphere has a numeric value. If we associate a color to every point on the sphere, you get a visualization like this:
This page shows a visualization where the values of the SH function are used to MORPH THE SPHERE (which is part of what was confusing me earlier). But just because a function has values for every point on the sphere doesn't mean there is a sphere.