Search code examples
google-apps-scriptcell

Check if cell is empty

As I'm new to Google Apps Script I'm struggling with the understanding of what and how thing done there. I want to change the color of a cell IF it is empty and two other cells in other columns are empty as well. IF one of them has value, that's OK and should left without any color.

In this screen shot, only rows 5, 6 and 7 need to be colored:

SO54747074 question example

Meaning the use of a script while running, to check where there is missing values on givens columns ( Email / Main Phone etc ).

Solution

  • try this:

    Your question doesn't specify which columns, so based upon the image I presume you want columns C,F and G. 

    function onEdit(e) {
  var dflt='#ffffff';
  var fill='#ffff00';
  if(e.range.getSheet().getName()!='Sheet69'){return;}//you need to add your sheet name so that this only works on the appropriate sheets
  var ss=e.source;
  var sh=e.range.getSheet();
  var rg=sh.getRange(1,1,sh.getMaxRows(),sh.getMaxColumns());
  var vA=rg.getValues();
  var bA=rg.getBackgrounds();
  for(var i=0;i<vA.length;i++) {
    if(!vA[i][2] && !vA[i][5] && !vA[i][6]){
      for(var j=0;j<bA[i].length;j++) {
        bA[i][j]=fill;
      }        
    }else{
      for(var j=0;j<bA[i].length;j++) {
        bA[i][j]=dflt;
      }     
    }
  }
  rg.setBackgrounds(bA);
}