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

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.

CodePudding user response：

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:

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])) {

CodePudding user response：

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");
    }
  });
}