I've recently started working on a '94 Grand Am SE, it was my little brothers and now he's selling it. It also leaked water onto the floor whenever it rained, and I finally found the cause. Apparently it's a relatively common problem with these cars. We tried a variety of things while he was still driving it, but nothing ever worked.
I happened to remove the HVAC blower housing because the plastic cover was all busted up, and I happened to have a spare one from a parts car I dismantled a couple years ago. Once I removed that I found the leak, where the water drains from under the cowl through the firewall to the ground, had been rusting out the firewall. Someone had apparently tried to fix it previously by filling it with bathroom caulk from the inside, but that had failed.
I cleared away as much of the rust and old caulk as I could and then went a little crazy with a can of that insulating spray foam, put it inside the hole, and all around the firewall up to the drain and down to the power steering rack. After it cured I used a razor blade and as best as I could, shaved it off flat and at an angle away from the firewall, then cut a small trench in it directly below the drain from the cowl to keep the water from pooling up in any crevices in the foam. Now at no point should water coming from that drain ever touch the metal of the firewall, it should drain freely to the ground, and the hole into the interior of the car is completely sealed up.
So far after washing the car, two rainstorms, and intentionally running water through the drain with a hose, the carpets have stayed completely dry, whereas before after even the lightest rainfall, there would be standing water on the passenger side floor. As for the driver side, this car never had a problem on that side, but there is another drain under the cowl on that side, so potentially it could also rust out the firewall there and drain into the driver side floor. That drain is a little harder to get at though, I think it's kind of near the brake booster and is difficult to reach without removing a lot of stuff. But if that's what it takes to stop the leak, then that's what you have to do.
For good measure, when I reinstalled the cowl (I had removed it earlier when trying to diagnose the leak) I put a nice bead of clear silicone caulk between it and the glass, then trimmed it back with a razor blade after it cured. That way, all the water from the windshield will go through the appropriate holes in the cowl, and no where else. There's an open air box thing behind the blower housing and under the cowl which leads straight to the carpet via the lower heating ducts, so if water were getting under the cowl and dripping into that, it soaks the carpet.
BTW, removing the blower housing is a little bit of a PITA. There's I think 7 nuts/bolts holding it on, but some are hard to reach, and the bottom two were rusted badly. When removing those I snapped the bolts off. With everything back together though it seems to be sealed up tight, despite the two missing on the bottom.
Anyways, sorry for the length, I tend to be thorough, but hopefully the info will help someone else out.