Highlight a label sum (Col#) cell if the column it queried had blanks in it?

Question

So I have a query function:

=IFERROR(QUERY(IMPORTRANGE(Index!$C9,"report.csv!A1:Z1000"),"Select sum(Col7) where Col2 contains 'ADC' label sum(Col7)''",0))

And what I want to do is add one more thing to this but I don't know how. I want to make it so that the cell that has this formula is highlighted if the column (in another spreadsheet) that is referenced and summed (in this case Col7) has any blank cells in it if the corresponding Col2 does have a value in it.

So basically:

**Col 2**     **Col 7** 
  ADC            4.5
  ADC 
  ADC            6.2

I would want the cell that queried the sum of Col7 (in another spreadsheet) in this example to highlight itself yellow to alert me that one of the cells in the column (7) it was summing was blank. I have an idea of how to do this in a normal cell, but I have no idea how to integrate this with SQL language of query to do this. Any help would be appreciated. Thank you

Cooper Cooper · Accepted Answer · 2019-12-09T20:06:45

function runOne() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var cell=sh.getActiveRange();
  var blankfound=false;
  var rg7=sh.getRange(2,7,sh.getLastRow()-1,1);
  var col7=rg7.getValues();
  var rg2=sh.getRange(2,2,sh.getLastRow()-1,1);
  var col2=rg2.getValues();
  var sum=0;
  col2.map(function(r,i){if(r[0]=='ADC'){if(col7[i][0]){sum+=col7[i][0];}else{blankfound=true;}}});
  if(blankfound){cell.setBackground('#ffff00');}else{cell.setBackground('#ffffff');}
  cell.setValue(sum);
}

Highlight a label sum (Col#) cell if the column it queried had blanks in it?

1 Answers