CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
TC76
TC76

Reputation: 870

Use GAS to search col A and return values of col B when matching

I have a script that will search the column headers of a sheet and return all of the values from the rows below that match that search criteria. I'd like to modify this script to instead search column A for that criteria and return all of the corresponding row values from column B.

So in the following example, if I searched for "Apples", the resulting array would be "Red, Green".

Apples | Red
Oranges | Orange
Grapes | Green
Apples | Green

I've been racking my brain all day and switching things around, but cannot for the life of me make it work. I'm obviously new to Google Apps Script. So any help, and especially explanation, is greatly appreciated.

Here is the script I use to search the headers and return the values below. 

function projectTasksAdjDV(e) {
  Logger.log("Started projectTasksAdjDV(e)");


  //  The sheet we are currently editing
  var activess = SpreadsheetApp.getActive().getSheetByName('projectTasksAdj');  

  //  Column B data spreadsheet
  var colBss = SpreadsheetApp.getActive().getSheetByName('projectTasks');

  //  Column C data spreadsheet
  //  var colCss = SpreadsheetApp.getActive().getSheetByName('');

  //  Column C helper spreadsheet
  //  var colCDVss = SpreadsheetApp.getActive().getSheetByName('');

  //  Column D data spreadsheet
  //  var colDss = SpreadsheetApp.getActive().getSheetByName('Items');

  //  Column D helper spreadsheet
  //  var colDDVss = SpreadsheetApp.getActive().getSheetByName('estimate-ItemsDV');  


  var activeCell = activess.getActiveCell();  
  Logger.log("activeCell: ROW " + activeCell.getRow() + " COL " + activeCell.getColumn());


  //  Populate column B data validations
  //  
  //  

  //  If we are in the 1st column (A), and not in the first row...
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1){

    //  Clear the current data validation in the next column
    activeCell.offset(0, 1).clearDataValidations();

    //  Search the header row of colBss for the selection from column (A)
    var colB = colBss.getRange(1,2,1,colBss.getLastColumn()).getValues();

    //  Beginning with Col A as #1, count across to identify matching column
    var colBIndex = colB[0].indexOf(activeCell.getValue()) + 2;
    Logger.log("colB: " + colB[0]);
    Logger.log(colB[0].indexOf(activeCell.getValue()) + 2);

    if(colBIndex != 0){

      // colBss.getRange(row, column, numRows, numColumns)
      var colBvalidationRange = colBss.getRange(2, colBIndex,colBss.getLastRow()-1);
      var colBvalidationRule = SpreadsheetApp.newDataValidation().requireValueInRange(colBvalidationRange).build();

      activeCell.offset(0, 1).setDataValidation(colBvalidationRule);

    }

  }


}

If you'd like to see my sheet for reference, here it is. The sheet I'm working in is projectTasksAdj. The source data comes from projectTasks.

Thank you!

Trey

Upvotes: 1

Views: 176

Answers (1)

Amit Agarwal
Amit Agarwal

Reputation: 11268

This might help.

function projectTasksAdjDV(e) {
  var activess = SpreadsheetApp.getActive().getSheetByName('projectTasksAdj');  
  var foundValues = [];  
  var activeCell = activess.getActiveCell();    
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1){
    const valueToFind = activeCell.getValue();
    var colBss = SpreadsheetApp.getActive().getSheetByName('projectTasks');
    var data=colBss.getRange(1,1,colBss.getLastRow(),2).getValues();
    for(var i=0;i<data.length;i++) {
      if(valueToFind==data[i][0]) {
        foundValues.push(data[i][1]);
      }
    }
  }
  Logger.log(foundValues.join(", "));
}

Upvotes: 3

Related Questions