3 states of row highlighting in Google Sheets in Apps Script

user_13960452 0
Viewed 617

Having a problem getting this to work properly. Any help would be amazing. Thank you. :)

Description:

  • If all Columns A:G have text, turn the row Red
  • If Columns A:G have text and theTracking # cell has text, turn the row Green Else the row should be white. (If no fill, partial fill or Tracking # has text, but A:G are incomplete.)

Here is a link to the sheet: https://docs.google.com/spreadsheets/d/1uznY7nxhdev7TCLwB3HAE5GxE5qq4sE3C8YyvVfUEdI/edit#gid=0

Here is the script I am trying to use:

//Sets the row color depending on the value in the "Tracking #" column.
function onEdit() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var trackingColumnOffset = getTrackingColumnOffset();
  var currentUser = Session.getActiveUser().getEmail();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    
    rowRange = range.offset(i, 0, 1);
    
    trackingNumber = rowRange.offset(0, trackingColumnOffset).getValue();
    
    if (trackingNumber == '') {
      
      rowRange.clearFormat();
      rowRange.setFontSize(14);
      
      
      // check to see whether other
      // required columns are populated
      if (isPhase1Satisfied()) {
        // set background color to red
        rowRange.setBackgroundColor("#F4CCCC");
      }
      else
      {
        // set background color to white
        // this means that both required columns
        // and tracking number is not fully populated
        rowRange.setBackgroundColor("#FFFFFF");
      }
      
    }
    else {
      
      if (isPhase1Satisfied()){  
        // set background color to green
        // i.e. Phase 2 satisfied
        rowRange.setBackgroundColor("#D9EBD3");        
      }
      else{
      }
         // set background color to red
        rowRange.setBackgroundColor("#D9EBD3"); //#F4CCCC
    } 
  }
}

//Returns the offset value of the column titled "Tracking #"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getTrackingColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Tracking #") {
      return i;
    } 
  }
}

// checks to see whether columns
// A-P have been filled in.
// This is the Phase1 check resulting in
// background color being set to Red if
// satisfied
function isPhase1Satisfied() {

  var result = true;
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeCell = activeSheet.getActiveCell();
  var range = activeSheet.getRange(activeCell.getRow(), activeCell.getColumn());
  
  // loop through each column to check to
  // see if it contains a value
  for (var i = 0; i < range.getLastColumn(); i++){
    if (range.offset(0, i).getValue() == ""){
      // empty cell value found
      // so set the return value to 
      // false
      result = false;
    }
  }
  return result;
}
google-apps-script google-sheets
2 Answers

I believe your goal as follows.

  • You want to change the background color of the columns "A" to "H" to #D9EBD3 when the row have all values for the columns.
  • You want to change the background color of the columns "A" to "G" to #F4CCCC when the row have all values for the columns "A" to "G" while the column "H" is the empty.
  • You want to change the background color to #FFFFFF for the cases except for above situations.

Modification points:

  • In your script, getValue and setBackgroundColor are used in the loop. In this case, the process cost will become high.
  • In this modification, I would like to propose the following flow.
  1. Retrieve values from the Spreadsheet.
  2. Create an array including the color codes.
  3. Set the background colors using the created array.

Pattern 1:

In this pattern, the simple trigger of onEdit is used. When you run the script, please edit the cells of the columns "A" to "H" on "Sheet1".

Sample script:

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();
  if (s.getSheetName() == "Sheet1" && r.getColumn() >= 1 && r.getColumn() <= 8 && r.getRow() > 1) {
    s.getDataRange().setBackground(null);  // I'm not sure whether you want this line.
    
    // 1. Retrieve values from the Spreadsheet.
    var range = s.getRange("A2:H" + s.getLastRow());
    var values = range.getValues();
    
    // 2. Create an array including the color codes.
    var colors = values.map(r => {
      var length = r.length;
      if (r.every(e => e.toString() != "")) {
        return Array(length).fill("#D9EBD3");
      } else if (r.splice(0, 7).every(e => e.toString() != "")) {
        return Array(length - 1).fill("#F4CCCC").concat("#FFFFFF");
      }
      return Array(length).fill("#FFFFFF");
    });
    
    // 3. Set the background colors using the created array.
    range.setBackgrounds(colors);
  }
}

Pattern 2:

In this pattern, the simple trigger of onEdit is NOT used. When you run the script, please run it at the script editor.

Sample script:

function sample() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getDataRange().setBackground(null);  // I'm not sure whether you want this line.
  
  // 1. Retrieve values from the Spreadsheet.
  var range = sheet.getRange("A2:H" + sheet.getLastRow());
  var values = range.getValues();
  
  // 2. Create an array including the color codes.
  var colors = values.map(r => {
    var length = r.length;
    if (r.every(e => e.toString() != "")) {
      return Array(length).fill("#D9EBD3");
    } else if (r.splice(0, 7).every(e => e.toString() != "")) {
      return Array(length - 1).fill("#F4CCCC").concat("#FFFFFF");
    }
    return Array(length).fill("#FFFFFF");
  });
  
  // 3. Set the background colors using the created array.
  range.setBackgrounds(colors);
}

Note:

  • Please use this script with V8.

References:

user_71086530

I think this is what you want:

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()!='Your Sheet Name')return;
  const idx={};
  sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0].forEach(function(h,i){idx[h]=i;});
  const v=sh.getRange(e.range.rowStart,1,sh.getLastRow(),sh.getLastColumn()).getValues();
  var currentUser = Session.getActiveUser().getEmail();
  v.forEach(function(r,i){
    let rowRange=a=sh.getRange(i+e.range.rowStart,1,1,sh.getLastColumn());
    let trackingNumber=r[idx['Tracking #']];
    if(trackingNumber=='') {
      rowRange.clearFormat();
      rowRange.setFontSize(14);
      if(r.length==r.filter(function(e){return e;}).length) {
        rowRange.setBackgroundColor("#F4CCCC");
      }else{
        rowRange.setBackgroundColor("#FFFFFF");
      }
    }else{
      if(r.length==r.filter(function(e){return e;}).length) {
        rowRange.setBackgroundColor("#D9EBD3");
      }else{
        rowRange.setBackgroundColor("#D9EBD3"); 
      }
    }
  }); 
}
user_72150910
Related