How do I use a wildcard in Binance API to return all quote assets for a particular base asset

Question

I'm using Binance to Google Sheets which is coded in Google Apps Script to get data from Binance API into a Google Sheet. (https://github.com/diegomanuel/binance-to-google-sheets)

If I use the following formula in Google Sheets I get a list of all trading pairs and their respective prices:

=BINANCE("prices")

If I use the following formula I get only the price of BTC (quote asset) in USDT (base asset):

=BINANCE("prices", "BTC", "USDT")

I would like to use wildcards to get all quote assets traded against a particular base asset. The following code did not work for me because it does not filter against USDT but brings back all trading pairs:

=BINANCE("prices", ""*"", "USDT")

I would also like to use wildcards to get all base assets traded against a particular quote asset. The following code only returned an error for me:

=BINANCE("prices", "USDT", ""*"")

I would be grateful if anybody could help me find a solution.

Answer 1

If I well understand your request, try

=query(getDataJSON("https://api1.binance.com/api/v3/ticker/24hr",A1:E1),"where Col1 like '%USDT' ")

with this layout

and the following script

function getDataJSON(url, xpath) {
  let resultat = [];
  function getData(elem, list) {
    var prov = []
    list.forEach(function (path) { prov.push(elem[path]) })
    resultat.push(prov)
  }
  try {
    var json = JSON.parse(UrlFetchApp.fetch(url).getContentText())
    var list = xpath.join().split(",")
    if (json.length) { json.forEach(function (elem) { getData(elem, list) }) } else { getData(json, list) }
    return resultat
  }
  catch (e) {
    return ('No data !');
  }
}

you can add a dummy parameter in getDataJSON in third position (as a checkbox) to refresh.