google sheet importhtml with click show more

Question

I'm trying to import NHL data from https://www.espn.com/nhl/stats/player/_/view/skating/table/offensive/sort/goals/dir/desc

The problem is that I can't import the 733 rows because of a "Show more" which make a little import of 51 rows. I'd like to import all 733 rows.

Here's the code that I put in a cell of my google sheet:

=IMPORTHTML("https://www.espn.com/nhl/stats/player/_/view/skating/table/offensive/sort/goals/dir/desc";"Table";2;)

Thanks for your time,

Vincent

Answer 1

You can extract main informations with this script

function hockey() {
  var url = 'https://site.web.api.espn.com/apis/common/v3/sports/hockey/nhl/statistics/byathlete?region=us&lang=en&contentorigin=espn&isqualified=false&limit=1000&sort=offensive.goals%3Adesc&category=skaters'
  var json = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('extraction')
  sh.clearContents()

  var labels = []
  json.categories[0].labels.forEach(function (e) { labels.push(e) })
  json.categories[1].labels.forEach(function (e) { labels.push(e) })
  sh.getRange(1, 7, 1, labels.length).setValues([labels])

  var names = []
  json.categories[0].displayNames.forEach(function (e) { names.push(e) })
  json.categories[1].displayNames.forEach(function (e) { names.push(e) })
  sh.getRange(2, 7, 1, names.length).setValues([names])

  var athletes = []
  json.athletes.forEach(function (data) {
    var prov = []
    prov.push(data.athlete.firstName)
    prov.push(data.athlete.lastName)
    prov.push(data.athlete.debutYear)
    prov.push(data.athlete.age)
    prov.push(data.athlete.teamName)
    prov.push(data.athlete.teamShortName)
    data.categories[0].totals.forEach(function (t) { prov.push(t) })
    data.categories[1].totals.forEach(function (t) { prov.push(t) })
    athletes.push(prov)
  })
  sh.getRange(3, 1, athletes.length, athletes[0].length).setValues(athletes)
}

I got 970 players at once with all these informations

GP +/- TOI/G SHFT PROD WINS L TOI G A PTS PPG PPA S SPCT GWG FW FL FO% SOA SOG SOPCT

Answer 2

There's a similar question here.

Basically it is stated that if the data displayed is controlled by JavaScript, it is not possible to fetch the data using import() functions from Google Sheets.

In your case checking if the NHL data you want to retrieve is controlled by JavaScript I went to the URL, disabled JavaScript (by clicking on the padlock icon next to the URL in the URL bar > clicking on Site Settings > setting JavaScript to 'Block'> refreshing the website) when clicking on 'Show more' it did not show anything else, so the data is indeed controlled by JavaScript.

Answer 3

You can try importJson, but you need to write some script.
Attack this URL which will return you all 970 players json format.
https://site.web.api.espn.com/apis/common/v3/sports/hockey/nhl/statistics/byathlete?region=us&lang=en&contentorigin=espn&isqualified=false&limit=1000&sort=offensive.goals%3Adesc&category=skaters.
I did it and it imports all players, but with a different format, maybe you need to fix something on script from tutorial. Get data like this:

I hope I was able to help you.