Having a bit of trouble using importJSON for the first time in Google Sheets. My data is importing as truncated and I can't find any way to really filter things the way I'd like.

API source: https://prices.runescape.wiki/api/v1/osrs/1h

I'm using the following command: =IMPORTJSON(B1;B2) where B1 is the source link, and B2 references any filters I've applied. So far I have no filters.

My result is a truncated list that displays as such:

data/2/avgHighPrice 166
data/2/highPriceVolume  798801
data/2/avgLowPrice  162
data/2/lowPriceVolume   561908
data/6/avgHighPrice 182132
data/6/highPriceVolume  7
data/6/avgLowPrice  180261
data/6/lowPriceVolume   37
data/8/avgHighPrice 195209
data/8/highPriceVolume  4
data/8/avgLowPrice  192880
data/8/lowPriceVolume   40

In the examples I've seen and worked with (primarily the example provided by the Addon), it will naturally pivot into a table. I can't even achieve that, which would be workable although I'm really only looking to ping the markers avgHighPrice and avgLowPrice.

EDIT:

I'm looking for results along the lines of this:

EDIT2:

So I have one more thing I was hoping to figure out. Using your script, I created another script to pull the names and item IDs

function naming(url){
  //var url='https://prices.runescape.wiki/api/v1/osrs/mapping'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(['#','id','name'])
  for (let p in eval('data.data')) {
    try{result.push([p,data.item(p).ID,data.item(p).Name])}catch(e){}
  }
  return result
}
Object.prototype.item=function(i){return this[i]};

I'm wondering if it is possible to correlate the item Name with the Item ID from the initial pricing script. To start, the 1st script only list items that are tradeable, while the 2nd list ALL item IDs in the game. I'd essentially like to correlate the 1st and 2nd script to show as such

CodePudding user response：

Try this script (without any addon)

function prices(url){
  //var url='https://prices.runescape.wiki/api/v1/osrs/1h'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(['#','avgHighPrice','avgLowPrice'])
  for (let p in eval('data.data')) {
    try{result.push([p,data.data.item(p).avgHighPrice,data.data.item(p).avgLowPrice])}catch(e){}
  }
  return result
}
Object.prototype.item=function(i){return this[i]};

You can retrieve informations for naming / from mapping as follows

function naming(url){
  //var url='https://prices.runescape.wiki/api/v1/osrs/mapping'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(["id","name","examine","members","lowalch","limit","value","highalch"])
  json=eval('data')
  json.forEach(function(elem){
    result.push([elem.id.toString(),elem.name,elem.examine,elem.members,elem.lowalch,elem.limit,elem.value,elem.highalch])
  })
  return result
}

https://docs.google.com/spreadsheets/d/1HddcbLchYqwnsxKFT2tI4GFytL-LINA-3o9J3fvEPpE/copy

CodePudding user response：

Integrated function

=pricesV2()

https://docs.google.com/spreadsheets/d/1HddcbLchYqwnsxKFT2tI4GFytL-LINA-3o9J3fvEPpE/copy

function pricesV2(){
  var url='https://prices.runescape.wiki/api/v1/osrs/mapping'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  let myItems = new Map()
  json=eval('data')
  json.forEach(function(elem){myItems.set(elem.id.toString(),elem.name)})
  var url='https://prices.runescape.wiki/api/v1/osrs/1h'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(['#','name','avgHighPrice','avgLowPrice'])
  for (let p in eval('data.data')) {
    try{result.push([p,myItems.get(p),data.data.item(p).avgHighPrice,data.data.item(p).avgLowPrice])}catch(e){}
  }
  return result
}
Object.prototype.item=function(i){return this[i]};