I'm trying to use IMPORTJSON to import some data from:
https://templeosrs.com/api/player_stats.php?player=Mikael&date=1639587712
to my google spreadsheet. For some columns, like "Ehp", I can get the value just fine with
=VALUE(QUERY(TRANSPOSE(IMPORTJSON(C5)), "select Col2 where Col1 = 'Ehp'"))
where C5 is just a cell with the link above in it. But this doesn't seem to work for any column that has an underscore in it, like "Ehp_rank".
I should say I understand nothing about how any of this works, I'm doing everything by looking at examples I found, then just trial and error. Can someone help me import columns with an underscore?
Also, I'd like to import the "Username", but it's inside that info block and I can't seem to get it right. I've tried "info.Username", changing Col2/1 to Col3/2, but no sucess.
CodePudding user response:
In that case, how about directly preparing a script as follows?
Sample script:
Please copy and paste the following script to the script editor. And, please put a custom formula of =SAMPLE("https://templeosrs.com/api/player_stats.php?player=Mikael&date=1639587712")
. By this, in this case, 90
is returned.
function SAMPLE(url) {
const res = UrlFetchApp.fetch(url).getContentText();
const obj = JSON.parse(res);
return obj.data && obj.data.Ehp_rank ? obj.data.Ehp_rank : "";
}