1

Sorting out some of my trading cards into google sheets. I would like retrieve the average price of the card using yugiohprices api (https://yugiohprices.docs.apiary.io/#reference/checking-card-prices/check-price-for-cards-print-tag/check-price-for-card's-print-tag?console=1)

I can link the cell to retrieve the cards data. But I would only like to get the average price value

sample sheet

NAME, SET, AVERAGE_PRICE

JINZO PSV-000 =IMPORTDATA(CONCATENATE("https://yugiohprices.com/api/price_for_print_tag/",B2))

https://yugiohprices.com/api/price_for_print_tag/PSV-000

{"status":"success","data":{"name":"Blue-Eyes White Dragon","card_type":"monster","property":null,"family":"light","type":"Dragon / Normal","price_data":{"name":"Legend of Blue Eyes White Dragon","print_tag":"LOB-001","rarity":"Ultra Rare","price_data":{"status":"success","data":{"listings":[],"prices":{"high":125000.0,"low":22.22,"average":81.48,"shift":-0.281354736285059,"shift_3":-0.275475724702116,"shift_7":-0.302755433852473,"shift_21":-0.345173993409949,"shift_30":-0.302874743326489,"shift_90":0.0978172999191593,"shift_180":0.886984715145901,"shift_365":-0.525699982536818,"updated_at":"2022-04-11 13:55:38 -0600"}}}}}}
Pasta
  • 11
  • 2

1 Answers1

0

When you want to retrieve the value from JSON data, I thought that a custom function created by Google Apps Script might be useful.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And save the script. When you use this, please put =SAMPLE("https://yugiohprices.com/api/price_for_print_tag/PSV-000") to a cell. By this, the value of obj.data.price_data.price_data.data.prices.average is retrieved.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url);
  const obj = JSON.parse(res.getContentText());
  return obj.data.price_data.price_data.data.prices.average;
}

Testing:

When this script is run using =SAMPLE("https://yugiohprices.com/api/price_for_print_tag/PSV-000"), the following result is obtained.

enter image description here

Note:

  • If the above custom function doesn't work, please reopen Spreadsheet and test it again.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165