按列搜索电子表格，返回行 [英] Search spreadsheet by column, return rows

问题描述

我正在尝试在运行时方面找到最佳脚本来完成任务.我有一个相当大的电子表格，需要检查某些已知列中的值，并根据匹配的情况返回该行.理想情况下，我想要一个包含返回的行的新电子表格.

I'm trying to find the best script in terms of runtime to complete a task. I've got a decently large spreadsheet where I need to check values in certain known columns, and depending on a match case it returns that row. Ideally I'd like a new spreadsheet containing the returned rows.

我已经通过ID打开了电子表格，并获得了&范围，但不确定最有效的方式来搜索特定的列并不仅获取该值，而且获取整个行.

I've got the spreadsheet opened by ID and I've got the sheet & range, but not sure the most efficient way to search through the specific columns and grabbing not just that value but the entire row.

推荐答案

您可以使用下面的代码在特定列中进行搜索.代码是不言自明的.

You can use the code below to search in a specific column. Code is self explanatory.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Search", functionName: "onSearch"} ];
  ss.addMenu("Commands", menuEntries);    
}

function onSearch()
{
    var searchString = "Test11";
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName"); 
    var column =4; //column Index   
    var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues(); //1st is header row
    var searchResult = columnValues.findIndex(searchString); //Row Index - 2

    if(searchResult != -1)
    {
        //searchResult + 2 is row index.
        SpreadsheetApp.getActiveSpreadsheet().setActiveRange(sheet.getRange(searchResult + 2, 1))
    }
}

Array.prototype.findIndex = function(search){
  if(search == "") return false;
  for (var i=0; i<this.length; i++)
    if (this[i] == search) return i;

  return -1;
} 

这篇关于按列搜索电子表格，返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！