CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
TC76
TC76

Reputation: 870

Compare columns A,B & C and return values of col D when matching

I asked this question the other day "Use GAS to search col A and return values of col B when matching" and received an answer which works perfectly!

But now I'm trying to modify the code to compare all of the previously selected column values and return the next value (B-E). So after projectTasksAdj!B5 is selected, the values from projectTasks!A5:B5 will be used to populate the data validation of projectTasksAdj!C5 and so on until projectTasksAdj!E5.

I only asked for the last process A & B & C & D though because I figured I could just trim it down myself as needed.

Here's a link to my sheet.

Here is my current script

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

Upvotes: 1

Views: 109

Answers (2)

TC76
TC76

Reputation: 870

This is what I wound up with. It could probably be made more streamline or efficient, but it seems to be working so far. Thank you @MiMi (and Cooper...) for all of your help! You taught me several things. Thank you!

I start by filtering all possibilities that match column A, then B,C,D and finally E is an exact match. But the following column's data validation is "re-populated" with each selection.

Please feel free to critique what I've done as I'm still learning and very likely have some redundancy going on.

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

Upvotes: 0

Cooper
Cooper

Reputation: 64062

I noticed this in your code if(activeCell.getColumn() != 0){ and it is always true for any active cell.

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

Take a look at these references. Understanding arrays is crucial to understanding how to loop through data.

getValues() getValue()

Upvotes: 2

Related Questions