Home > Software engineering >  Get Image URL Using IMPORTXML Google Sheets
Get Image URL Using IMPORTXML Google Sheets

Time:02-22

I'm not sure if it's just me but I'm not able to get the URL link on a product page using IMPORTXML in google sheets.

Here is the Formula:

=IMPORTXML("https://monocle.com/shop/product/1748819/issue-135/","//*[@id='content']/div/section/section[1]/div/div/div/picture/img/@src")

I've also tried

=IMPORTXML("https://monocle.com/shop/product/1748819/issue-135/","//html/body/div[1]/div/section/section[1]/div/div/div/picture/img/@src")

Just keep saying: "Error Imported content is empty."

Thanks in advanced for the help,

B

CodePudding user response:

It's not possible to get it from the /picture/img/@src because that element is generated dynamically. That's a limitation of importing XML.

You need to right-click on the website and then View Source to get the elements that are rendered.

Fortunately, there's a script tag where the info is displayed by using:

=IMPORTXML("https://monocle.com/shop/product/1748819/issue-135/","//section[@class='shop-product']//script")

I get as a response:

" var _learnq = _learnq || []; var item = { ""ProductName"": ""Issue 135"", 
""ProductID"": ""1748819"", ""SKU"": """", ""Categories"": [""print"", ""magazines""], 
""ImageURL"": 
""//img.monocle.com/product/monocle_135-5ef4abaee51e9.jpg?g=center&q=50"", 
""URL"": ""https://monocle.com/shop/product/1748819/issue-135/"", ""Brand"": """", 
""Price"": 20, ""CompareAtPrice"": 20 }; _learnq.push([""track"", ""Viewed 
Product"", item]); "

The Image URL is displayed. I'm thinking about a REGEXEXTRACT function to get the ImageURL value, but that's outside of my knowledge. But as soon as you get the ImageURL, you can use it in Sheets as

=IMAGE("https://img.monocle.com/product/monocle_135-5ef4abaee51e9.jpg")

CodePudding user response:

In your situation, for example, how about retrieving the URL from the meta tag as follows?

Sample formula:

=IMPORTXML(A1,"//meta[@property='og:image']/@content")
  • The URL of https://monocle.com/shop/product/1748819/issue-135/ is put in the cell "A1".

  • In this case, https://img.monocle.com/product/monocle_135-5ef4abaee51e9.jpg?w=760&h=570 is retrieved. When you want to retrieve https://img.monocle.com/product/monocle_135-5ef4abaee51e9.jpg. Please use the following sample formula.

      =REGEXREPLACE(IMPORTXML(A1,"//meta[@property='og:image']/@content"),"\?. ","")
    

Result:

enter image description here

Note:

  • This sample formula is for your showing URL of https://monocle.com/shop/product/1748819/issue-135/. So when you changed the URL, it might not be able to be used. Please be careful about this.

Reference:

  • Related