CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

How to check whether url used in IMAGE formula was correct and image is displayed?

I have a list of images I reference using their filenames:

=image("domainname.com/somepath/"&A2&".jpg")

where in A2 is a filename.

Unfortunately some of these images have .png extension. I try to find a solution to check if file with jpg extension is correct and image is found. If not found I want to use

 =image("domainname.com/somepath/"&a2&".png")

For this cell.

IFNA and IFERROR formulas do not work. I tried also to concatenate formulas:

=image("domainname.com/somepath/"&a2&".png")&image("domainname.com/somepath/"&a2&".jpg")

but I see that you can use & only for strings

There are thousands of images in different folders for reference and I cannot control their format. Do you have any idea?

Upvotes: 1

Views: 1112

Answers (1)

Kristkun
Kristkun

Reputation: 5963

One workaround I would suggest is to create and use a custom function to validate the image URL.

Custom Functions

  • Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these aren’t enough for your needs, you can use Google Apps Script to write custom functions — say, to convert meters to miles or fetch live content from the Internet — then use them in Google Sheets just like a built-in function.

Sample Custom Function:

/**
 * validate image
 *
 * @param {input} image url to validate.
 *
 * @customfunction
 */
function IMAGE_TRY(input) {
  
  try{
      var response = UrlFetchApp.fetch(input);
      return 1;
  } catch(err) {
    
      return 0;
  }

}
  • This custom function will take 1 image URL argument and verify if that URL is valid using the try-catch method. The function will return 1 when the URL is valid and 0 if not a valid URL

Note that the @customfunction tag is important in the function comment so that this custom function will appear as a suggested formula when you are typing in a cell.

Once you have created a custom function, you can use this to validate the URL and use the correct image URL using IF function.

Sample:

=IF(IMAGE_TRY("https://www.google.com/images/branding/googlelogo/2x/googlelogo_color_160x56dp.jpg"),image("https://www.google.com/images/branding/googlelogo/2x/googlelogo_color_160x56dp.jpg"), image("https://www.google.com/images/branding/googlelogo/2x/googlelogo_color_160x56dp.png"))

Sample Output:

enter image description here

(UPDATE)

OPTIMIZED SOLUTION:

Sample Custom Function:

/**
 * get image url with correct file extension.
 *
 * @param {input} image url string without extension.
 *
 * @customfunction
 */
function getURL(input) {
  
  try{
      var response = UrlFetchApp.fetch(input+".jpg");
       return (input.concat(".jpg"));
  } catch(err) {
    
      return (input.concat(".png"));
  }

}
  • This solution accepts an image URL without file extension and return an image URL with the valid file extension.

Sheets Formula:

=image(getURL("https://www.google.com/images/branding/googlelogo/2x/googlelogo_color_160x56dp"))

Upvotes: 2

Related Questions