google-apps-script - Replace conditonal formating with script, moving rows creates a mess

Question

I have a set of sheets and move rows back and fourth multiple times. It makes a mess of the conditional formatting ranges. I have spent hours trying to solve the problem by moving rows differently with no luck. The only solution seems to be using script to handle the conditional formatting. I am not sure how to go about this.

I cant post images. Here are links to my current conditional formatting.

https://www.dropbox.com/s/7ptrwifdelzz7zk/Screen%20Shot%202019-08-24%20at%204.13.36%20PM.png?dl=0

https://www.dropbox.com/s/8wlsay0fclsoyil/Screen%20Shot%202019-08-24%20at%204.13.46%20PM.png?dl=0

https://www.dropbox.com/s/04hp2pyq7icdt56/Screen%20Shot%202019-08-24%20at%204.13.52%20PM.png?dl=0

https://www.dropbox.com/s/qozz3dxaephfymq/Screen%20Shot%202019-08-24%20at%204.13.59%20PM.png?dl=0

I would like the script to keep background green until 5 days before date in cell. Then turn yellow. Once it reaches date in cell it turns red and stays until the row is deleted.

标签： google-apps-script

Answer 1

尝试这个：

function bgControl() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var bA=rg.getBackgrounds();
  var tda=new Date();
  var tdav=new Date(tda.getFullYear(),tda.getMonth(),tda.getDate()).valueOf();
  var day=86400000;
  for(var i=0;i<vA.length;i++) {
    var did=tdav-new Date(vA[i][0]).valueOf();
    if(did>(5*day) ){
      sh.getRange(i+1,1,1,sh.getLastColumn()).setBackground('green');
    }
    if(did<=(5*day) && did>=0) {
      sh.getRange(i+1,1,1,sh.getLastColumn()).setBackground('yellow');
    }
    if(did<0) {
      sh.getRange(i+1,1,1,sh.getLastColumn()).setBackground('red');
    }
  } 
}

测试：

对于 AQ8：AQ

function bgControl() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getRange(8,43,sh.getLastRow(),1);//AQ8:AQ without all the nulls at the bottom
  var vA=rg.getValues();
  var bA=rg.getBackgrounds();
  var tda=new Date();
  var tdav=new Date(tda.getFullYear(),tda.getMonth(),tda.getDate()).valueOf();
  var day=86400000;
  for(var i=0;i<vA.length;i++) {
    var did=tdav-new Date(vA[i][0]).valueOf();
    var rng=sh.getRange(i+8,43,1,1);
    if(did>(5*day) ){
      rng.setBackground('green');
    }
    if(did<=(5*day) && did>=0) {
      rng.setBackground('yellow');
    }
    if(did<0) {
      rng.setBackground('red');
    }
  } 
}