Google Script for Conditional Formatting w/ multiple ranges and more than one condition

Question

Sample of the page I am using
I use a conditional format across my sheets but want to change it to Google Script instead.

For every check out date that does not have a Y, highlight yellow.

I managed to modify a script to highlight a cell based on two conditions for A:D but cannot figure out how to extend it to the other ranges in my sheet.

Here is what I have so far that I found :

function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A:D");
var values = range.getValues();

//for each row that data is present
for(var i = 0; i < values.length; i++) {
var cell = sheet.getRange(i + 1, 4);

  if(values[i][1] !== "") {


  if(values[i][3] === "") {
    cell.setBackground('yellow');
  } else {
    cell.setBackground(null);
  }

 } else {

  cell.setBackground(null);
}
}  
}

Links: Google App Script - Conditional Formatting Based on Another Cell

1. Share a sample sheet to have better chances to get a good answer. 2. What is the start and final result, any screenshots? Please help me to help you. — Jeff Rush, Jun 29 '20 at 10:43

Cooper · Answer 1 · 2020-06-29T18:50:46.277

Try this:

function onEdit(e) {
  //e.source.toast('Entry');
  var sh=e.range.getSheet();
  if(sh.getName()=="Sheet2") {
    //e.source.toast('Conditional');
    var vs=sh.getRange(1,1,sh.getLastRow(),4).getValues();
    vs.forEach(function(r,i){
      let rg=sh.getRange(i+1,4);
      if(r[1]=="") {
        if(r[3]=="") {
          //e.source.toast('yellow');
          rg.setBackground('#ffff00');
        }else{
          //e.source.toast('white');
          rg.setBackground('#ffffff');
        }
      }else{
        //e.source.toast('r[1] not null');
        rg.setBackground('#ffffff');
      }
    });
  }
}

I tested this a little and it seems to work

function onEdit(e) {
  //e.source.toast('Entry');
  var sh=e.range.getSheet();
  if(sh.getName()=="Sheet2") {
    //e.source.toast('Conditional');
    var v=sh.getRange(6,1,sh.getLastRow()-5,sh.getLastColumn()).getValues();
    var r0=sh.getRange(6,4,sh.getLastRow()-5,1);
    var c0=r0.getBackgrounds();
    var r1=sh.getRange(6,9,sh.getLastRow()-5,1);
    var c1=r1.getBackgrounds();
    var r2=sh.getRange(6,14,sh.getLastRow()-5,1);
    var c2=r2.getBackgrounds();
    var r3=sh.getRange(6,19,sh.getLastRow()-5,1);
    var c3=r3.getBackgrounds();
    var r4=sh.getRange(6,24,sh.getLastRow()-5,1);
    var c4=r4.getBackgrounds();
    v.forEach(function(r,i){
      c0[i][0]=(r[1]=="" && r[3]=="")?'#ffff00':'#ffffff';
      c1[i][0]=(r[6]=="" && r[8]=="")?'#ffff00':'#ffffff';
      c2[i][0]=(r[11]=="" && r[13]=="")?'#ffff00':'#ffffff';
      c3[i][0]=(r[16]=="" && r[18]=="")?'#ffff00':'#ffffff';
      c4[i][0]=(r[21]=="" && r[23]=="")?'#ffff00':'#ffffff';
    });
    r0.setBackgrounds(c0);
    r1.setBackgrounds(c1);
    r2.setBackgrounds(c2);
    r3.setBackgrounds(c3);
    r4.setBackgrounds(c4);
  }
}

Try this for the entire spreadsheet:

function onEdit(e) {
  //e.source.toast('Entry');
  var sh=e.range.getSheet();
  //e.source.toast('Conditional');
  var v=sh.getRange(6,1,sh.getLastRow()-5,sh.getLastColumn()).getValues();
  var r0=sh.getRange(6,4,sh.getLastRow()-5,1);
  var c0=r0.getBackgrounds();
  var r1=sh.getRange(6,9,sh.getLastRow()-5,1);
  var c1=r1.getBackgrounds();
  var r2=sh.getRange(6,14,sh.getLastRow()-5,1);
  var c2=r2.getBackgrounds();
  var r3=sh.getRange(6,19,sh.getLastRow()-5,1);
  var c3=r3.getBackgrounds();
  var r4=sh.getRange(6,24,sh.getLastRow()-5,1);
  var c4=r4.getBackgrounds();
  v.forEach(function(r,i){
    c0[i][0]=(r[1]=="" && r[3]=="")?'#ffff00':'#ffffff';
    c1[i][0]=(r[6]=="" && r[8]=="")?'#ffff00':'#ffffff';
    c2[i][0]=(r[11]=="" && r[13]=="")?'#ffff00':'#ffffff';
    c3[i][0]=(r[16]=="" && r[18]=="")?'#ffff00':'#ffffff';
    c4[i][0]=(r[21]=="" && r[23]=="")?'#ffff00':'#ffffff';
  });
  r0.setBackgrounds(c0);
  r1.setBackgrounds(c1);
  r2.setBackgrounds(c2);
  r3.setBackgrounds(c3);
  r4.setBackgrounds(c4);
}

Keep in mind if you have multiple people editing the sheet then you are going to be missing some edits because the script will not be able to keep up.

It's giving me the following code when I run it: TypeError: Cannot read property 'range' of undefined (line 3, file "Code") — Susie Power, Jun 28 '20 at 14:54
You can't run this from the script editor. It requires the onedit trigger event block. — Cooper, Jun 28 '20 at 15:05
Thanks. I apologize that I was not more clear in my post. I have re-edited it to explain the parameters I need across the page and included a snapshot of my page. — Susie Power, Jun 28 '20 at 15:22
I posted a pic in case you need a visual. It would be if G is not blank and I is blank then highlight, if L is not blank and N is blank then highlight, if Q is not blank and S is blank, then highlight and lastly, if V is not blank and X is blank then highlight. Hope that helps! — Susie Power, Jun 28 '20 at 19:45
That's it!! Is it possible to do this on the active sheet or the whole workbook without changing too much code? Thank you!!! — Susie Power, Jun 29 '20 at 18:37
It doesn't seem to be working on the other worksheets. Every worksheet has the exact same format but different worksheet name. — Susie Power, Jun 29 '20 at 18:45
It works on one sheet but if you want to remove that then it can work on the entire workbook maybe. I think the issue is that you have to realize that this function will be triggered on every edit of every sheet in the spreadsheet and it must finish within 30 seconds. The real problem is that if you have a lot of editors then some of the editors are going to be missed. — Cooper, Jun 29 '20 at 18:45
In answer to you question `var r0=sh.getRange(6,4,sh.getLastRow()-5,1);` the third parameter in getRange( is the number of rows. If your start startrow=6 then the third parameter should be sh.getLastRow()-startrow+1 or sh.getLastRow()-5 — Cooper, Aug 03 '20 at 20:29

Google Script for Conditional Formatting w/ multiple ranges and more than one condition

1 Answers1