匹配时使用GAS搜索col A并返回col B的值 [英] Use GAS to search col A and return values of col B when matching

问题描述

我有一个脚本，该脚本将搜索工作表的列标题，并从与该搜索条件匹配的下面的行中返回所有值.我想修改此脚本以改为在A列中搜索该条件，并从B列返回所有对应的行值.

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.

因此在下面的示例中，如果我搜索"Apples"，则结果数组将为"Red，Green".

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 

我整天都在绞尽脑汁，改变周围的事物，但是我一生都无法使它发挥作用.我显然是Google Apps脚本的新手.因此，我们将不胜感激任何帮助，尤其是解释.

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.

谢谢！

特雷

推荐答案

这可能有帮助.

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(", "));
}

这篇关于匹配时使用GAS搜索col A并返回col B的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！