Google Sheet how to determine if a cell has an image and if not insert my image

Question

So i'm using importxml to get images from a website. Since image links in this website are truncated i'm successfully using this method:

• In cell A1 i put main domain link : https://imagedomain.com/
• In cell A2 i pull truncated image links with this formula:

---

=TRANSPOSE(query(importxml(B1 **(here image page link)** ;"
(/a/img/@src) | 
(/a/img/@src) | 
(/a/img/@src) ")))

(I import many images but in this example concentrating just in one cell and trucated links just show like text in cell similar to this: img/1234.jpg)

• In cell A3 i united A1 and A2 with this formula:

---

 =(A1)&(A2)

(This unites main domain with truncated image link and gives this: https://imagedomain.com/img/1234.jpg)

• In cell A4 (last step) i show image with this formula:

---

=image(A3)

Now my problem is that sometime imported truncated link in A2 does not exist because that website offers no image but i still have a formula and link in A4. This is a problem because i can't use if blank to replace with one of my images.

My question is if there is any formula detecting if image shows and if not to insert my own image. I don't want to use any script.

I tried to explore if @alt because that website offers alt for every image but i don't know exactly how to do that. So my idea was if link + alt then verifies that there is image but if no alt then no image imported and then insert my image. This is just my idea how this could be solved ONLY with formula but maybe there are other and easier ways. Thnx

Answer 1

this may work:

=(A3<>"")*(NOT(ISTEXT(A3)))*(NOT(ISNUMBER(A3)))=1

---

but note that you would need to use all possible states to really make sure ...so:

=ISNONTEXT(A3)*
 (NOT(ISBLANK(A3)))*
 (NOT(ISNUMBER(A3*1)))*
 (NOT(ISERROR(A3)))*
 (NOT(ISTEXT(A3)))*
 (NOT(ISFORMULA(A3)))=1

---

UPDATE:

try:

=INDEX(IF(COUNTIF(QUERY(LOWER(IMPORTXML($A$1; "//img/@alt")); 
 "where not Col1 matches '.*calendario.*|.*commenta.*'"; 0); 
 REGEXEXTRACT(LOWER(C3); "\d+ (.*) \("))*1=0; 
 IMAGE("https://i.imgur.com/RkXY3DB.jpg"); 
 IMAGE("https://tv.zam.it/"&QUERY(QUERY({LOWER(
 IMPORTXML($A$1; "//img/@alt"))\ IMPORTXML($A$1; "//img[@alt]/@src")}; 
 "where not Col1 matches '.*calendario.*|.*commenta.*'"; 0); 
 "select Col2 where Col1 contains '"&
 REGEXEXTRACT(LOWER(C3); "\d+ (.*) \(")&"'"; 0))))

spreadsheet demo