How to speed ​up the search data in sheet

Question

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

Answer 1

• 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.

Sample script 1:

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())
    }
  }
}

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])
  }
}

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])
  }
}

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]).

References:

• 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.