Say I send a connection request to some website (eg. Google) through a router, which then sends it to the modem. If the modem is powered off, what happens? How does the router handle the resulting connection failure? Thanks.
The router does simply not handle the connection failure. It simply sends the packet and waits for a response (which it forwards on if/when it receives). The handling of the connection request is done by the device making the TCP connection - and I believe that was one of the innovations which made "TCP/IP" scalable enough to use on an Internet-Size system.
I note that firewalls may keep track of packets (but this is not a routing job), and may do stuff with traffic, but I'd say thats not even typical. (Except when the router is doing NAT, but except for address rewriting and logic and tracking done for that purpose, its still a matter of simply send a packet, wait for a response, forward if received. If not received in a reasonable timeframe, reset tracking)