CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
TC76
TC76

Reputation: 870

Only one value from array appearing in results

This may be a more complicated question than I realize, but I have no coding experience or training, hence the cryptic title (I don't even know what it's called that I'm doing here. Something about "looping" is about all I know). So if foundCValues.push(colCdata[1][i]); returns the first result in a column that should have multiple results, is the fix as simple as just changing something within the brackets after colCdata? I've tried several variations and none get me where I need to be. I'd like to either return the entire column, or every value in the column that isn't NULL.

Here is the rest of this particular part of the script. If I need to post the entire script or explain further, please let me know.

    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
      }

EDIT: full script script is HorizontalDV.gs

Basically, what I'm trying to do is transition from using a helper sheet to populate my data validations to just using a list so that things don't get mixed up if lines are added to a sheet.

You can see the commented out "Populate column C data validations" using the previous "helper sheet" method. It searches the header row for a match (to the previously selected cell [activecell.value]), and then displays the rest of the column below in the data validation to select from.

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);
      }
    }
  }
}

Upvotes: 1

Views: 81

Answers (1)

Krausladen
Krausladen

Reputation: 148

You proably need to have two counters going for it to display the entire array if you have multiple rows and columns. Depending on how wide your array and how tall, you may set your limits. If your data is 5 rows tall:

//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]); 
    }
}

Upvotes: 2

Related Questions