Google Sheets ImportXML issues

0
votes

I have a google sheet that I'm trying to automate as much as possible for my WoW Raid group. What I'm trying to do here is parse some data from WoW's armory to automatically pull a persons item level.

I am having issues pulling from WoW's website directly (https://worldofwarcraft.com/en-us/character/us/sargeras/Beansy), but I can pull the item level from another site (https://raider.io/characters/us/sargeras/beansy). The only difference I can spot is that one site I can pull from a [ /div/span/b clas"text-white" ] and from WoW the information is directly in [ /div/class="media-text" ]

WOW Formula =IMPORTXML(C32,"//*[@id='character-profile-mount']/div/div/div[2]/div/div[1]/div[1]/div/div[2]/div[1]/a[1]/div/div[2]")
Raider IO Formula =IMPORTXML(C31,"//*[@id='content']/div/div/div/div[2]/div[1]/div[1]/section/div/div[1]/div/span/b")

WOW Inspect Element <div class="Media-text">184 ilvl</div>
Raider IO Inspect Element <b class="text-white">184</b>

Above are the respective formula's and elements I've used. Raider IO's pulls properly and outputs 184 as it's information. However WoW's does not pull properly and outputs N/A Google Sheets Output Screencap

Does anyone have any ideas on why this might be happening?

Thanks in advance!

1
google-sheets-formula

1 Answers

0
votes

I think that the https://worldofwarcraft.com/en-us/character/us/sargeras/Beansy prepares the values using Javascript. For example, when the HTML without using Javascript is retrieved from this URL, Media-text cannot be found in the retrieved HTML. On the other hand, https://raider.io/characters/us/sargeras/beansy has the values in the HTML without using Javascript. I thought that the difference is due to this.

But in order to retrieve the value of 184 from URL of the former, when I saw the HTML without using Javascript, I noticed that the value is included in the metadata. So when the value of 184 is retrieved from the metadata, the sample formula is as follows.

Sample formula:

=REGEXEXTRACT(IMPORTXML(A1,"//meta[@name='description']/@content"),"(\d+) ilvl")
  • In this formula, the URL of https://worldofwarcraft.com/en-us/character/us/sargeras/Beansy is put to the cell "A1".

Result:

enter image description here

Also, as additional modification, about your =IMPORTXML(URL,"//*[@id='content']/div/div/div/div[2]/div[1]/div[1]/section/div/div[1]/div/span/b"), in this case, the xpath might be able to be modified simple a little as follows.

Modified formula:

=IMPORTXML(A1,"//span[contains(text(),'Item Level')]/b[@class='text-white']")
  • In this formula, the URL of https://raider.io/characters/us/sargeras/beansy is put to the cell "A1".

Result:

enter image description here

References: