Google Sheets Import XML Function error: Imported Xml content can not be parsed-CodePudding

I am trying to scrape data from crypto-exchanges. When i use the Import XML function in google sheets is gives the "Imported Xml content can not be parsed." error. The function I am using is

=IMPORTXML("https://www.binance.us/en/trade/pro/ADA_BTC","//*[@id=""__APP""]/div/div/div[6]/div/div/div/div[2]/div[1]/div/div[1])")

that is the price data i am trying to get into the sheet.

I have tried to look into different x paths but they all return Imported content is empty error. Not sure if i am using the right Xpath.

CodePudding user response：

the issue is with a site that uses JavaScript. google sheets IMPORT formulae do not support scraping of JS elements:

CodePudding user response：

To retrieve informations you need, you can parse the json provided by Binance at https://api1.binance.com/api/v3/ticker/24hr.

function binance(url = 'https://api1.binance.com/api/v3/ticker/24hr') {
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  data.forEach(function(d) {if (d.symbol == 'ADABTC') console.log(d.lastPrice)})
}

The other way is to use websockets at wss://stream.binance.com:9443/ws with this stream

{"method":"SUBSCRIBE","params":["adabtc@kline_15m","adabtc@kline_5m","adabtc@kline_1m"],"id":1}

you will recieve a json as follows

{"e":"kline","E":1649207018439,"s":"ADABTC","k":{"t":1649206800000,"T":1649207699999,"s":"ADABTC","i":"15m","f":106692274,"L":106692475,"o":"0.00002555","c":"0.00002551","h":"0.00002556","l":"0.00002550","v":"89755.10000000","n":202,"x":false,"q":"2.29071565","V":"27199.10000000","Q":"0.69455387","B":"0"}}