google sheet importxml returns error: imported content is empty

0
votes

I am attempting to import data from website using the google sheet importxml function:

=IMPORTXML("https://decksofkeyforge.com/decks?title=Rych%C5%82y%20Mag","//div[@class='MuiTypography-root MuiTypography-h3']")

I am trying to get the sas raitng, one number circled on the image: 54

Unfortunetely, the function returns error. It is my first time using it and I would like to ask if i am doing something wrong. Thanks!

2
xpathgoogle-sheetsgoogle-sheets-importxml

2 Answers

1
votes

I believe you won't have any success, as the site you're trying to pull content from needs javascript to build - thus the node will probably not be accessible to IMPORTXML...
Try just pulling =IMPORTXML("https://decksofkeyforge.com/decks?title=Rych%C5%82y%20Mag","//body") and all you'll get will be a script tag and warning that you need to have JS enabled for the site to work.

Other than that, I can tell you that you have a slip up in your XPATH string, because you're trying to select a h3 node, not div :)

0
votes

Javascript is required to display the data. But you can get it from the JSON loaded in the background.

The easiest way to do it (other option is to write a GoogleAppScript to POST a request and retrieve the JSON) is to use IMPORTDATA, QUERY and REGEXEXTRACT formulas.

First, you have to gather the urls returned by the search engine. Those who contain the id of each deck (not the ones containing decks?title=). With your example : https://decksofkeyforge.com/decks/111a6473-83cb-4b13-aa65-fbfb8ca7af37.

Once you get them, you have to transform them with the following formula. In C3 :

=SUBSTITUTE(B3;"m/decks";"m/api/decks/with-synergies")

Then, use the following formula to extract the sasRating. In D3 :

=REGEXEXTRACT(QUERY(TRANSPOSE(IMPORTDATA(C3));"select * WHERE Col1 starts with 'sasRating'";0);"\d+")*1

Output :

SasRating