Get array values from column

Question

If my function gets the values of one column, say column I, how can I tell it to instead get the values of the column to the right (J) instead of I:K?

function headerSearch(e, activeCell, activeRow, activeCol, data, mode, secMode, terMode) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var foundValues = [];
  var forConR = data.length;
  var forConC = data[0].length;
  Logger.log("data[0] = " + data[0]);

  for (var i = 1; i < forConR; i++) {
    for (var j = 0; j < forConC; j++) {
      if (activeCell != "" && activeCol == 2 && data[0][j].indexOf(mode) > -1) {
        if (activeCell.getValue() == data[0][j]) {
          foundValues.push(data[i][j]);
        }
      } else if (activeCell != "" && activeCol == 3 && data[0][j].indexOf(mode) > -1 && data[i][j] != "") {
        foundValues.push(data[i][j]);
        Logger.log("foundValues = " + foundValues);
      }
    }
  }
  if (foundValues != "") {
    var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(foundValues).build();
    activeCell.offset(0, 1).setDataValidation(validationRule);
  }
}

EDIT:

I tried adding foundValues.push(data[i][j+1]); which gets me out of the first column (I), but then of course adds the NEXT column (L) that I don't want either. I'm just not sure how to isolate the column index. Once I figure that out, I'm sure it's just a matter of adding +1 or something to OFFSET to the column to the right.

ziganotschka · Accepted Answer · 2020-06-04 15:28:42Z

1

You have two `for` loops - one of them iterating through all rows, the second through all columns of data

What you want instead is to retrieve only ONE column of data rather than iterating through ALL of them
You can do it by simply dropping the second for loop and instead hardcoding the value for j
If you are itnerested in the second column of your range - the column index should be 1 (since array indices start with 0)
Without having a deeper knowledge of the purpose of your if conditions and assuming that you use them only to assess the value in column J, you can modify your code as following:

...
for (var i = 1; i < forConR; i++) {
  var j = 1;
  if (activeCell != "" && activeCol == 2 && data[0][j].indexOf(mode) > -1) {
    if (activeCell.getValue() == data[0][j]) {
      foundValues.push(data[i][j]);
    }
  } else if (activeCell != "" && activeCol == 3 && data[0][j].indexOf(mode) > -1 && data[i][j] != "") {
    foundValues.push(data[i][j]);
    Logger.log("foundValues = " + foundValues);
  } 
}
...

answered Jun 4, 2020 at 15:28

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TC76 Over a year ago

Thank you very much for your answer. After messing with it a little bit, I determined that I do in fact need to loop through the columns to discover which column matches my selection. But your answer drove me to look at what I was doing a little bit differently. Thank you!

TC76 · Accepted Answer · 2020-06-05 14:38:26Z

I rearranged my if statements and added one to isolate the "mode" column (B) selected. At that point, I could add j + 1 to get the following column values for the next data validation selection.

function headerSearch(e, activeCell, activeRow, activeCol, data, mode, secMode, terMode) {
  var foundValues = [];
  var forConR = data.length;
  var forConC = data[0].length;
  if (activeCell != "") {
    for (var i = 1; i < forConR; i++) {
      for (var j = 0; j < forConC; j++) {
        if (data[0][j] == mode && data[i][j] != "") {
          var modeCol = j;
        }
        if (activeCol == 2 && data[i][j] != "") {
          if (activeCell.getValue() == data[0][j]) {
            foundValues.push(data[i][j]);
          }
        } else if (activeCol == 3 && data[0][j].indexOf(mode) > -1 && data[i][j] != "" && data[0][modeCol + 1].indexOf(mode) > -1) {
          foundValues.push(data[i][modeCol + 1]);
        } else if (activeCol == 4 && data[0][j].indexOf(mode) > -1 && data[i][j] != "" && data[0][modeCol + 2].indexOf(mode) > -1) {
          foundValues.push(data[i][modeCol + 2]);
        }
      }
    }
  }
  if (foundValues != "") {
    var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(foundValues).build();
    activeCell.offset(0, 1).setDataValidation(validationRule);
  }
}

Collectives™ on Stack Overflow

Get array values from column

2 Answers 2

You have two `for` loops - one of them iterating through all rows, the second through all columns of data

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You have two for loops - one of them iterating through all rows, the second through all columns of data

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related

You have two `for` loops - one of them iterating through all rows, the second through all columns of data