Home > OS >  Any way to pull player data using (Google Sheets) IMPORTHTML or XML
Any way to pull player data using (Google Sheets) IMPORTHTML or XML

Time:02-12

Is there any way I can pull the players from this website onto google sheets using importhtml or xml.

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:

output2

  • Related