IMPORTJSON to google sheets with an underscore (_) in the column's name-CodePudding

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 : "";
}

Reference:

fetch(url)