My partner and I visited the Magic Fountain on a Sunday evening in July 2013. We took the Metro from Placa de Catalunya to Placa de Espanya which is 2 euros each way and takes about 6 minutes. When you exit the metro, walk towards the two towers and the fountain is at the top of that street.
We arrived just before 9pm & purchased drinks from the onsite food & drink vans (which were very good value!) The displays started just before 9:30pm and as the night becomes darker the true 'magic' of the fountain becomes clear! The displays are fantastic - there were numerous musical themes on the night we were there, starting with a Disney theme, Chart music & Classical numbers. It really is an event for the whole family - there were thousands of people there, from small children to the elderly.
This has to be seen if you are in Barcelona - and to make it even better, it is FREE! The displays are on from around 9pm to 11pm on Thursdays to Sundays during the summer. Check the official website before travelling just in case! More