Singer-songwriter, who co-wrote “Good Vibrations” and “California Girls” with Wilson, was honored at Songwriters Hall of Fame