How to go over a 2 columns and set the colour of a cell under a condition

user_15655973 0
Viewed 39

Im trying to go over 2 column and set the colour of them into red if they are not equal to specefic string, but its not working with me.

Here is my code.

function setCellColors() {
  //Get the sheet you want to work with. 
   const thisSheet = SpreadsheetApp.openByUrl(THIS).getSheetByName('Project');
   var range = thisSheet.getRange("V3:W").getValues();
   var newData = range;
   for (var i in newData) {
  if(newData!== "There is no NCR" && newData !=="N/A" && newData!=="There is no Reason Codes"){
  newData.setBackground("red");
  } 
  }
}

I,m keep getting this error "TypeError: newData.setBackground is not a function" which I think because newData ia=s an array but how would I change it to string in that case, and is the rest of the code right?

If you need more information please let me know

Thank you.

google-apps-script google-sheets javascript
2 Answers

Set selected backgrounds to red

function setCellColors() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet(Project);
  const tA = ["There is no NCR", "N/A", "There is no Reason Codes"];
  const vs = sh.getRange(3, 22, sh.getLastRow() - 2, 1).getValues();
  const newData = vs;
  vs.forEach((r, i) => {
    if (!~tA.indexOf(r[0])) {
      sh.getRange(i + 3, 22).setBackground("red");
    }
  });
}
user_72150910

It's not quite clear what of the columns should or should not contain one of the texts. I suppose it's the column 'V'. It this case you can try this code:

function setCellColors() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var texts = ['There is no NCR', 'N/A', 'There is no Reason Codes'];
  var range = sheet.getRange('V3:W');
  var values = range.getValues();
  var backgrounds = range.getBackgrounds();

  for (var row in values) {
    if (texts.includes(values[row][0])) {
      backgrounds[row][0] = 'red';
      backgrounds[row][1] = 'red';
    }
  }

  range.setBackgrounds(backgrounds);
}

The result:

enter image description here

If you want to check both of the columns you can change this line:

if (texts.includes(values[row][0])) {

with this:

if (texts.includes(values[row][0]) || texts.includes(values[row][1])) {
user_142654690
Related