I never really saw the attempted rape as such a big deal.
But it's not because I want to justify Spike's actions, mind you. I can't stand Spike and I firmly believe that pandering to Spike's fans was one of the prime reasons BtVS was ruined. The thing is the AR totally pales in comparison with what Spike had done before. Thousands of murders, most likely many rapes too. Excuse my total non-shock that he would try to rape the woman he claims to love. He is the "do that guy". When Drusila seemed to like Angel more than him in S2 his response was to hit her on the head. In Crush he kidnaps Buffy too. Really, the only shocking thing is it took Spike so long to do it.
Well, for me, I never watched Buffy during it's original run, so I really don't know what the fans' reactions were during that time. But, just watching it all recently, I didn't think anything or any character was ruined as the series progressed. That's just my opinion though.
For me, the "shocking" aspect was the fact that since falling in love with Buffy, Spike wanted to be good and didn't want to hurt her, but the AR totally threw any progress he made out the window. It was a harsh reminder that Spike still had no soul. In "Crush," he told Buffy that he could be a good man. But, of course, she didn't believe him for the simple fact that he didn't have a soul. Their banter in "Crush" --
Buffy: "Spike, please you're a vampire."
Spike: "Angel was a vampire."
Buffy: "Angel was good." -- since in her eyes, vampires with a soul were good vampires.
Spike: "And I can be too. I've changed, Buffy."
He wanted to prove to her again and again that he could be good, although he didn't have a soul. She really began to recognize that in the last scene of "Intervention." From that point on, she started to trust him. She had him come along with the Scoobies to protect Dawn and invited him back inside her house in "The Gift." Yet, the whole having no soul still got in the way when they started a relationship. Then, in "Entrophy," Spike mentions that he doesn't hurt Buffy, although he may lie, cheat, manipulate, etc. And she says she knew that. But he says: "No you don't. I've tried to make it clear to you, but you won't see it. Something happened to me. The way I feel about you. It's different..."
Then, after sleeping with Anya, he didn't think he was hurting Buffy. He thought he was doing exactly what she wanted, which was moving on. Surprisingly, she was hurt and Dawn even told him that in "Seeing Red," which prompted him to explain himself to Buffy. "I'm sorry. Not that it matters anymore, but I needed you to know that."
My point in all this is that up until the AR, Spike was really trying to be good and not hurt Buffy, despite the fact he had no soul. The AR was shocking, at least for me, because Spike, the man who loved and would die for Buffy, would never do something like that. But, he did because there was still that demon inside and he had no soul. Everything that he tried to prove to her up until that point really didn't matter because he was reminded he had no soul. It also confirmed what Buffy said about him, that he was an evil, soulless thing, not capable of love or doing any good. And that was something he always wanted to prove her wrong about.