1

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:

2 6 8
/avgLowPrice 162 180261 192880
/avgHighPrice 166 182132 195209

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

ID Name avgHighPrice avgLowPrice
2 Cannonball 180261 192880
6 Cannon Base 182132 195209
user3831119
  • 23
  • 4

2 Answers2

1

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

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • This worked perfectly, thank you. I have one more question that I edited my initial post with if you feel inclined to help. – user3831119 Sep 22 '21 at 04:57
  • No problem, I will have a look at it. – Mike Steelson Sep 22 '21 at 06:24
  • The structure of `mapping` is completely different. I updated my answer. What I don't understand is the relationship between the two URLs. I will take a closer look at Old School RuneScape site and API. – Mike Steelson Sep 22 '21 at 07:29
  • ok, I have understood the link between id and # ... I have changed the script so that the ID will be in string format. – Mike Steelson Sep 22 '21 at 08:16
0

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]};
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20