Is there any way I can pull the players from this website onto google sheets using importhtml or xml.
1 Answers
There is something with the site that prevents access on to the data (tested and got the same issue with script).
However, if you only want the list of the names of the players, you could use another link (e.g. https://www.espn.com/nhl/team/roster/_/name/chi/chicago-blackhawks) to fetch the player names.
Formula:
=arrayformula(regexreplace(transpose({
importxml("https://www.espn.com/nhl/team/roster/_/name/chi/chicago-blackhawks", "//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[1]/div[2]/div/div[2]/table/tbody"),
importxml("https://www.espn.com/nhl/team/roster/_/name/chi/chicago-blackhawks", "//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[2]/div[2]/div/div[2]/table/tbody"),
importxml("https://www.espn.com/nhl/team/roster/_/name/chi/chicago-blackhawks", "//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[3]/div[2]/div/div[2]/table/tbody"),
importxml("https://www.espn.com/nhl/team/roster/_/name/chi/chicago-blackhawks", "//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[4]/div[2]/div/div[2]/table/tbody"),
importxml("https://www.espn.com/nhl/team/roster/_/name/chi/chicago-blackhawks", "//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[5]/div[2]/div/div[2]/table/tbody")
}), "\d.*", ""))
Output:
You can store your links and paths into cells and then just call them so you can reuse them (xpaths) with other teams as well and have the final formula easier to read. See sample below using another team (Colorado Avalanche)
B1 (link to the team):
https://www.espn.com/nhl/team/roster/_/name/col/colorado-avalanche
E1 - E5 (xpaths of each table):
//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[1]/div[2]/div/div[2]/table/tbody
//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[2]/div[2]/div/div[2]/table/tbody
//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[3]/div[2]/div/div[2]/table/tbody
//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[4]/div[2]/div/div[2]/table/tbody
//*[@id='fittPageContainer']/div[2]/div[5]/div/div/section/div/section/div[2]/div[5]/div[2]/div/div[2]/table/tbody
Formula:
=arrayformula(regexreplace(transpose({
importxml(B1, E1),
importxml(B1, E2),
importxml(B1, E3),
importxml(B1, E4),
importxml(B1, E5)
}), "\d.*", ""))
Output:
Related