Google表将列与所有表上的同一列进行比较

Question

    function setStrikeThru(range) {
      var ss = SpreadsheetApp.getActiveSpreadsheet;
      var sheet = SpreadsheetApp.getActiveSheet();
      var cell = sheet.getRange(range);
      cell.setFontLine("line-through");
    }
    //
    function compareTwo() {
    // strike through duplicates on user pages
    //if on here
      var sheetNameToWatch1 = "WEEKLY COMPLETED JOBS";
    //or here
      var sheetNameToWatch2 = "PAST JOBS";
    //strikethrough here
      var shtNames = ["0","Adia","Alejandra","Austin","Daniel","David L"];
    //loop through number of values in array
      for(var j=1; j < shtNames.length; j++){
        var ss = SpreadsheetApp.getActiveSpreadsheet();
    //use this sheet as values to check for
        var sheet = ss.getSheetByName(sheetNameToWatch1);
    //strike through on this sheet
        var targetSheet = ss.getSheetByName(shtNames);
    //count number of rows to check
        var totalRows = sheet.getRange(targetSheet.getLastRow() + 1, 1);
   //loop through number of rows
        for (var i = 1; i < totalRows; i++){
          var cell = "C" + i;
          var valToChk = sheet.getRange(cell) ;
          if (sheet.getRange(cell)===targetSheet.getRange(cell) ){
            setStrikeThru(targetSheet.getRange(cell));
          }
        }
      }

我不知道我是否接近这一点。

基本上，我有一个主表"每周完成的作业"，我想将此单纸C列上的值与所有其他纸上的C列进行比较，如果在"每周完成的作业"上存在，请在其他片面上进行罢工"

我已经成功编写了其他代码来复制行并根据价值移动它们，但是我无法对此进行逻辑。JavaScript不是我的母语。感谢您的帮助。

Answer 1

您正在比较范围对象，这将行不通。您需要使用范围＃getValues（）从范围提取值并比较值。以下是一个可能对您有帮助的示例。

function compare() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var controlSheet = ss.getSheetByName("WEEKLY COMPLETED JOBS");
  var column = 3;
  var firstValueRow = 2;
  var lastRow = controlSheet.getLastRow();
  var controlValues = controlSheet.getRange(firstValueRow, column, lastRow).getValues();
  var sheetsToCheck = ["Adia", "Alejandra", "Daniel", "David L"];
  sheetsToCheck.forEach(function (sheetName) {
    var sheetToCheck = ss.getSheetByName(sheetName);
    if (!sheetToCheck) {
      throw new Error("No sheet with name: " + sheetName);
    }
    var checkValues = sheetToCheck.getRange(firstValueRow, column, lastRow).getValues();
    for (var i = 0; i < controlValues.length; i++) {
      controlValue = controlValues[i][0];
      if (controlValue) {
        var checkValue = checkValues[i][0];
        if (controlValue === checkValue) {
          sheetToCheck.getRange(i + firstValueRow, column).setFontLine("line-through");
        }
      }
    }
  });
}