Find all empty cells in several columns-CodePudding

Is there an easy way to color the rows containing empty cells in specific columns? So far, I only came up with a solution to highlight the cells themselves, but this script also takes some time to run. Will appreciate any advice or guidance!

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('FRs & NFRs')

 var columns = ['J17:J','L17:L', 'V17:V', 'AB17:AB','AI17:AI', 'AK17:AK'];

  for(var col in columns){ //loop to iterate the desired ranges

var range = sheet.getRange(columns[col]); 
//range.activate();

var values = range.getValues();
var formulas = range.getFormulas();

//for each row that data is present
for(var i = 0; i < values.length; i  ) { //loop to iterate the rows
  for( var j = 0; j< values[i].length ; j  ){   //loop to iterate the columns 
    var cell = range.getCell(i 1, j 1);    
    if ( values[i][j] == "" )
     {
       cell.setBackground('red');
       cell.activate
     }  
      else
     { cell.setBackground('white')   
  }

CodePudding user response：

Use ConditionalFormatRuleBuilder instead. This will create a Conditional format rule in your Sheet.

Try this.

Code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('FRs & NFRs');
  var ranges = sheet.getRangeList(['J17:J','L17:L', 'V17:V', 'AB17:AB','AI17:AI', 'AK17:AK']);
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellEmpty()
    .setBackground('red')
    .setRanges(ranges.getRanges())
    .build();


  var sheetRules = sheet.getConditionalFormatRules();
  sheetRules.push(rule);
  sheet.setConditionalFormatRules(sheetRules);
}

Output:

Conditional Formatting:

Highlighting empty cells using rangeList.setBackgroundColor(color)

In my code below, I collected the cell name of the empty cells and use it to create a RangeList. Then I used the RangeList to set the background to red. This reduced the Google Sheet Service calls from thousands to 11.

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp   65)   letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function highlightEmptyCell(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('FRs & NFRs')
  var rangeList = sheet.getRangeList(['J17:J','L17:L', 'V17:V', 'AB17:AB','AI17:AI', 'AK17:AK']);
  var rangeArray = rangeList.getRanges();
  var ranges = [];
  rangeArray.forEach(range =>{
    var data = range.getValues();
    for( var i = 0; i < data.length; i   ){
      for( var j = 0; j < data[0].length; j   ){
        if(data[i][j] == ""){
          var cellName = columnToLetter(range.getColumn())   (i   17)
          ranges.push(cellName);
        } 
      }
    }
  });

  sheet.getRangeList(ranges).setBackground("red");
}

Try this.

Reference: