Many promising applications of surface-enhanced Raman scattering (SERS), such as microfluidic SERS and electrochemical (EC)-SERS, require immersion of plasmonic nanostructured films in aqueous media. Correlational investigations of the optical response and SERS efficiency of solid SERS substrates immersed in water are absent in the literature. This work presents an approach for tuning the efficiency of gold films over nanospheres (AuFoN) as SERS substrates for applications in aqueous environment. AuFoN are fabricated by convective self-assembly of colloidal polystyrene nanospheres of various diameters (300–800 nm), followed by magnetron sputtering of gold films. The optical reflectance of the AuFoN and Finite-Difference Time-Domain simulations in both water and air reveal the dependence of the surface plasmon band on nanospheres’ diameter and environment. SERS enhancement of a common Raman reporter on AuFoN immersed in water is analyzed under 785 nm laser excitation, but also using the 633 nm line for the films in air. The provided correlations between the SERS efficiency and optical response in both air and water indicate the best structural parameters for high SERS efficiency and highlight a route for predicting and optimizing the SERS response of AuFoN in water based on the behavior in air, which is more practical. Finally, the AuFoN are successfully tested as electrodes for EC-SERS detection of the thiabendazole pesticide and as SERS substrates integrated in a flow-through microchannel format. The obtained results represent an important step toward the development of microfluidic EC-SERS devices for sensing applications.Supplementary InformationThe online version contains supplementary material available at 10.1186/s11671-023-03851-3.