How do I use ImportJson to import Lichess.org chess data into Google Sheets from this Json file?

user_16920065 0
Viewed 67

I have been trying for the past week to build a master leaderboard for my middle school chess clubs in Google Sheets. We play on the website Lichess.org which has a great API. I managed to get the tournament id using ImportXML, but the results I am trying to access don't seem accessible with any of the ImportJson scripts out there added to sheets.

This is the page with the student results https://lichess.org/tournament/2bHOVix0 I want to get the "name" and "score" for each of the top 3 results First, I assume I cannot just use ImportXML to get that data from the above link even though it shows up in "view page source"

Assuming that would not work I used the Lichess API to download the json file with the correct data via this link http://lichess.org/api/tournament/NguuB8pO/results

Is there a reason this "Importjson" will not work? Does it matter that the API describes the json as a ndjson? =ImportJSON("http://lichess.org/api/tournament/NguuB8pO/results","/name") I have tried is at least 20 different ways with different importjson scripts added to my sheets.

Thanks in advance for any help you can offer. -Mike

google-sheets
1 Answers

Try, assuming url = https://lichess.org/tournament/2bHOVix0 , table = /data/standing/players and xpath = /name /rank /rating /score

var resTable = []; 
function ImportJSON(url,table,xpath){
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.split('lichess.load.then(()=>{LichessTournament(')[1].split(')})</script>')[0]
  var data = JSON.parse(jsonString)
  var json = eval('data'+table.replace(/\//gm,"\."))
  if (typeof xpath == 'object'){var liste = xpath.join().split(",")} else {var liste = xpath.split("|")}
  if (json.length){json.forEach(function(elem){getData(elem,liste)})} else {getData(json,liste)}
  return resTable
}
function getData(elem,liste){
  var prov=[]
  liste.forEach(function(chemin){
    var t=chemin.split('/');
    var obj=elem;
    try{
      for (var i=1;i<t.length;i++){obj=obj.item(t[i])}
      if(typeof obj=='object'){prov.push('['+obj+']')}else{prov.push(obj)}
    }
    catch(e){prov.push('')}
  })
  resTable.push(prov)
}
Object.prototype.item=function(i){return this[i]};

https://docs.google.com/spreadsheets/d/1eBFjpgwRAXyNVXOODaCAviyAQv9YsDoLBGqhzM9sm2M/copy

user_150193800
Related