javascript - 结果中仅出现数组中的一个值

Question

这可能是一个比我意识到的更复杂的问题，但我没有编码经验或培训，因此有一个神秘的标题(我什至不知道我在这里做的事情叫什么。关于“循环”的一些东西是关于我所知道的一切)。因此，如果foundCValues.push(colCdata[1][i]); 返回应该有多个结果的列中的第一个结果，修复方法是否简单，只需更改 colCdata 之后括号内的内容即可？我尝试了几种变体，但没有一个能让我到达我需要的地方。我想返回整个列，或该列中不为 NULL 的每个值。

这是脚本的这个特定部分的其余部分。如果我需要发布整个脚本或进一步解释，请告诉我。

    for(var i=0;i<colCdata.length;i++) { 
      if(valueToFind==colCdata[0][i]) {
          foundCValues.push(colCdata[1][i]);  //  Only displays first match
        //  foundCValues.push(colCdata[i][1]);  //  Shows something seemingly random
        //  foundCValues.push(colCdata[1]);  //  Displays everything in a row
      }

编辑:full script脚本是 Horizo​​ntalDV.gs

基本上，我想做的就是从使用辅助表填充数据验证过渡到仅使用列表，这样如果将行添加到表中，事情就不会混淆。

您可以使用之前的“帮助表”方法看到注释掉的“填充 C 列数据验证”。它在标题行中搜索匹配项(与先前选择的单元格 [activecell.value])，然后在数据验证中显示下面列的其余部分以供选择。

function horizontal_dv(e){

  if(e.range.getSheet().getName() == 'Items'){  
    var activess = SpreadsheetApp.getActive().getSheetByName('Items');  
    var colBss = SpreadsheetApp.getActive().getSheetByName('Categories-Concat');
  }
  else if(e.range.getSheet().getName() == 'materialsData'){  
    var activess = SpreadsheetApp.getActive().getSheetByName('materialsData');  
    var colBss = SpreadsheetApp.getActive().getSheetByName('materialsCategories-Concat');
  }
  else if(e.range.getSheet().getName() == 'toolsData'){  
    var activess = SpreadsheetApp.getActive().getSheetByName('toolsData');  
    var colBss = SpreadsheetApp.getActive().getSheetByName('Data');
    var colBdata = colBss.getRange(2,5,colBss.getLastRow(),1).getValues();
  }
  else if(e.range.getSheet().getName() == 'tasksData'){  
    var activess = SpreadsheetApp.getActive().getSheetByName('tasksData');  
    var colBss = SpreadsheetApp.getActive().getSheetByName('Categories-Concat');
    var colBdata = colBss.getRange(1,2,1,colBss.getLastColumn()).getValues();

    var colCss = SpreadsheetApp.getActive().getSheetByName('tasksGroups');
    var colCdata = colCss.getRange(2,2,colCss.getLastRow(),colCss.getLastColumn()).getValues();
    var colDss = SpreadsheetApp.getActive().getSheetByName('tasksSubGroups');
    var colDdata = colDss.getRange(3,2,1,colDss.getLastColumn()).getValues();
  }

  else if(e.range.getSheet().getName() == 'tasksToolsRequired'){  
    var activess = SpreadsheetApp.getActive().getSheetByName('tasksToolsRequired');  
    var colBss = SpreadsheetApp.getActive().getSheetByName('Categories-Concat');
    var colCss = SpreadsheetApp.getActive().getSheetByName('tasksGroups');
    var colDss = SpreadsheetApp.getActive().getSheetByName('tasksSubGroups');
  }

  var colAValues = [];  
  var foundBValues = [];  
  var foundCValues = [];  
  var foundDValues = [];  
  var foundEValues = [];  
  var foundFValues = [];  
  var foundGValues = [];  
  var foundHValues = [];  

  var activeCell = activess.getActiveCell();    
  const valueToFind = activeCell.getValue();

  //  Populate column B data validations
  //  Horizontal search 
  //  Search header row and return column below
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var colIndex = colBdata[0].indexOf(activeCell.getValue()) + 2;

    if(colIndex != 0){
      if(activeCell.getValue() != ""){ 
        var colBvalidationRange = colBss.getRange(2, colIndex,colBss.getLastRow()-1);
        var colBvalidationRule = SpreadsheetApp.newDataValidation().requireValueInRange(colBvalidationRange).build();
        activeCell.offset(0, 1).setDataValidation(colBvalidationRule);
      }  
    }
  }

  //  //  Populate column C data validations
  //  if(e.range.getSheet().getName() != 'Items'){  
  //    if(activeCell.getColumn() == 2 && activeCell.getRow() > 1){
  //      activeCell.offset(0, 1).clearDataValidations();
  //      var colCIndex = colCdata[0].indexOf(activeCell.getValue()) + 2;
  //      if(colCIndex != 0){
  //        if(activeCell.getValue() != ""){ 
  //          var colCvalidationRange = colCss.getRange(3, colCIndex,colCss.getLastRow()-1);
  //          var colCvalidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colCvalidationRange).build();
  //          activeCell.offset(0, 1).setDataValidation(colCvalidationRule);
  //        }
  //      }
  //    }

  //  Populate column C data validations
  if(activeCell.getColumn() == 2 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    Logger.log("valueToFind = " + valueToFind);
    Logger.log("colCdata = " + colCdata);
    Logger.log("colCdata.length = " + colCdata.length);


    for(var i=0;i<colCdata.length;i++) { 
      if(valueToFind==colCdata[0][i]) {
          foundCValues.push(colCdata[1][i]);  //  Only displays first match
        //  foundCValues.push(colCdata[i][1]);  //  Shows something seemingly random
        //  foundCValues.push(colCdata[1]);  //  Displays everything in a row
      }
      Logger.log("colCdata[0][i] = " + colCdata[0][i]);
      Logger.log("colCdata[1][i] = " + colCdata[1][i]);

    }
    Logger.log("foundCValues = " + foundCValues);

    for(var i=0;i<colCdata.length;i++) {
      if(valueToFind==colCdata[i]) {
        foundDValues.push(colCdata[i]);
      }
    }

    if(activeCell.getValue() != ""){ 
      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);
    }
  }      

  //  Populate column D data validations
  if(activeCell.getColumn() == 3 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var colDIndex = colDdata[0].indexOf(activeCell.getValue()) + 2;
    if(colDIndex != 0){
      if(activeCell.getValue() != ""){ 
        var colDvalidationRange = colDss.getRange(4, colDIndex,colDss.getLastRow()-1);
        var colDvalidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colDvalidationRange).build();
        activeCell.offset(0, 1).setDataValidation(colDvalidationRule);
      }
    }
  }

  //  Populate column E data validations
  if(activeCell.getColumn() == 4 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var colEIndex = colEdata[0].indexOf(activeCell.getValue()) + 2;
    if(colEIndex != 0){
      if(activeCell.getValue() != ""){ 
        var colEvalidationRange = colEss.getRange(5, colEIndex,colEss.getLastRow()-1);
        var colEvalidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colEvalidationRange).build();
        activeCell.offset(0, 1).setDataValidation(colEvalidationRule);
      }
    }
  }

  //  Populate column F data validations
  if(activeCell.getColumn() == 5 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var colFIndex = colFdata[0].indexOf(activeCell.getValue()) + 2;
    if(colFIndex != 0){
      if(activeCell.getValue() != ""){ 
        var colFvalidationRange = colFss.getRange(4, colFIndex,colFss.getLastRow()-1);
        var colFvalidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colFvalidationRange).build();
        activeCell.offset(0, 1).setDataValidation(colFvalidationRule);
      }
    }
  }

  //  Populate column G data validations
  if(activeCell.getColumn() == 6 && activeCell.getRow() > 1){
    activeCell.offset(0, 1).clearDataValidations();
    var colGIndex = colGdata[0].indexOf(activeCell.getValue()) + 2;
    if(colGIndex != 0){
      if(activeCell.getValue() != ""){ 
        var colGvalidationRange = colGss.getRange(4, colGIndex,colGss.getLastRow()-1);
        var colGvalidationRule = SpreadsheetApp.newDataValidation().requireValueInList(colGvalidationRange).build();
        activeCell.offset(0, 1).setDataValidation(colGvalidationRule);
      }
    }
  }
}

Answer 1

如果您有多个行和列，您可能需要两个计数器来显示整个数组。根据阵列的宽度和高度，您可以设置限制。如果您的数据有 5 行高:

//untested 
for (var q = 0; q < 5; q++) /* counts to height one time for every entire count to 
length*/
{
    for(var i=0;i<colCdata.length;i++) /*counts to length before rolling back to 
height */
    {
     foundCValues.push(colCdata[q][i]); 
    }
}