I am looking to find if non-consecutive dynamic values are found in another sheet B. If so, I want to format those values in Sheet A.

The problem with my code is that it is going through a range while the values I am looking for are not consecutive. So I'm not sure if a for loop is what should be used in this case.

      function homeBoxlianTasks() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var todolistsheet = ss.getSheetByName("To-Do List") 
  var todolistliantaskvalues = todolistsheet.getRange("B6:B").getValues();  
  var todolistlianbackground = todolistsheet.getRange("B6:B").getBackgrounds()   
  var homeboxsheet = ss.getSheetByName("HomeBox");
  var homeboxtaskvalues = homeboxsheet.getRange("homeboxtodotasks").getValues();  
  var backgrounds = [];
  var fontLines = [];     
  //for each row that data is present
  for(var i = 0; i < homeboxtaskvalues.length; i  ) {
    var ltValue = todolistliantaskvalues[i][0];    
    var hValue = homeboxtaskvalues[i][0];
    var lbValue = todolistlianbackground[i][0];
    
      if((hValue === ltValue) && lbValue === '#d9ead3') {
      backgrounds.push(["#d9ead3"]); 
      fontLines.push(['line-through']);  
      }
   

    else {
      backgrounds.push([null]); 
      fontLines.push([null]);      
    } 
  }

  homeboxsheet.getRange("homeboxtodotask1").setFontLines(fontLines).setBackgrounds(backgrounds);
}

CodePudding user response：

This is essentially your code:

function myfunk() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet0")
  var vs1 = sh1.getRange("B6:B"   sh1.getLastRow()).getValues();
  var bg1 = sh1.getRange("B6:B"   sh1.getLastRow()).getBackgrounds()
  var sh2 = ss.getSheetByName("Sheet1");
  var vs2 = sh2.getRange("B6:B"   sh2.getLastRow()).getValues();
  var backgrounds = [];
  var fontLines = [];
  for (var i = 0; i < vs2.length; i  ) {
    if ((vs2[i][0] === vs1[i][0]) && bg1[i][0] === '#ffffff') {
      backgrounds.push(["#ffff00"]);
      fontLines.push(['line-through']);
    } else {
      backgrounds.push([null]);
      fontLines.push([null]);
    }
  }
  sh2.getRange(6,2,fontLines.length,1).setFontLines(fontLines).setBackgrounds(backgrounds);
  sh1.getRange(6,2,fontLines.length,1).setFontLines(fontLines).setBackgrounds(backgrounds);
}

Both sheet contain the same data

Sheet0:

Image of highlight output:

Non consecutive values can be found it's simply a line by line search. If it finds to elements in column B that are identical it highlight and strikes through.

CodePudding user response：

Based on your reply:

I have 2 employees with a to-do list one under the other. Every time a task is done, there is a checkbox which will highlight and scratch the task in SheetA. I want that task to be also highlighted and scratched in Sheet B.

Try using an onEdit() trigger. Try the following code:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var summarySheet = ss.getSheetByName("Sheet1");

  var range = e.range
  var row = range.getRow()
  var col = range.getColumn()

  if (sheet.getName() == "Sheet0" && col == 1) {
    var taskCell = sheet.getRange(row, col   1)
    var taskName = taskCell.getValue();

    if (range.isChecked()) {
      taskCell.setFontLine("line-through");
    } else {
      taskCell.setFontLine(null);
    }

    var checker = range.isChecked();
    while (checker != null) {
      row = row - 1
      checker = sheet.getRange(row, col).isChecked();
    }

    var name = sheet.getRange(row - 1, col   1).getValue();

    var sumSheetRow = summarySheet.createTextFinder(name).findNext().getRow();
    var sumSheetLastCol = summarySheet.getLastColumn();
    var sumSheetCol = summarySheet.getRange(sumSheetRow,2,1,sumSheetLastCol-1).createTextFinder(taskName).findNext().getColumn();
    var sumSheetTaskCell = summarySheet.getRange(sumSheetRow, sumSheetCol);
    if (range.isChecked()) {
      sumSheetTaskCell.setFontLine("line-through");
    } else {
      sumSheetTaskCell.setFontLine(null);
    }
  }
}

Result:

This also removes the strikethrough if unchecked:

Hope this helps!