比较列 A，B 和 C 并在匹配时返回 col D 的值

Question

我前几天问了这个问题"使用col A搜索col a并在匹配时返回col b的值"，并收到了一个完美工作的答案！

，但是现在我试图修改代码以比较所有先前选择的列值并返回下一个值(B-E(。因此，在选择ProjectTaskSadj！B5之后，将使用ProjectTasks！A5：B5的值来填充ProjectTaskSadj！C5的数据验证，依此类推，直到ProjectTaskSadj！E5。

。

我只要求最后一个过程A & B & C & D，因为我认为我可以根据需要自己修剪自己。

这是我表格的链接。

这是我当前的脚本

function projectTasksAdjDV(e) {
  var ptaSh=SpreadsheetApp.getActive().getSheetByName('projectTasksAdj');  
  var ptSh=SpreadsheetApp.getActive().getSheetByName('projectTasks');
  var foundValues = [];  
  var foundValues2 = [];  
  var foundValues3 = [];  
  var activeCell = ptaSh.getActiveCell();    
  const valueToFind = activeCell.getValue();
  if(activeCell.getColumn()==1 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var data=ptSh.getRange(3,1,ptaSh.getLastRow(),2).getValues(); // changed ptaSht
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][0]) {
        foundValues.push(data[i][1]);
      }
    }
    var colBValidationRange = foundValues;
    var colBValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colBValidationRange).build();
    activeCell.offset(0, 1).setDataValidation(colBValidationRule);
  }
  //  Populate column C, D & E data validations
  Logger.log("getColumn = " + activeCell.getColumn());
  Logger.log("getRow = " + activeCell.getRow());
  if(activeCell.getColumn() > 1 && activeCell.getColumn()<4 && activeCell.getRow()>1){
    Logger.log("Row > 1 && Column < 4");
    activeCell.offset(0, 1).clearDataValidations();
    activeCell.offset(0, 2).clearDataValidations();
    activeCell.offset(0, 3).clearDataValidations();
    var ptadata=ptaSh.getRange(3,1,ptaSh.getLastRow(),5).getValues();
    var ptdata=ptSh.getRange(3,4,ptSh.getLastRow(),5).getValues();
    for(var i=0;i<ptadata.length;i++) {
      if(valueToFind==ptadata[i][0]) {
        foundValues.push(ptdata[i][0]);
      }
      Logger.log("foundValues = " + foundValues);
      if(valueToFind==ptadata[i][1]) {
        foundValues2.push(ptdata[i][0]);
      }
      Logger.log("foundValues2 = " + foundValues2);
      if(valueToFind==ptadata[i][2]) {
        foundValues3.push(ptdata[i][0]);
      }
      Logger.log("foundValues3 = " + foundValues3);
    }
    var colCValidationRange = foundValues;
    var colCValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colCValidationRange).build();
    activeCell.offset(0, 1).setDataValidation(colCValidationRule);
    var colDValidationRange = foundValues2;
    var colDValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colDValidationRange).build();
    activeCell.offset(0, 2).setDataValidation(colDValidationRule);
    var colEValidationRange = foundValues3;
    var colEValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEValidationRange).build();
    activeCell.offset(0, 3).setDataValidation(colEValidationRule);
  }
}

Answer 1

我在您的代码if(activeCell.getColumn() != 0){中注意到了这一点，并且对于任何活动单元格始终是正确的。

function projectTasksAdjDV(e) {
  var ptaSh=SpreadsheetApp.getActive().getSheetByName('projectTasksAdj');  
  var ptSh=SpreadsheetApp.getActive().getSheetByName('projectTasks');
  var foundValues = [];  
  var foundValues2 = [];  
  var foundValues3 = [];  
  var activeCell = ptaSh.getActiveCell();    
  const valueToFind = activeCell.getValue();
  if(activeCell.getColumn()==1 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var data=ptaSh.getRange(3,1,ptaSht.getLastRow(),2).getValues();
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][0]) {
        foundValues.push(data[i][1]);
      }
    }
    var colBValidationRange = foundValues;
    var colBValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colBValidationRange).build();
    activeCell.offset(0, 1).setDataValidation(colBValidationRule);
  }
  //  Populate column C, D & E data validations
  if(activeCell.getColumn()>0 && activeCell.getColumn()<4 && activeCell.getRow()>1){
    activeCell.offset(0, 1).clearDataValidations();
    activeCell.offset(0, 2).clearDataValidations();
    activeCell.offset(0, 3).clearDataValidations();
    var ptadata=ptaSh.getRange(3,1,ptaSh.getLastRow(),3).getValues();
    var ptdata=ptSh.getRange(3,4,ptSh.getLastRow(),1).getValues();
    for(var i=0;i<ptadata.length;i++) {
      if(valueToFind==ptadata[i][0]) {
        foundValues.push(ptdata[i][0]);
      }
      if(valueToFind==ptadata[i][1]) {
        foundValues2.push(ptdata[i][0]);
      }
      if(valueToFind==ptadata[i][2]) {
        foundValues3.push(ptdata[i][0]);
      }
    }
    var colCValidationRange = foundValues;
    var colCValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colCValidationRange).build();
    activeCell.offset(0, 1).setDataValidation(colCValidationRule);
    var colDValidationRange = foundValues2;
    var colDValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colDValidationRange).build();
    activeCell.offset(0, 2).setDataValidation(colDValidationRule);
    var colEValidationRange = foundValues3;
    var colEValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEValidationRange).build();
    activeCell.offset(0, 3).setDataValidation(colEValidationRule);
  }
}

看一下这些参考。了解阵列对于理解如何通过数据循环至关重要。

getValues((getValue((

Answer 2

这就是我所遇到的。它可能可以使其更加精简或高效，但似乎已经有效了。谢谢@mimi(和库珀...(的所有帮助！你教我几件事。谢谢！

i首先要过滤匹配A列，然后B，C，D和最后E的所有可能性是确切的匹配。但是以下列的数据验证是"重新填充"的。

请随时批评我仍在学习时所做的事情，并且很可能有一些冗余。

function projectTasksAdjDV(e) {  
  var activess = SpreadsheetApp.getActive().getSheetByName('projectTasksAdj');  
  var datass = SpreadsheetApp.getActive().getSheetByName('projectTasks');
  var colAValues = [];  
  var foundBValues = [];  
  var foundCValues = [];  
  var foundDValues = [];  
  var foundEValues = [];  
  var activeCell = activess.getActiveCell();  
  var data = datass.getRange(3,1,datass.getLastRow(),7).getValues();
  const valueToFind = activeCell.getValue();
  const colAVal = data;
    for(var i=0;i<data.length;i++) {
      if(colAVal==data[i][1]) {
        colAValues.push(data[i][0]);
      }
    }
  //  Couldn't seem to get indexOf to work properly
  //  so this is my "workaround".
  var colIndex = activess.getActiveCell().getColumn();
  //  Populate column B data validations
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 2){
    activeCell.offset(0, 1).clearDataValidations();
    activeCell.offset(0, 2).clearDataValidations();
    activeCell.offset(0, 3).clearDataValidations();
    activeCell.offset(0, 4).clearDataValidations();
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][0]) {
        foundBValues.push(data[i][1]);
      }
    }
    for(var i=0;i<data.length;i++) {      
      if(valueToFind==data[i][0]) {
        foundCValues.push(data[i][2]);
      }
    }
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][0]) {
        foundDValues.push(data[i][3]);
      }
    }
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][0]) {
        foundEValues.push(data[i][6]);
      }
    }
    if(colIndex != 0){      
      var colBValidationRange = foundBValues;
      var colBValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colBValidationRange).build();
      activeCell.offset(0, 1).setDataValidation(colBValidationRule);
      var colCValidationRange = foundCValues;
      var colCValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colCValidationRange).build();
      activeCell.offset(0, 2).setDataValidation(colCValidationRule);
      var colDValidationRange = foundDValues;
      var colDValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colDValidationRange).build();
      activeCell.offset(0, 3).setDataValidation(colDValidationRule);
      var colEValidationRange = foundEValues;
      var colEValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEValidationRange).build();
      activeCell.offset(0, 4).setDataValidation(colEValidationRule);
    }      
  }
  //  Populate column C data validations
  if(activeCell.getColumn() == 2 && activeCell.getRow() > 2){
    activeCell.offset(0, 1).clearDataValidations();
    activeCell.offset(0, 2).clearDataValidations();
    activeCell.offset(0, 3).clearDataValidations();
    for(var i=0;i<data.length;i++) {      
      if(valueToFind==data[i][1]) {
        foundCValues.push(data[i][2]);
      }
    }
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][1]) {
        foundDValues.push(data[i][3]);
      }
    }
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][1]) {
        foundEValues.push(data[i][6]);
      }
    }
    if(colIndex != 0){      
      var colCValidationRange = foundCValues;
      var colCValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colCValidationRange).build();
      activeCell.offset(0, 1).setDataValidation(colCValidationRule);
      var colDValidationRange = foundDValues;
      var colDValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colDValidationRange).build();
      activeCell.offset(0, 2).setDataValidation(colDValidationRule);
      var colEValidationRange = foundEValues;
      var colEValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEValidationRange).build();
      activeCell.offset(0, 3).setDataValidation(colEValidationRule);
    }      
  }
  //  Populate column D data validations
  if(activeCell.getColumn() == 3 && activeCell.getRow() > 2){
    activeCell.offset(0, 1).clearDataValidations();
    activeCell.offset(0, 2).clearDataValidations();
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][2]) {
        foundDValues.push(data[i][3]);
      }
    }
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][2]) {
        foundEValues.push(data[i][6]);
      }
    }
    if(colIndex != 0){            
      var colDValidationRange = foundDValues;
      var colDValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colDValidationRange).build();
      activeCell.offset(0, 1).setDataValidation(colDValidationRule);
      var colEValidationRange = foundEValues;
      var colEValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEValidationRange).build();
      activeCell.offset(0, 2).setDataValidation(colEValidationRule);
    }      
  }
  //  Populate column E data validations
  if(activeCell.getColumn() == 4 && activeCell.getRow() > 2){
    activeCell.offset(0, 1).clearDataValidations();
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][3] && data[0][0]==data[i][0]) {
        foundEValues.push(data[i][6]);
      }
    }
    if(colIndex != 0){      
      var colEValidationRange = foundEValues;
      var colEValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEValidationRange).build();
      activeCell.offset(0, 1).setDataValidation(colEValidationRule);
    }      
  }
}