Google-sheets – Count cells with or without image in Google Sheets

google sheets

Google Spreadsheets supports an IMAGE function to include a graphic inside a cell. If the URL provided does not yield a valid picture resource, e.g. the server returns a 404 response, the cell will look empty and not throw any # error. Alas, neither of COUNT, COUNTA, COUNTBLANK and COUNTUNIQUE treats these visually empty cells any different than those that successfully show an image (and neither do the respective IS... functions). There also seems to be no respective expression that one could supply as a second parameter to COUNTIF(S).

Is there any way to count all cells of a region that either contain or do not contain an image? If not, should this be considered a bug and be reported accordingly? Where?

Best Answer

The following piece of code will try and fetch an URL. If possible (200) then 1, else 0 (404 or else).

Code

/**
 * Count the number of urls that throw a 200 error or not
 * @param  {Array.Array} range data range
 * @param  {int} startcol The column of the range
 * @param  {int} startrow The first row of the range
 * @param {(boolean)} boolean optional, but defaults to true
 * @return The summation of the input range
 * @customfunction
 */
function imageCount(range, startcol, startrow, boolean) {
  boolean = typeof boolean == 'undefined' ? true : boolean;  
  var col_id = String.fromCharCode(64 + startcol), range_string = col_id + startrow;
  if(!!range == true) { 
    var endrow = startrow + range.length - 1;
    range_string = range_string + ":" + col_id + endrow; 
  }  
  var formulaRange = SpreadsheetApp.getActive().getRange(range_string);
  var sum = formulaRange.getFormulas().map ( function (formula) {
    return formula[0] ? formula[0].split(',')[0].split('(')[1].split('"')[1] : null;
  }).map ( function (url) {
    try {
      UrlFetchApp.fetch(url);
      return 1;
    } catch(e) {
      return 0;
    }
  }).reduce ( function (a, b) {
    return a + b;
  }, 0);
  return boolean == true ? sum : (!!range == true ? range.length - sum : (sum === 1 ? 0 : 1));  
}

Screenshot

enter image description here

Note

Paste this code into the script editor (Tools > Script editor...) and press the bug button to authenticate the script. After that, you are able the use the custom formula.

Related Topic