Google脚本按列搜索电子表格，并返回行

问题描述：

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

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

Google脚本按列搜索电子表格，并返回行

相关推荐