“Chebyshev and Fourier Spectral Methods” by Boyd is a pretty good textbook on the sort of thing that ApproxFun and Chebfun automate, and can be downloaded free of charge online. Trefethen has a couple of books on this sort of thing, too.
My short answer is that spectral methods of this sort are extremely effective when the functions are smooth, or all singularities can be accounted for analytically, and the domain can be mapped smoothly to a box. In that case the exponential convergence is awesome, and also the fact that it turns everything into a polynomial is great — e.g. things like root-finding are a snap.
For complicated domains or problems filled with oddly arranged discontinuities, especially in 3d, however, it can be really hard to get spectral (faster than polynomial) convergence, and once that happens schemes based on generic grids/meshes are often more competitive. (Sheehan and Alex have done a great job in extending the power of spectral methods, however, especially in 2d, and they can now handle a surprising range of problems.)
For example, I do a lot of work on 3d electromagnetic scattering problems off of materials arranged into complicated shapes (e.g. imagine computing the radar cross-section of an airplane). Because there are discontinuities in the electric field at every material interface, not to mention corner singularities all over the place, it is usually not practical to get spectral convergence in these problems.
Another drawback of spectral methods is that you can’t easily obtain the kind of nonuniform spatial resolution that you can get with adaptive mesh refinement in FEM. So, if you need a lot more spatial resolution in some regions than in others, they aren’t as attractive. (There are ways to do it, but they aren’t automated AFAIK.) An example that I’ve encountered is a scattering problem involving two nearly touching spheres: even though spheres are smooth high-symmetry shapes that admit a natural spectral basis in spherical harmonics (“Mie scattering”), when the spheres are almost touching the uniform angular resolution of spherical harmonics kills you.