Trying to refresh import XML xpath query, jquery string changes URL and breaks the request

0
votes

I have successfully used importxml to pull down data from live NBA games on espn.com. However, I need the importxml function to refresh every minute (or some frequent amount of time) to keep fetching the live data and have it update in my google sheets.

I found this script online to refresh the import xml but it adds a jquery string to the end of the URL for cache busting. The jquery string at the end of the URL changes the URL from being in a specific game like this: https://www.espn.com/nba/game?gameId=401161051 (which would successfully fetch the data) back to just the NBA scoreboard: https://www.espn.com/nba/game?gameId=401161051.1234232

I need the import xml function to successfully refresh while cache busting, without adding a random number to the end of the URL because espn sends you to a different page if that happens.

This is my import XML function that works successfully

=IMPORTXML("https://www.espn.com/nba/matchup?gameId=401161049","//tr[20]")
2
google-sheetsgoogle-sheets-formula
We need a sample sheet link, your formula returns imported content is empty you can't use rand() in import functions so not sure how you even tested random number. - CodeCamper

2 Answers

0
votes

Assuming your import formula is in cell A2 you will make the following modification:

=IMPORTXML("your link&"&A1,"your xpath")

Then in cell A1 do

=today()^0+A1

Don't forget to enable these settings:

enter image description here

Congrats it updates every minute and edit.

0
votes

Answer:

You can use an Apps Script time-based trigger to go off every minute and update the cell in question.

More Information:

From the Tools > Script editor menu item, you can create a function with the following code:

function updateTheSheet() {
  var formula = '=IMPORTXML("https://www.espn.com/nba/matchup?gameId=401161049","//tr[20]")'
  ss = SpreadsheetApp.getActiveSpreadsheet()
  ss.getSheets()[0].getRange('A2').setFormula(formula);
}

Assuming that the cell you wish to have update is in the first sheet, in cell A2. You can change this in the getRange() function.

From here, save the script with the save icon, press the run button (►), and confirm the authentication of running the script.

From here, following the Edit > Current project's triggers menu item, you will have a new page open in the G Suite Developer Hub. Click the + Add Trigger button in the bottom right and set up the trigger settings as follows:

  • Choose which function to run: updateTheSheet
  • Choose which deployment should run: Head
  • Select event source: Time-driven
  • Select type of time based trigger: Minutes timer
  • Select minute interval: Every minute

And press save. Now, the function will run each minute and update that cell with the formula, causing the formula to recalculate and update itself.

References: