Importing javascript table into Google Docs spreadsheet

Question

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

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
– Sponge, Commented Aug 21, 2015 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
– Sponge, Commented Aug 21, 2015 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
– Joshua Dannemann, Commented Aug 21, 2015 at 18:58

Joshua Dannemann · Accepted Answer · 2015-08-21 18:58:56Z

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.

edited Aug 21, 2015 at 18:58

answered Aug 15, 2015 at 23:20

Joshua Dannemann

2,1002 gold badges16 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing javascript table into Google Docs spreadsheet

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related