Google Sheets IMPORTXML issue with ESPN table format

Question

In A1 I have: =IMPORTXML("https://www.espn.com/mens-college-basketball/lines","//tr")

This works as expected. However, if you go to the above page you will see half the table hidden and will need to click the show more button to view the entire table. How can I import the entire table?

Answer 1

You cannot do it directly: when IMPORTXML fetches the page the data is not on the page yet. It is loaded from the backend only when you click the Show More button. IMPORTXML cannot click buttons and load data after that.

Luckily, the data on the page is loaded from a publicly accessible API: https://site.web.api.espn.com/apis/fitt/v3/sports/basketball/mens-college-basketball/dailyline?region=us&lang=en&contentorigin=espn&groups=50&limit=20&page=1 . When you click the button, the website loads another page of data from the same API.

To load all data you will need to use a custom Google Apps Script function and use it in the spreadsheet via =fetchData():

function fetchData() {
  let url = "https://site.web.api.espn.com/apis/fitt/v3/sports/basketball/mens-college-basketball/dailyline?region=us&lang=en&contentorigin=espn&groups=50&limit=100&page=1";
  const games = [];
  const rows = [];

  while (url) {
    const response = UrlFetchApp.fetch(url);
    const data = JSON.parse(response.getContentText());
    games.push(...data.dailyLines);
    url = data.pagination.next;
  }

  games.forEach(game => {
    rows.push([
      new Date(game.gameDate),
      "REC (ATS)",
      "LINE",
      "ML",
      "BPI",
    ]);
    rows.push([
      game.awayTeam.team.nickname,
      `${game.awayTeam.record} (${game.awayTeam.ats})`,
      game.awayTeam.line,
      game.awayTeam.moneyLine,
      game.awayTeam.powerIndex,
    ]);
    rows.push([
      game.homeTeam.team.nickname,
      `${game.homeTeam.record} (${game.homeTeam.ats})`,
      game.homeTeam.line,
      game.homeTeam.moneyLine,
      game.homeTeam.powerIndex,
    ]);
  });

  return rows;
}

Notes:

1. In the script I have tried to replicate the format that you got via IMPORTXML. Still, there are some differences, for example, BPI has more precision and does not have % sign. You can compare the data on the screenshot below:

   

   Probably, there are more efficient ways to structure the data for your specific use case.

2. This API is not guaranteed to stay available. They can change its format or restricted unauthorized access.

3. This script can be extended to run on schedule or directly from the Google Apps Script editor.