CODEWITHSUNDEEP
jsonxmlgoogle-sheetsweb-scrapinggoogle-sheets-formula
onit
onit

Reputation: 2372

Why is this Importxml formula not working?

The following formula does work for some, but not for others:

=IFNA(VALUE(IMPORTXML("https://finance.yahoo.com/quote/C2PU.SI", "//*[@class=""D(ib) Mend(20px)""]/span[1]")))

If used without IFNA, it says 'Resource at url not found'.

Here's the value I'm trying to pull in: enter image description here

I appreciate if you could point me to the right direction.

Thank you!

Upvotes: 0

Views: 269

Answers (3)

Mike Steelson
Mike Steelson

Reputation: 15308

The structure of the DOM is generated by javascript. Nevertheless, all informations you need are contained by a json string called here root.App.main. You can get all the data by these way

function extract(url){
  var source = UrlFetchApp.fetch(url).getContentText()
  return source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
}

and then retrieve the data by conventionnal json parsing. This will give you the value

[![function marketPrice() {
  var code = 'C2PU.SI'
  var url='https://finance.yahoo.com/quote/' + code
  var source = UrlFetchApp.fetch(url).getContentText()
  var jsonString = source.match(/(?<=root.App.main = ).*(?=}}}})/g) + '}}}}'
  var data = JSON.parse(jsonString)
  var regularMarketPrice = data.context.dispatcher.stores.StreamDataStore.quoteData.item(code).regularMarketPrice.raw
  Logger.log(regularMarketPrice)
}
Object.prototype.item=function(i){return this\[i\]};][1]][1]

enter image description here

Upvotes: 1

Wicket
Wicket

Reputation: 38140

Don't use the "inspect" tool as it will show the DOM as it's being rendered by the web browser including modifications to the source code by client-side JavaScript, instead look at the source code.

Resources

Upvotes: 1

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

It does not return any values even for simple importxml. It seems the site is generated by javascript or protected so it can't be scraped by importxml.

Upvotes: 1

Related Questions