Importing API data via importJSON

Question

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

About My result is a truncated list that displays as such:, can you show your expected result in your question? — Tanaike
– Tanaike, Commented Sep 22, 2021 at 0:54
Thank you for replying. Now I notice that an answer has already been posted. I think that it will resolve your issue. — Tanaike
– Tanaike, Commented Sep 22, 2021 at 4:46

Mike Steelson · Accepted Answer · 2021-09-22 08:16:46Z

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

This worked perfectly, thank you. I have one more question that I edited my initial post with if you feel inclined to help.
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.
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 · Accepted Answer · 2021-09-22 08:27:03Z

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]};

Collectives™ on Stack Overflow

Importing API data via importJSON

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related