如何加快工作表中的搜索数据 [英] How to speed ​up the search data in sheet

问题描述

我有超过1000000条记录如何在工作表中加快搜索速度.我通常搜索20多岁时如何提高? (工作表包括20列和10000条记录)

I have more than 1000000 record how to speed up search in sheet. I normally search for 20s how to improve ? (sheet include 20 column and 10000 record)

var ss =  SpreadsheetApp.openByUrl(urldb);
var ws =  ss.getSheetByName("Account");
var data = ws.getDataRange().getValues();

for(var i = 0; i < data .length; i++){

    if(data [i][1] == "ID998724"){

      Logger.log("found you" + data [i][1]);

    }
};

return data[i][1];

推荐答案

• 您要使用Google Apps脚本在电子表格中帐户"表上的"B"列中搜索ID998724的值.
• You want to search the value of ID998724 form the column "B" on the sheet of "Account" in Spreadsheet using Google Apps Script.

如果我的理解是正确的，那么这三个示例脚本又如何呢?请认为这只是几个答案之一.

If my understanding is correct, how about these 3 sample scripts? Please think of this as just one of several answers.

在此脚本中，我将Class TextFinder用于这种情况.这是在Google的最新更新中添加的.

In this script, I used Class TextFinder for this situation. This was added in a recent update of Google.

var urldb = "###"; // Please set this.
var searchValue = "ID998724";

var ss = SpreadsheetApp.openByUrl(urldb);
var ws = ss.getSheetByName("Account");
var f = ws.createTextFinder(searchValue).findAll();
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    if (f[i].getColumn() == 2) {
      Logger.log("found you" + f[i].getValue())
    }
  }
}

示例脚本2:

在此脚本中，从"B"列中检索值. 罗斯的评论中也提到了这一点.而且从基准测试的结果来看，我在这种情况下使用了filter().

Sample script 2:

In this script, the values are retrieved from the column "B". This is also mentioned at ross's comment. And also from the result of benchmark, I used filter() for this situation.

var urldb = "###"; // Please set this.
var searchValue = "ID998724";

var ss = SpreadsheetApp.openByUrl(urldb);
var ws = ss.getSheetByName("Account");
var data = ws.getRange(1, 2, ws.getLastRow(), 1).getValues();
var f = data.filter(function(e) {return e[0] == searchValue});
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    Logger.log("found you" + f[i])
  }
}

示例脚本3:

在此脚本中，我在这种情况下使用了查询语言.

Sample script 3:

In this script, I used Query Language for this situation.

var urldb = "###"; // Please set this.
var searchValue = "ID998724";

var ss = SpreadsheetApp.openByUrl(urldb);
var ws = ss.getSheetByName("Account");
var query = "select * where B='" + searchValue + "'";
var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/gviz/tq?gid=" + ws.getSheetId() + "&tqx=out:csv&tq=" + encodeURIComponent(query);
var options = {headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}};
var csv = UrlFetchApp.fetch(url, options);
var f = Utilities.parseCsv(csv);
if (f.length > 0) {
  for (var i = 0; i < f.length; i++) {
    Logger.log("found you" + f[i][1])
  }
}

注意:

• 在您的脚本中，我认为在return data[i][1]处发生了错误.因为i与data.length相同.例如，如果要通过return data[i][1]检索值，请在Logger.log("found you" + data [i][1])之后放置break.

Note:

• In your script, I think that an error occurs at return data[i][1]. Because i is the same with data.length. If you want to retrieve the value by return data[i][1], for example, please put break after Logger.log("found you" + data [i][1]).
• 文本查找器
• filter()
• 基准:使用Google Apps脚本进行数组处理的循环
• 查询语言参考(0.7版)
• Class TextFinder
• filter()
• Benchmark: Loop for Array Processing using Google Apps Script
• Query Language Reference (Version 0.7)

如果我误解了您的问题，而这些示例脚本不是您想要的结果，我深表歉意.

If I misunderstood your question and these sample scripts were not the results you want, I apologize.

这篇关于如何加快工作表中的搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！