CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Parseltongue
Parseltongue

Reputation: 11697

Google Apps Script Spreadsheets: Color whole row red if cell value is not contained in another column

I have a Google Workbook with two spreadsheets. In the first spreadsheet, people will be adding various IDs to Column B, and I want to ensure that the IDs in Column B are contained within a list of valid IDs contained in Column A of the second worksheet.

If the ID in Column B of the first worksheet is invalid, color the whole row red.

This is my current working code, but it operates slowly-- I think because there are a lot of valid IDs on the second spreadsheet (~5400). In addition, if a row is deleted, I want to uncolor the row.

Is there a better way of doing this:

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveCell();
  var activeRange = s.getActiveRange();

  var changeRange = s.getRange(activeRange.getRow(),1,1,s.getLastColumn());


    if (r.getColumn() == 2 && r.getValue() == "") {
      changeRange.setBackground("none");
     }

    if (r.getColumn() == 2 && findRow(r.getValue()) !=1) {
      changeRange.setBackground("red");
    }
  }


function findRow(item) {;// the actual search function
        var resultArray = []
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet=ss.getSheets()[1];
        var values = sheet.getRange("A:A").getValues(); 
        for(cc =0; cc < values.length; ++cc) {
            if(values[cc].toString().match(item)){
                return 1;
             }

        }
}

Upvotes: 0

Views: 2912

Answers (2)

Sam Scholefield
Sam Scholefield

Reputation: 1250

Rather than cycling through comparing values using a script you could use an additional 'helper' column in your spreadsheet (which can be hidden if you need to maintain a 'clean sheet' for your end users) that by formula creates the valid/invalid signifier for your ID.

=IF(ISERROR(VLOOKUP(B1,Sheet2!A:A,1,FALSE)),"invalid","valid")

The formula above looks for the value in cell B1 (your id) and then tries to locate that value in column A of sheet 2, if it fails it will return 'invalid', if it succeeds it will return 'valid'. You can use onEdit to insert the formula for any new rows (or check to see if it has been inserted already).

Using this method you can then create an array of the valid/invalid values (one call) and set the relevant background colour for every row using its index in the array.

var validArray = sheet.getRange(yourFormulaRange);

for(var i = 0; var i < validArray; i++){
   if(validArray[i] = "invalid"){
     sheet.getRange(i, sheet.getLastColumn(), 1).setBackground("red");
   }else{
     sheet.getRange(i, sheet.getLastColumn(), 1).setBackground("white");
}

This approach should be much faster, although I would recommend converting the formulas to values after a period of time (archiving?).

Upvotes: 0

wchiquito
wchiquito

Reputation: 16551

When you say that your code operates slowly. How long approximately?

The following code, perhaps something can help.

function onEdit(e) {
  var ss = e.source,
      s = ss.getActiveSheet(),
      sName = s.getName(),
      range = e.range,
      sheet1 = 'Sheet1',
      sheet2 = 'Sheet2',
      value,
      changeRange;
  if (sName === sheet1 && range.getColumn() === 2) {
    value = e.value, changeRange = getChangeRange_(s, range);
    switch(true) {
      case !value:
      case findRow_(ss, value, sheet2):
        changeRange_(changeRange, 'none');
        break;
      default:
        changeRange_(changeRange, 'red');
    }
  }
}

function getChangeRange_(s, range) {
  return s.getRange(range.getRow(), 1, 1, s.getLastColumn() || range.getColumn());
}

function changeRange_(range, color) {
  range.setBackground(color);
}

function findRow_(ss, findValue, sheet2) {
  var arr = getList_(ss, sheet2), len = arr.length;
  while (len--) if (arr[len][0] === findValue) return true;
  return false;
}

function getList_(ss, sheet2) {
  return ss.getSheetByName(sheet2).getRange('A:A').getValues();
}

Upvotes: 1

Related Questions