2

I am attempting to import the leaderboard at pgatour.com/leaderboard.html or golf.com/leaderboard (sorry, can't post more than 2 links since I have <10 reputation).

Based on my research, it seems that the IMPORTHTML & IMPORTXML functions are unable to retrieve the data because the table doesn't actually exist at the time the import function runs, as the table/data is loaded via javascript after the function reads the page's html/xml. Example:

=IMPORTXML("http://www.golf.com/leaderboard","//*[@id='leaderboardPositionTable']")
=IMPORTHTML("http://www.pgatour.com/leaderboard.html","table",1)

Is anyone aware of a way to pull such a table into a Google Docs spreadsheet? I tried to follow the advice here, but honestly don't have a clue if importing JSON is the right approach, or if I'm even doing it correctly.

Any help would be much appreciated. Thanks

Rubén
  • 34,714
  • 9
  • 70
  • 166
Sponge
  • 21
  • 1
  • 2
  • I know how to do this, but I am on my phone right now. If nobody answers by tomorrow I will get back to you. – Joshua Dannemann Aug 15 '15 at 01:01
  • Any luck on trying out the solution I posted? If that works for you, please make sure to accept my answer. Thanks, – Joshua Dannemann Aug 20 '15 at 16:08
  • Joshua, thank you for your help and answer. Unfortunately, I haven't yet had the opportunity to work on this and apply your suggestion. I will also need to figure out how to write the JSON information to the spreadsheet. – Sponge Aug 21 '15 at 18:29
  • I do have a question, however. Is this a different approach than fastfedora's ImportJSON code referenced in the "advice here" I linked in my question? Your solution certainly looks much simpler. Thanks for your help. Will follow up as soon as I've had a chance to tinker with this. – Sponge Aug 21 '15 at 18:38
  • Yes. I would, however, advise not to use a JSON import library. Since the JSON is not in a standard format for importing it as a table, those kinds of tools will probably frustrate more than help. What you could do is have the process read through the data while creating arrays for each table and then feed it into one of those tools to do the import, or just clear the spreadsheet and write the information onto it from a macro. – Joshua Dannemann Aug 21 '15 at 18:58

1 Answers1

1

After looking into this, I think you should take the following approach. It turns out that leaderboard is available as a JSON file. I wrote a function to test out the import and it works well.

function update_leaderboard(){
  var url = "http://www.pgatour.com/data/r/033/leaderboard-v2.json?ts=" + new Date().getTime()
  var result = UrlFetchApp.fetch(url);
  var response = result.getContentText();
  var data = JSON.parse(response);
  var w = SpreadsheetApp.getActive();
  var s = w.getActiveSheet();
  s.clear();
  //Write the JSON to the spreadsheet...
}

Having gotten that far, the only thing left to do is write that information onto the spreadsheet.

Joshua Dannemann
  • 2,003
  • 1
  • 14
  • 34