查找匹配数值的单元格并返回行号

Question

查找匹配数值的单元格并返回行号

google-apps-scriptgoogle-sheetsrow-number

54

员工表格中包含员工姓名，位于单元格C2。员工姓名还应出现在数据表格的B3:B153区域内。

如何获取与员工姓名匹配的数据表格中单元格的行号？

我尝试了下面的脚本，但似乎不起作用。

  var Sheet = SpreadsheetApp.getActive();
  var Employeesheet = Sheet.getSheetByName('Employee')
  var DataSheet = Sheet.getSheetByName('Data');
  var Column = Sheet.getRange(3,2,151,1);
  var Values = column.getValues(); 
  var Row = 0;

  while ( Values[Row] && Values[Row][0] !=(EmployeeSheet.getRange(2,3,1,1).getValue()) ) {
    Row++;
  }

  if ( Values[Row][0] === (EmployeeSheet.getRange(2,3,1,1).getValue()) ) 
    return Row+1;
  else 
    return -1;

  }

- LennartB

4个回答

32

今天我遇到了这个问题，我找到了一个本地方法来完成这个任务。

以下是一个测试代码片段，您可以用它来验证是否有效:

  var spreadsheet = SpreadsheetApp.getActive();
  var tosearch = "your text to search";
  var tf = spreadsheet.createTextFinder(tosearch);
  var all = tf.findAll();
  
  for (var i = 0; i < all.length; i++) {
    Logger.log('The sheet %s, cell %s, has the value %s.', all[i].getSheet().getName(), all[i].getA1Notation(), all[i].getValue());
  }

- Charles Santos

1

我已经尝试了这个函数来查找表格中的所有0，但它不仅可以找到所有的0，还能找到包含0的数字（100、2000、10）。我们能否只用它来查找零？此外，它运行时间很长，直到崩溃才结束。有什么建议吗？ - user12736831

嗨@Roomi。查看类文档（https://developers.google.com/apps-script/reference/spreadsheet/text-finder#matchentirecellmatchentirecell），我发现您需要将matchEntireCell方法设置为TRUE以实现您的需求。希望能有所帮助。 - Charles Santos

是的，这解决了问题。感谢您的建议。 - user12736831

4

由于Google App Script是一个基于JavaScript的平台，我可以想到另外两种获取行索引的方法。这可能会更快，因为它们是内置的Array方法。

方法1：使用Array.prototype.forEach()

function getRowIndexUsingforEach(){
  let term = 'user-1700@example.com';
  var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 2000).getValues();
  data.forEach((val, index) => {
     if(val == term){
         Browser.msgBox(index+1); //because Logger.log() takes too long
     }               
  })
}

方法二：使用 Array.prototype.findIndex()

function getRowUsingfindIndex(){
  let term = 'user-1700@example.com';
  let data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 2000).getValues();
  let row = data.findIndex(users => {return users[0] == term});  
  Browser.msgBox(row+1); //because Logger.log() takes too long
}

为了看出哪个方法更快，我多次对22000行数据的一列运行了这两种方法以及被接受的答案中的方法。以下是平均值：

使用for循环（来自被接受的答案）：2.66毫秒
使用Array.prototype.forEach：11.75毫秒
使用Array.prototype.findIndex：2.00毫秒

我猜Array.prototype.forEach并不适用于查找匹配项的索引；它是为Array.prototype.findIndex这一目的而设计的。

- MWaheed

2

我更喜欢在打开表格时一次性将所有值加载到数组中，然后使用Array.indexOf()查找匹配字符串的索引。我认为这种解决方案的执行时间会比其他解决方案短。

function updateValues(){
  dataRangeSearch = activeSheet.getRange(1,1,activeSheet.getLastRow());
  dataSearch = dataRangeSearch.getValues().reduce(function (a, b) {
    return a.concat(b);
  });;
}
updateValues();

function findValue(fieldName){
  var row = dataSearch.indexOf(fieldName);
  if (row == -1){ // Variable could not be found
    SpreadsheetApp.getUi().alert('I couldn\'t find field name "'+fieldName+'"');
    return "";
  } else {
    return activeSheet.getRange(row+1,2).getValue(); //Return the value of the field to the right of the matched string
  }
}

- Julian

2

我想测试一下，因为我希望找到最快的方法来获取包含搜索元素的行，但结果表明使用indexOf并不是最快的方法，而被接受的解决方案是两种方法中最快的。我认为问题在于需要将数据列转换为一维数组，可以通过映射或缩减来实现，就像你在这里做的那样，然后才能应用indexOf，我认为这违背了使用indexOf查找所需值所在行号的目的。 - Francisco Cortes

indexOf只获取第一个实例吗？ - aNewb

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- St3ph · Accepted Answer

这里是代码

function rowOfEmployee(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var employeeName = sheet.getRange("C2").getValue();
  for(var i = 0; i<data.length;i++){
    if(data[i][1] == employeeName){ //[1] because column B
      Logger.log((i+1))
      return i+1;
    }
  }
}

如果您想要执行这种查找操作，最好使用sheet.getDataRange().getValues()来检索数据，因为在此情况下，您将获得数据作为值的表格，这样更快。当您使用标准的EmployeeSheet.getRange(2,3,1,1).getValue()时，实际上会检索出一个需要更长时间处理且每次查询电子表格的对象。

在我的示例中，我只进行了一次查询以检索所有数据，而不是每次检索一个数据。

Stéphane